DBEngine problem (1 Viewer)

JANR

Registered User.
Local time
Today, 07:31
Joined
Jan 21, 2009
Messages
1,623
I just need to rant a little, I have this little old code which sits innocently smack i the middle of a critical procedure in my db.

Code:
Public Function GetBEFolder(pTableName As String) As String
    Dim strFullPath As String
    Dim I As Long
    strFullPath = Mid(DBEngine.Workspaces(0).Databases(0).TableDefs(pTableName).Connect, 11)

    For I = Len(strFullPath) To 1 Step -1
        If Mid(strFullPath, I, 1) = "\" Then
            GetBEFolder = Left(strFullPath, I)
            Exit For
        End If
    Next
End Function

It has work for eons but to day it bit me HARD, all of a sudden it errors on

strFullPath = Mid(DBEngine.Workspaces(0).Databases(0).TableDefs(pTableName).Connect, 11)

and because of this the rest of the procedure tanked and gave me a taskmanager full of hidden instances of Excel generated for another function in my chain of subs. In a mild panic I tried everything to fix it, eventually I just closed the DB and reopen it and everthing was working again.

So what went wrong?? To day I had to modify one of my linked .txt files and to update my db I ran the procedure and BOOM, now experience developers know where I went wrong but to other beginners who like me use code found on the net and do not fully understand it only that it works can be dangerous. I was bitten by the old

DBEngine vs. CurrentDB

You will find a lot og code out on the net that uses DBEngine(0)(0) because of its so called speed advantages over CurrentDb but like every other supercode it has it's limitation among other it dosen't refresh the TableDef collection without explicitly call for it and that loses it speed advantage and more code to write than just use CurrentDB.

To plug this hole in my function I need to add this command.

Code:
DBEngine.Workspaces(0).Databases(0).TableDefs.Refresh

But you might as well use CurrentDb instead.

Code:
Public Function GetBEFolder(pTableName As String) As String
    Dim strFullPath As String
    Dim I As Long
    Dim db As DAO.Database

    Set db = CurrentDb

    strFullPath = Mid(db.TableDefs(pTableName).Connect, 11)
    For I = Len(strFullPath) To 1 Step -1
        If Mid(strFullPath, I, 1) = "\" Then
            GetBEFolder = Left(strFullPath, I)
            Exit For
        End If
    Next

End Function

It was in the end a lesson for me and thanks to sites like AWF the solutions is out there you just need to find and spend alot of time reading what is out there.

JR
 

ChrisO

Registered User.
Local time
Today, 17:31
Joined
Apr 30, 2003
Messages
3,202
Thanks for the post.

Somewhere in the back of my memory I seem to recall that the wizards can also play games with the TableDefs.

Another thing you may want to look at…
That procedure can be written without the need for a DAO reference. You will probably be using a DAO reference somewhere else in your database so you won’t be able to delete it but you can remove this procedure’s dependency on it.

In Access 2000, Microsoft added a hidden reference to DAO because they were trying to shift people to ADO. I guess they found they couldn’t totally remove DAO from their code and so created the hidden reference.

By Access 2003, DAO was back in as a standard reference but that reference, like all references, can get broken. It’s a personal preference of mine to remove all references if I can. That means starting at the procedure level and remove the dependency on any reference. Rather like late binding DAO and, of course, DAO constants also need duplicating. Usually there are only two constants we need 2 and 128 (dbOpenDynaset and dbFailOnError).

A bit long winded but the code actually reduces in size as well as removing the need for the reference:-

Code:
Public Function GetBEFolder(pTableName As String) As String
    Dim strFullPath As String
    Dim I As Long

    strFullPath = Mid(CurrentDb.TableDefs(pTableName).Connect, 11)
    
    For I = Len(strFullPath) To 1 Step -1
        If Mid(strFullPath, I, 1) = "\" Then
            GetBEFolder = Left(strFullPath, I)
            Exit For
        End If
    Next

End Function

Chris.
 

JANR

Registered User.
Local time
Today, 07:31
Joined
Jan 21, 2009
Messages
1,623
Thanks for the post Chris, I acutally tested it with that code by setting
CurrenDb.Tabledefs and it does work. Perhaps i was being overly cautious by explicitly creating a variable db and setting that to CurrentDb.

I usually use CurrentDb to all my code where needed execpt when dealing directly with the table collection.

JR :)
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 17:31
Joined
Jan 20, 2009
Messages
12,849
Perhaps i was being overly cautious by explicitly creating a variable db and setting that to CurrentDb.

Remember that CurrentDb is not an object but a Method of the Application so a reference to it is not a direct reference to an object but a function that does quite a lot of work including refreshing the TableDefs Collection which is quite a slow process.

Setting a variable for CurrentDb is a good idea if it is to be referred to more than once in a procedure and especially if in a loop.

BTW Instead of the loop to find the last instance of the slash you can use the InstrRev function.

Code:
GetBEFolder = Left(strFullPath, InStrRev(strFullPath) - 1)
 

Users who are viewing this thread

Top Bottom