DBEngine Error

[llnoble]

We moved an Access 2002 database to Access 2007 and are now receiving an
error stating the system can't findthe name "DBEngine" entered in the
expression. No code changes were made. Any ideas?

 

[Danny Seager]

what code are you getting the error on?

 

[llnoble]

The VBA code is in a Sub Complete_Click module, as follows:

Set rstlog = dbs.OpenRecordset("tblSecurityLog", dbOpenDynaset)
rstlog.AddNew
rstlog!ID = GetUniqueKey(Tbl)
rstlog!Date_Modified = Now
rstlog!Usr = DBEngine.Workspaces(0).UserName
rstlog.UPDATE
rstlog.Close

Is that what you were looking for?

llnoble

 

[david]

Check your references (tools, references) in the VBA project.

You haven't shown your declaration for rstlog or dbs. I assume
that you are using declarations, and "Option Explicit"?

Change dbEngine to
Application.dbEngine

Also, is this code is executed at startup? Some things aren't immediately
initialised.

(david)

 

[llnoble]

I checked my references, but please excuse my lack of experience with VBA,
I'm not sure what I was checking for. No references to a DBEngine. This
application was written by someone else and given to me to attempt to fix.

I am using "Option Explicit", following a "Option Compare Database."
Followed by the following:

Sub CompleteAdjudication_Click()
On Error GoTo Err_CompleteAdjudication_Click

Dim dbs As Database, rstlog As Recordset

Set dbs = CurrentDb

Set rstlog = dbs.OpenRecordset("tblSecurityLog", dbOpenDynaset)
rstlog.AddNew
rstlog!ID = GetUniqueKey(Tbl)
rstlog!Date_Modified = Now
rstlog!Usr = DBEngine.Workspaces(0).UserName
rstlog.UPDATE
rstlog.Close

And yes, I believe this code is executed at startup.

Leesa

 

[Douglas J. Steele]

Are you using the MDB or ACCDB format in Access 2007?

If an MDB (or MDE), you need a reference to Microsoft DAO 3.6 Object
Library. If an ACCDB (or ACCDE), you need a reference to Microsoft Office
12.0 Access Database Engine

 

[llnoble]

I'm using an MDB file, and the Microsoft DAO 3.6 Object Library reference is
already checked.

llnoble

 

[david]

Change dbengine to application.dbEngine
Change CurrentDB to application.CurrentDB

This can help fix the order of initialisation at startup, so that dbEngine
is initialised when you try to use it in your startup code.

Add a new reference to the reference list, close the list, then reopen
the list and remove the new reference (to refresh the reference list)

Best practice is to do your declarations like this:

Dim dbs As DAO.Database
Dim rstlog As DAO.Recordset

Is there an ADO reference in your reference list? If so, you
may need to remove it, or change the reference order, or do
your declarations using DAO. as shown

(david)

 

[llnoble]

I made all the changes you suggested, as well as the references tweeking, but
am still getting the "can't find the name 'DBEngine' entered in the
expression" error.

No ADO references were set.

Leesa

 

[david]

If you have changed DBEngine to Application.dbEngine, I don't
think you can be getting:

"can't find the name 'DBEngine' entered in the expression"

So if you are still getting that error, I think that there still must
be a place in your code that hasn't been changed.

One possibility is that your code is corrupted, and you don't
see the code that is actually being run. So you should 'decompile'
your database. Instructions on recovery from corruption are here:
http://allenbrowne.com/recover.html

Actually, you should probably 'decompile' the Access 2002 database,
and convert it again to 2007, but you can start with just trying the 2007
version.

Are you using Windows Vista?

(david)

 

[David W. Fenton]

rstlog!Usr = DBEngine.Workspaces(0).UserName

Is there some circumstance where DBEngine.Workspaces(0).UserName and
CurrentUser() will return different results? I've never used
DBEngine.Workspaces(0).UserName to get the Jet username, so don't
know why you'd be doing it at all.

 

