A2003 Slow in Shuting Down BE ldb

[Tim Marshall]

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
"CompactDatabase 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("TBL_GAME")

fCompactDataFile 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.Count > 0

DoCmd.Close acForm, Forms(0).Name

Loop

End Function

Function fCompactDataFile(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.mdb"
'renames whatever.mdb to whatever_COMPACT.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

[Steve Jorgensen]

Have you tried DBEngine.Idle before trying to copy the file?

On Sun, 30 Jan 2005 21:55:55 -0330, Tim Marshall
<TI****@PurplePandaChasers.Moertherium> wrote:

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
"CompactDatabase 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("TBL_GAME")

fCompactDataFile 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.Count > 0

DoCmd.Close acForm, Forms(0).Name

Loop

End Function

Function fCompactDataFile(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.mdb"
'renames whatever.mdb to whatever_COMPACT.mdb

CompactDatabase strNew, strCompact

FileCopy strCompact, strNew 'copy over new file with successfully
compacted one

Kill strCompact

End Function

[Tim Marshall]

Steve Jorgensen wrote:

Have you tried DBEngine.Idle before trying to copy the file?

Thanks, Steve, no I haven't - I've never heard of this... I'll see what
I can find in help.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[Tim Marshall]

Steve Jorgensen wrote:

Have you tried DBEngine.Idle before trying to copy the file?

No, that did not work. 8( I tried

Access DBEngine.Idle (bothe with and without dbRefreshCache)

in front of the compactdatabase command and also immediately after the
function which closes forms and I still threw 3356 "you attempted to
open a database that is already opened exclusively by user 'ADMIN' on
machine '<my machine name>'. Try agains when the database is available."

This is a single user app, as well, not a multi user. The back end will
most likely always be on the same machine.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto