Existance of .LDB prevents Rename, File Copy, Repair/Compact

[Guest]

I want to backup, repair & compact the backend MDB from code within my
application. I have a function that works fine in a simple app that links to
tables in the backend MDB (lets call it Tables.mdb). However, the same
function fails in my more complex app.

I get errors if I try to:
Rename Tables.mdb to Tables.bak
Copy Tables.mdb to another folder
Repair/Compact Tables.mdb

In the simple app, I notice that Tables.LDB does not exist at the time I try
to run my function. However, it DOES exist when I try to run my more complex
app. I assume that's because I have a Form open that is bound to tables in
Tables.mdb when I call my function. And, I assume it's the existance of the
..LDB that is causing the "permission denied" and similar errors.

So, I've tried closing all Forms (in code) prior to trying to execute the
copy / rename / repair commands. However, the .LDB still exists, and the
errors still occur.

How can I get around this problem?

 

[Allen Browne]

You cannot reliably back up a database while it is in use.

It's not too difficult to just back up the data into another database. The
basic idea is:
strTable = "Table1"
strFile = "C:\MyPath\MyFile.mdb"
strSql = "SELECT * INTO [" & strTable & "] IN """ & strFile & """ FROM
[" & strTable & "];"
dbEngine(0)(0).Execute strSql, dbFailOnError

The problem comes if you need to restore this data. Because you did not have
exclusive access to the database, it is possible that the data you backed up
is inconsistent, e.g. that someone added an Order after you backed up the
Orders table, but before you backed up the OrderDetail table, so your backup
has an OrderDetail that does not relate to an Order. The chances of that may
be small, but the issue applies to edits and deletions as well, so it is not
possible to get a reliable, consistent copy while the file is in use.