Compacting Back-end DB

[Guest]

I have a front end/back end architecture DB application. I'm trying to
compact the back end following a reload procedure. I have the following code;

If Len(Dir$(DatabaseName)) > 0 Then

If StrComp(Right$(DatabaseName, 4), ".mdb", vbTextCompare) = 0 Then
strBackupFile = Left$(DatabaseName, Len(DatabaseName) - 4) &
".bak"

If Len(Dir$(strBackupFile)) > 0 Then
Kill strBackupFile
End If

Name DatabaseName As strBackupFile

DBEngine.CompactDatabase strBackupFile, DatabaseName, , ,
";pwd=letmein"
End If

End If

When ever this function is called, an error 75, Path/File Access Error. On
debugging, it is occuring during the Name. DatabaseName holds
C:\stuff\nom\NOM_BE.mdb and strBackupFile holds C:\stuff\nom\NOM_BE.bak

Any help would be greatly appreciated.

 

[Arvin Meyer]

You cannot rename the database while it is open, nor can you compact it.
You'll need to copy it to a new file, then compact that file. If you close
all existing connections to the back-end database, you can compact, but it
is difficult to ascertain when it is actually closed and you'll have to have
an error handler in place that keeps retrying. The best method is to use a
maintenance database or VB utility that performs the necessary functions.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access