ADODB using 97

[T]

--------------------------------------------------------------------------------

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

['69 Camaro]

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/ex...ributors2.html for contact
info.
"T" <te****@wideopenwest.comwrote in message
news:11**********************@l77g2000hsb.googlegr oups.com...

--------------------------------------------------------------------------------

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

[Tom van Stiphout]

On Mon, 30 Apr 2007 14:16:28 -0700, "'69 Camaro"
<Fo**************************@Spameater.orgZERO_SP AMwrote:

Or alternatively, you create a parameterized Append query in Access:
PARAMETERS parUserName text, parWhenUsed DateTime;
INSERT INTO UserLog( UserName, WhenUsed )
SELECT [parUserName] AS Expr1, [parWhenUsed] AS Expr2;

Then call it using this code:
dim qd as dao.querydef
set qd=currentdb.querydefs("myQuery")
qd!parUserName = GetUser()
qd!parWhenUsed = Now 'No need to format a date field
qd.execute dbFailOnError

parUserName and parWhenUsed are query parameters you specify under
menu option Query Parameters.

-Tom.

>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

<clip>

[RoyVidar]

"'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM>
wrote in message <13*************@corp.supernews.com>:

Hi.

>I assume the ADODB is the problem?

Yes. The ADODB library was introduced in Access 2000.

I don't think the ADO library is the problem, except that it seems
the errormessage indicates there's no reference to the library.

ADO is a separate library that can be referenced and used in Access 97.

Here, the next problem would be the CurrentProject.Connection, which
isn't part of ADO, but Access. That property wasn't added to Access
until the the 2000 version, which makes using the current database
in Access 97 a bit awkward ;-)

Danny Lesandrini demonstrates the principles of how it can be done here
http://groups.google.com/group/comp....5d794acd89ffa/

--
Roy-Vidar

['69 Camaro]

Hi, Roy-Vidar.

I don't think the ADO library is the problem

The error message that the user-defined type wasn't defined was due to the
fact that he didn't have the ADODB library referenced, and he truly didn't
need to have it referenced for Access 97, because the default libraries
easily handled the task he needed to do. That's why I agreed that the ADODB
library was his problem.

ADO is a separate library that can be referenced and used in Access 97.

Yes. It can be. And DAO 3.6 can also be referenced in Access 97, along
with other libraries for later versions of Office applications. But just
because one can reference those libraries and the code compiles doesn't mean
that one should do so. Access 97 wasn't designed to work with those
versions of the libraries. Screwy things can happen when the wrong library
or the wrong version of a library is used, so it's not recommended. As an
extreme example, look at what happens when Access 2000 without SP-3
references the VBA 6.2 or 6.4 library from Office XP or Office 2003, and the
code compiles at runtime. The code project corrupts.

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/ex...ributors2.html for contact
info.
"RoyVidar" <ro*************@yahoo.nowrote in message
news:mn***********************@yahoo.no...

"'69 Camaro" <Fo**************************@Spameater.orgZERO_SP AM>
wrote in message <13*************@corp.supernews.com>:

>Hi.

>>I assume the ADODB is the problem?

Yes. The ADODB library was introduced in Access 2000.

I don't think the ADO library is the problem, except that it seems
the errormessage indicates there's no reference to the library.

ADO is a separate library that can be referenced and used in Access 97.

Here, the next problem would be the CurrentProject.Connection, which
isn't part of ADO, but Access. That property wasn't added to Access
until the the 2000 version, which makes using the current database
in Access 97 a bit awkward ;-)

Danny Lesandrini demonstrates the principles of how it can be done here
http://groups.google.com/group/comp....5d794acd89ffa/

--
Roy-Vidar