97 ADODB error

[teiben]

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

 

[Fifty2One]

Time to get an upgrade at work to 2003 or later!
I always try to run my development machines at a lower and slower spec than the machines I am developing for, or at least one step behind... that way things run the same at least... or better and faster in a lot of cases...

 

[pbaldy]

Try this:

Dim strSQL As String

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

CurrentDb.Execute strSQL

 

[boblarson]

I could be mistaken, but I believe ADO came AFTER Access 97, so it wouldn't support it.

 

[pbaldy]

That is my belief too, Bob, so I'm sure that's where the error comes from. I'm assuming that the execute method will work in 97, but I don't have a copy around to test with.

 

[RoyVidar]

User defined type not defined, comes from not having a reference to the appropriate library, I think.

I'm unable to test, but as can be found through a web search, there's (almost) no problems connecting to other databases with ADO in Access 97, same with other Jet databases than the current. ADO is a COM library that can be referenced and used.

The challenge arises because CurrentProject.Connection isn't an ADO property, but an Access.Application property, which wasn't introduced until the 2000 version.

Since there's no CurrentProject, one would need to build the connection string, and open the connection based on it, as demonstrated here (though I'm not entirely sure the connection string is correct).

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/dfd5d794acd89ffa/

The execute method of currentdb works, but I think I'd use escape characters in the date formatting, or use ISO 8601, else it barfs where i live

Format$(Now(), "mm\/dd\/yyyy hh:nn:ss")
Format$(Now(), "yyyy-mm-dd hh:nn:ss")

 

[teiben]

pbaldy, it worked like a charm, would rather not use ADODB or DOA if possible, curious to see how it works w/2003

Fifty2One, sometimes you have work w/the crap they give you.

Thanks all

 

[pbaldy]

pbaldy, it worked like a charm, would rather not use ADODB or DOA if possible, curious to see how it works w/2003

That should also work fine in 2003.