I'm having a vexing problem with using filecopy and compactdatabase on a
Jet back end I'm sure I did not have with A97. It seems to me that
A2003 is much slower in shutting down the back end ldb than A97 and the
code below (for compactdatabase ) gets caught and throws an error related
to the BE still being open.
I would really appreciate any suggestions. I first ran into this with
FileCopy and settled on a warning to close all forms first and then the
user has to again choose a menu item to do the function.
Here's the code. I leave out the code for the fGetLinkPath function
which I believe I got from the Access Web & Dev Ashish. I do include
the fCloseAll function (closes open forms) and the actual function
performing the compact. The error is thrown at the line
"CompactDatabas e strNew, strCompact" in the last function below and
indicates the back end is still open:
Function fCompactBe()
Dim strPath As String
If MsgBox("This will close all screens and optimize (compact) your
data file" & vbCrLf & vbCrLf & _
"Continue?" , vbQuestion + vbOKCancel, "Shut Down and Optimize
Data File?") = vbCancel Then GoTo Exit_Proc
fCloseAll
strPath = fGetLinkPath("T BL_GAME")
fCompactDataFil e strPath
DoCmd.OpenForm "frmsetup", acNormal
Exit_Proc:
Exit Function
End Function
Function fCloseAll() As Boolean
'Closes all forms, getting rid of any connections
'to the back end
Do While Access.Forms.Co unt > 0
DoCmd.Close acForm, Forms(0).Name
Loop
End Function
Function fCompactDataFil e(strNew As String)
'strNew is path to back end
Dim strCompact As String
'first designate strCompact as the newpath with a wierdname:
strCompact = Left(strNew, (Len(strNew) - 3)) & "_COMPACT.m db"
'renames whatever.mdb to whatever_COMPAC T.mdb
CompactDatabase strNew, strCompact
FileCopy strCompact, strNew 'copy over new file with successfully
compacted one
Kill strCompact
End Function
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto