Database compaction error

Question

I have a public function "fncCompactDatabase" which I call from the click event of a command button on a form to compact and back-up the back-end of my database on demand.

It works very well when the form has no recordsource and the controls on the form are unbound.

But when I select a table or query name as the recordsource or add a bound control to the form, the back-up fails and pops up this error:

Error Number: 3704

Error Message: "You attempted to open a database that is already opened by user 'Admin' on machine 'MYCOMPUTER'. Try again when the database is available."

What could be the cause of this  error and how can I solve it? I am aware that all connections to a back-end database has to be closed to make the compact operation possible and that has been taken care of in the function on the line that closes all open objects prior the compaction.

Answer 1

You can't do that with an in-use database. Compact requires exclusive access, and you won't have those with bound forms unless you close out those forms (or reports). Bound forms create a connection to the database, and certain control (combos and listboxes, for example) can also create connections. You'd have to close all those before you could compact.

As far as backups, you'd be much better off using an actual backup program for things like that. The Windows backup program does a good job and is included free.

Answer 2

Hi Faraday,

Its pretty clear. You can't compact an repair a file if any of the tables are in use. But opening a Bound form, you are using a table in that file. So you can't have a bound form open while you try to run a Compact and Repair.

Answer 3

Yes, the windows backup functionality is fine, but I like to additionally backup my back end database from the front end while exiting the application.

Again, I'm aware that all connections to the back end need to be terminated to make the compaction possible and I included a line in my function that does that by closing all open objects

So why does it generate the error since the active form is also closed in the function prior to the compaction.

That's where I'm surprised.