[David W. Fenton]

Dim dbs As Database, rstlog As Recordset

These should certainly be disambiguated:

Dim dbs As DAO.Database, rstlog As DAO.Recordset

But I don't think that's the cause of the problem you're having. I
think you can eliminate any problem by getting rid of your call to
DBEngine and using CurrentUser() instead.

 

[Brendan Reynolds]

Is there some circumstance where DBEngine.Workspaces(0).UserName and
CurrentUser() will return different results? I've never used
DBEngine.Workspaces(0).UserName to get the Jet username, so don't
know why you'd be doing it at all.

Well ... there is a difference, albeit a small one.

Here's my test code ...

Public Sub TestUserNames()

Debug.Print CurrentUser()
Debug.Print DBEngine.Workspaces(0).UserName

End Sub

.... and here's the result in the Immediate window ...

testusernames
Admin
admin

The difference being that DBEngine.Workspaces(0).UserName does not
capitalize the initial letter. So the following code ...

Public Sub TestUserNames()

Debug.Print StrComp(CurrentUser(), "admin", _
vbBinaryCompare)
Debug.Print StrComp(DBEngine.Workspaces(0).UserName, "admin", _
vbBinaryCompare)
Debug.Print
Debug.Print StrComp(CurrentUser(), "Admin", _
vbBinaryCompare)
Debug.Print StrComp(DBEngine.Workspaces(0).UserName, "Admin", _
vbBinaryCompare)
End Sub


.... returns these results ...

testusernames
-1
0

0
1

This is of course an artificially contrived example.

Maybe the code was originally written by someone more familiar with VB than
Access. Or someone who thought they might want to port the code to VB
someday.

Anyway, while it probably would be safe to change the code to use
CurrentUser instead of DBEngine.Workspaces(0).UserName (unless there is code
doing something like my example above, which seems unlikely) might that not
be a case of treating the symptom rather than the underlying cause? As far
as I know, DBEngine.Workspaces(0).UserName should work, so if it isn't
working, something is wrong that probably needs to be fixed - mostly likely
either a references problem or code corruption, as others have suggested.

 

[David W. Fenton]

The difference being that DBEngine.Workspaces(0).UserName does not
capitalize the initial letter. So the following code ...

Public Sub TestUserNames()

Debug.Print StrComp(CurrentUser(), "admin", _
vbBinaryCompare)
Debug.Print StrComp(DBEngine.Workspaces(0).UserName, "admin",
_
vbBinaryCompare)
Debug.Print
Debug.Print StrComp(CurrentUser(), "Admin", _
vbBinaryCompare)
Debug.Print StrComp( , "Admin", _
vbBinaryCompare)
End Sub


... returns these results ...

testusernames
-1
0

0
1

This is of course an artificially contrived example.

Yes, absolutely contrived, as Jet security doesn't distinguish case
differences, so testing for case differences would just be dumb.

But if you try it with a different logon, you'll see that
CurrentUser() returns exactly what the user typed in, while the
DBEngine version does something weird with case (if' you log on as
"dwf" it returns "DWF", for example).

Maybe the code was originally written by someone more familiar
with VB than Access. Or someone who thought they might want to
port the code to VB someday.

You didn't delve far enough into the behavior. CurrentUser() returns
all user names *except* Admin in the case they were typed.
DBEngine(0).UserName returns a version with the case transformed in
an inconsistent fashion.

Anyway, while it probably would be safe to change the code to use
CurrentUser instead of DBEngine.Workspaces(0).UserName (unless
there is code doing something like my example above, which seems
unlikely) might that not be a case of treating the symptom rather
than the underlying cause? As far as I know,
DBEngine.Workspaces(0).UserName should work, so if it isn't
working, something is wrong that probably needs to be fixed -
mostly likely either a references problem or code corruption, as
others have suggested.

Why try to resolve a problem that you wouldn't encounter at all if
you used simpler methods?