B
BruceS
Have an A2K app with a form that contains a "Clean Up" button whose logic
includes compacting the back-end database. When that part of the code tries
to run, I get an Error 7866: "(Application Name) can't open the database
because it is missing, or opened exclusively by another user."
Since I know that the .mdb is there, and (right now) I'm the only one who
could possibly have it open, I'm assuming that the error has to do with the
way I link to the back end tables. Could use some help figuring out how to
fix.
Current logic links to back end tables as follows:
'Connect to database tables. (This is in my "initialize" routine.)
'...
For x = 1 To TablesNum
If Not TblConnect(TablesAry(x)) Then Exit Function
Next x
'...
This is what it calls:
Public Function TblConnect(tbl As String) As Boolean
On Error GoTo TblConnectError
Set LinkedTbl = CurrentDb.CreateTableDef(tbl)
LinkedTbl.Connect = ";DATABASE=" & strConnect
LinkedTbl.SourceTableName = tbl
CurrentDb.TableDefs.Append LinkedTbl
TblConnect = True
Exit Function
TblConnectError:
MsgBox "Could not connect to table '" & tbl & "'." & vbCr & vbCr & _
"Error " & Err.Number & vbCr & _
Err.Description, _
vbOKCritical, "Database Error"
TblConnect = False
End Function
Prior to trying to compact the back end, I disconnect from all tables by
calling the following:
Public Function ConnectionsDrop() As Boolean
On Error GoTo CD_Err
Dim strTbl As String
Dim tbl As TableDef
ConnectionsDrop = True
For Each tbl In CurrentDb.TableDefs
If tbl.SourceTableName > "" Then
strTbl = tbl.Name
CurrentDb.TableDefs.Delete strTbl
End If
Next tbl
Exit Function
CD_Err:
ConnectionsDrop = False
MsgBox "Could not disconnect from one of the tables in the main
database." & vbCrLf & _
"[" & strTbl & "]", _
vbOKCritical, "Database Error"
Resume Next
End Function
Here is the logic I run to do the actual compact and repair:
'Drop all connections.
ConnectionsDrop
'Compact & repair database, outputting new database.
' MainDatabase is the full path & file name to the backend.
' NewName is a string made from MainDatabase but ending in ...NEW.mdb
' OldName is a string made from MainDatabase but ending in ...OLD.mdb
' Earlier, I test for either Old or New existing and, if so, delete them.
' Next line is one that generates the error:
Application.CompactRepair MainDatabase, NewName, True
'Rename current database to "OLD".
Name MainDatabase As OldName
'Rename compacted database as regular name.
Name NewName As MainDatabase
I've verified that all of the connections are being dropped by
ConnectionsDrop, and that the path/file name strings are good, but it looks
like I still, somehow, have the back end open. Can anyone help?
Thanks,
Bruce
includes compacting the back-end database. When that part of the code tries
to run, I get an Error 7866: "(Application Name) can't open the database
because it is missing, or opened exclusively by another user."
Since I know that the .mdb is there, and (right now) I'm the only one who
could possibly have it open, I'm assuming that the error has to do with the
way I link to the back end tables. Could use some help figuring out how to
fix.
Current logic links to back end tables as follows:
'Connect to database tables. (This is in my "initialize" routine.)
'...
For x = 1 To TablesNum
If Not TblConnect(TablesAry(x)) Then Exit Function
Next x
'...
This is what it calls:
Public Function TblConnect(tbl As String) As Boolean
On Error GoTo TblConnectError
Set LinkedTbl = CurrentDb.CreateTableDef(tbl)
LinkedTbl.Connect = ";DATABASE=" & strConnect
LinkedTbl.SourceTableName = tbl
CurrentDb.TableDefs.Append LinkedTbl
TblConnect = True
Exit Function
TblConnectError:
MsgBox "Could not connect to table '" & tbl & "'." & vbCr & vbCr & _
"Error " & Err.Number & vbCr & _
Err.Description, _
vbOKCritical, "Database Error"
TblConnect = False
End Function
Prior to trying to compact the back end, I disconnect from all tables by
calling the following:
Public Function ConnectionsDrop() As Boolean
On Error GoTo CD_Err
Dim strTbl As String
Dim tbl As TableDef
ConnectionsDrop = True
For Each tbl In CurrentDb.TableDefs
If tbl.SourceTableName > "" Then
strTbl = tbl.Name
CurrentDb.TableDefs.Delete strTbl
End If
Next tbl
Exit Function
CD_Err:
ConnectionsDrop = False
MsgBox "Could not disconnect from one of the tables in the main
database." & vbCrLf & _
"[" & strTbl & "]", _
vbOKCritical, "Database Error"
Resume Next
End Function
Here is the logic I run to do the actual compact and repair:
'Drop all connections.
ConnectionsDrop
'Compact & repair database, outputting new database.
' MainDatabase is the full path & file name to the backend.
' NewName is a string made from MainDatabase but ending in ...NEW.mdb
' OldName is a string made from MainDatabase but ending in ...OLD.mdb
' Earlier, I test for either Old or New existing and, if so, delete them.
' Next line is one that generates the error:
Application.CompactRepair MainDatabase, NewName, True
'Rename current database to "OLD".
Name MainDatabase As OldName
'Rename compacted database as regular name.
Name NewName As MainDatabase
I've verified that all of the connections are being dropped by
ConnectionsDrop, and that the path/file name strings are good, but it looks
like I still, somehow, have the back end open. Can anyone help?
Thanks,
Bruce