Access 2007 Deleting database on Close/Compact

[Michael J. Strickland]

I have Compact on close set.

Ocassionally, when I close a database, Access has pops up a warning saying
it can't
delete the old database because it is Read-Only (which I know is not the
case). After clicking Ok (only option) to this prompt, it goes on to delete
both the original and the compacted database leaving me with no database at
all (not even in the Recycle bin).

The first time this happened, I lost my database entirely. Fortunately I had
a backup.
The next two times, when I saw the warning, I went into Windows Explorer and
made a copy (of Database.mdb) before I responded to the Warning box and this
saved it.

Anyone else seen this this problem or know of a fix/work-around for it?


--

 

[Jerry Whittle]

1. Turn off Compact on Close.

2. Investigate what is happening on your network that is causing connection
problems.

 

[Allen Browne]

Michael, where it this database stored? There is a known issue you keep the
database in an offline folder. Could this be the case?

If it is the offline folder issue, you can solve it by moving the database
into a regular folder. I strongly recommend against using offline folders
for Access databases.

If it is not an offline folder, I'm very interested to know more, and your
technique of copying the database before answering the dialog is worth
sharing. I'm interested in writing this up, as it could save other users
some grief.

 

[Michael J. Strickland]

--
---------------------------------------------------------------
Michael J. Strickland
Quality Services (e-mail address removed)
703-560-7380
---------------------------------------------------------------

Michael, where it this database stored? There is a known issue you keep
the database in an offline folder. Could this be the case?

It is in the "C:\My Documents" folder - not an offline folder as far as I
know.

Regarding Jerry Whittle's comment, I don't think it has anything to do with
the network.
We use a small network connecting to only one other PC and this file is on
my local PC.

If it is the offline folder issue, you can solve it by moving the database
into a regular folder. I strongly recommend against using offline folders
for Access databases.

If it is not an offline folder, I'm very interested to know more, and your
technique of copying the database before answering the dialog is worth
sharing. I'm interested in writing this up, as it could save other users
some grief.

To summarize the sequence:

1. When closing a database with "Compact on Close" selected, Access puts up
a message saying"

"Microsoft Office Access can't delete C:\My Documents\Access\[Filename]
after compacting it.
The compacted database has been renamed C:\My
Documents\Access\Database.mdb.

If you compact a database using the same name, Microsoft Office creates a
new compacted database
and then deletes the original database.
In this case, however the original database wasn't deleted because it is
read-only.

If you can, remove the read-only status, delete the original database,
and then rename the new
database using the original name.
If you can't remove the read-only status, inform your workgroup
administrator."

There is an "OK" button only at the bottom of this message and this is the
only way to continue.


2. After clicking on the "OK" button, you are presented with this message
box:


"Could not find file "C:\My Documents\Access\[Fielname].""

There is an "OK" button and a "Help" button on this message box.

It appears Access deletes the database after the first message box (Step 1
above).

Therefore, in order to save your database, you must go into Windows Explorer
and make a copy of the file "Database.mdb" prior to clicking on the "OK"
button in the first message box (Step 1 above).


Thanks for the help, Allen (and Jerry).

 

[Allen Browne]

Michael, thank you very much for posting these details.

Have posted your workaround (complete with a mock-up of the dialog) here:
http://allenbrowne.com/bug-DeleteOnCompact.html

Enjoy yor Christmas.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

--
---------------------------------------------------------------
Michael J. Strickland
Quality Services (e-mail address removed)
703-560-7380
---------------------------------------------------------------

Michael, where it this database stored? There is a known issue you keep
the database in an offline folder. Could this be the case?

It is in the "C:\My Documents" folder - not an offline folder as far as I
know.

Regarding Jerry Whittle's comment, I don't think it has anything to do
with the network.
We use a small network connecting to only one other PC and this file is on
my local PC.

If it is the offline folder issue, you can solve it by moving the
database into a regular folder. I strongly recommend against using
offline folders for Access databases.

If it is not an offline folder, I'm very interested to know more, and
your technique of copying the database before answering the dialog is
worth sharing. I'm interested in writing this up, as it could save other
users some grief.

To summarize the sequence:

1. When closing a database with "Compact on Close" selected, Access puts
up a message saying"

"Microsoft Office Access can't delete C:\My Documents\Access\[Filename]
after compacting it.
The compacted database has been renamed C:\My
Documents\Access\Database.mdb.

If you compact a database using the same name, Microsoft Office creates
a new compacted database
and then deletes the original database.
In this case, however the original database wasn't deleted because it is
read-only.

If you can, remove the read-only status, delete the original database,
and then rename the new
database using the original name.
If you can't remove the read-only status, inform your workgroup
administrator."

There is an "OK" button only at the bottom of this message and this is the
only way to continue.


2. After clicking on the "OK" button, you are presented with this message
box:


"Could not find file "C:\My Documents\Access\[Fielname].""

There is an "OK" button and a "Help" button on this message box.

It appears Access deletes the database after the first message box (Step 1
above).

Therefore, in order to save your database, you must go into Windows
Explorer and make a copy of the file "Database.mdb" prior to clicking on
the "OK" button in the first message box (Step 1 above).


Thanks for the help, Allen (and Jerry).