Re: ADODB using 97

Hi.

I assume the ADODB is the problem?

Yes. The ADODB library was introduced in Access 2000. However, you can
replace all of the following code:

On Open of my switchboard
Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

cmd.CommandText = strSQL
strSQL = "INSERT INTO UserLog(UserName,WhenUsed) " & _
"VALUES(""" & GetUser() & """,#" & _
Format(Now(), "mm/dd/yyyy hh:nn:ss") & "#)"
cmd.CommandText = strSQL
cmd.Execute

.. . . with the following lines of code:

' On Open of my switchboard
CurrentDb().Execute "INSERT INTO UserLog(UserName,WhenUsed) " & _
"VALUES(""" & GetUser() & """,#" & _
Format(Now(), "mm/dd/yyyy hh:nn:ss") & "#)", dbFailOnError

I did try adding in Microsoft Active X 2.0 then the error moved to
cmd.ActiveConnection = CurrentProject.Connection line

You don't need the ActiveX library for this code. Remove that reference
library and recompile the code.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


"T" <teiben@xxxxxxxxxxxxxxxx> wrote in message
news:1177966876.675561.281400@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
--------------------------------------------------------------------------------

I tried this audit trail code, using 2003 and it works great, I try
and use it at work w/97 and it blows up:
Compile error, user defined type not defined
I assume the ADODB is the problem? How would I change / fix this to
work w/97

Table called UserLog
w/field UserName txt
WhenUsed date & time

module
Option Compare Database
Option Explicit

Declare Function GetUserName Lib "advapi32.dll" Alias
"GetUserNameA" (ByVal _
lpBuffer As String, nSize As Long) As Long

Public Function GetUser() As String

Dim strBuffer As String
Dim lngSize As Long, lngRetVal As Long

lngSize = 199
strBuffer = String$(200, 0)

lngRetVal = GetUserName(strBuffer, lngSize)

GetUser = Left$(strBuffer, lngSize - 1)

End Function
'module ends'

On Open of my switchboard
Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

cmd.CommandText = strSQL
strSQL = "INSERT INTO UserLog(UserName,WhenUsed) " & _
"VALUES(""" & GetUser() & """,#" & _
Format(Now(), "mm/dd/yyyy hh:nn:ss") & "#)"
cmd.CommandText = strSQL
cmd.Execute

Any help would be greatly appreciated...I'm a hack at best
I did try adding in Microsoft Active X 2.0 then the error moved to
cmd.ActiveConnection = CurrentProject.Connection line



.

Relevant Pages

  • Re: VBScriptADO Max Length of String
    ... Dim objFSO, objFile, strServers, arrServers, strServer ... Dim strTableName, strSql, objConn, strSvrName, strDBName ... whole file is one string. ...
    (microsoft.public.windows.server.scripting)
  • Re: SQL Fustrations
    ... Dim strLogEvent As String ... Dim strSQL As String ...
    (microsoft.public.vb.general.discussion)
  • Re: repli
    ... Private Sub Program_NotInList(NewData As String, Response As Integer) ... Dim cmd As ADODB.Command ... Dim strSQL As String, strMessage As String ...
    (microsoft.public.access.forms)
  • RE: Creating a query using multiselect list box and text boxes
    ... Dim strSQL As String ... Dim strWhere As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: who is better to open an sqlserver2000 database
    ... Public Function GetResults(sSql As String) As ADODB.Recordset ... ' Description: returns a recordset ... Dim rs As ADODB.Recordset, bResult As Boolean ... > Dim cmd As New ADODB.Command ...
    (microsoft.public.vb.database.ado)