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.
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.
But you might as well use CurrentDb instead.
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
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