murray83
Games Collector
- Local time
- Today, 18:07
- Joined
- Mar 31, 2017
- Messages
- 845
extra copies of DB an issue ?
- Thread starter murray83
- Start date
- Local time
- Today, 10:07
- Joined
- Oct 29, 2018
- Messages
- 22,633
And these files are the duplicates of the FE, right?
- Local time
- Today, 12:07
- Joined
- Feb 28, 2001
- Messages
- 30,495
When you start getting numbered copied of the DB, that is a sign that Access is trying to simultaneously close and preserve data. It is not an unerring sign of corruption, but the odds are quite high that it is responding to something so corrupt it cannot be saved. Therefore, Access makes a copy of what was there before saving and closing everything. Here is how that happens.
When you do a Compact & Repair, Access does exactly what we sometimes suggest needs to be done. It makes an empty database and then, one internal object at a time, copies that object. It does exactly the same thing as if you made a new, "virgin" database and then imported everything in the older database. This is why, immediately after a C&R, tables appear to be sorted in the order of their primary keys.
The problem reveals itself if one of the objects is corrupted such that it cannot be copied - or more specifically, completely copied - into the new database. This corruption, so far as we can tell, most often takes the form of internal pointers in an object that do not point to the object header of the next object or element in a sequence. I.e a broken link in a chain of linked objects; a broken linked list. This is not the only possible corruption, but it is the most likely. So normally a C&R makes the new DB, copies the elements, and deletes the old DB. But when an error occurs during the process, it RENAMES the old DB to preserve stuff. And THAT is where your numbered copies originate. The date on the oldest copy tells you when the corruption first occurred. The number of numbered copies tells you how often you have done a failing C&R.
I haven't seen one of these in a long time, but it used to be that if you did a C&R and it failed, you would find a new system-generated table that enumerated the objects for which failures occurred.
When you do a Compact & Repair, Access does exactly what we sometimes suggest needs to be done. It makes an empty database and then, one internal object at a time, copies that object. It does exactly the same thing as if you made a new, "virgin" database and then imported everything in the older database. This is why, immediately after a C&R, tables appear to be sorted in the order of their primary keys.
The problem reveals itself if one of the objects is corrupted such that it cannot be copied - or more specifically, completely copied - into the new database. This corruption, so far as we can tell, most often takes the form of internal pointers in an object that do not point to the object header of the next object or element in a sequence. I.e a broken link in a chain of linked objects; a broken linked list. This is not the only possible corruption, but it is the most likely. So normally a C&R makes the new DB, copies the elements, and deletes the old DB. But when an error occurs during the process, it RENAMES the old DB to preserve stuff. And THAT is where your numbered copies originate. The date on the oldest copy tells you when the corruption first occurred. The number of numbered copies tells you how often you have done a failing C&R.
I haven't seen one of these in a long time, but it used to be that if you did a C&R and it failed, you would find a new system-generated table that enumerated the objects for which failures occurred.
- Local time
- Today, 10:07
- Joined
- Oct 29, 2018
- Messages
- 22,633
In that case, do what Doc suggested. Manually do a C&R and see if you get an error table. If the C&R goes without a hitch, try turning off Compact on Close and see if you continue to get the duplicate files.
I'm aware you confirmed this later in the thread, but are you really sure these files are all copies of the same database?
I don't think this is possible. Some of the files in the screenshot are so small, there can hardly be anything in them, while others are so big that there must be something in them.
"DatabaseN" is the default name of a new database created with Access. I rather think, some, most, or even all of the files you are seeing there are completely unrelated database files; many of them probably completely empty.
I suggest you open and check the contents of each of the files and then delete it if it contains nothing of value or is a duplicate without relevance.
For the future, I advise you never ever intentionally create an Access database application again without naming the file something other than "Database".
- Local time
- Today, 13:07
- Joined
- Feb 19, 2002
- Messages
- 47,590
You cut off some critical parts of the image. I'm going to guess the FE is stored on a server drive. The issue is that you probably do not have the correct permissions for the C&R to finish it's job so it is just leaving the backup it made and going back to the uncompacted version. Your FE is NOT getting compacted - well, actually it is - but the process isn't being fininshed because you do not have permission to delete/rename the original FE so it can be replaced by renaming the backup created by the C&R. The C&$ process is
A. Create a new empty database
B. Sort the data in each local table into PK order.
C. Append the table data table by table from the original to the new db
D. Copy all the objects and append into the new db
E. Delete the original FE (this seems to be what is failing)
F. Rename the numbered backup to the original name.
1. NEVER run your FE from a network drive. ALWAYS run it from your C: drive for the sake of efficiency and to reduce network traffic. We can offer solutions to help to manage this process silently.
2. Do not set the FE to compact on close. Since the user never changes objects in the FE, it should never bloat unless you have made the mistake of using Make table queries or delete/append queries - we can offer alternatives if you need to do this.
3. Under NO conditions should you ever C&R a database on a server drive. It is always better to download it to a local drive. Do the C&R, Then move it back.
A. Create a new empty database
B. Sort the data in each local table into PK order.
C. Append the table data table by table from the original to the new db
D. Copy all the objects and append into the new db
E. Delete the original FE (this seems to be what is failing)
F. Rename the numbered backup to the original name.
1. NEVER run your FE from a network drive. ALWAYS run it from your C: drive for the sake of efficiency and to reduce network traffic. We can offer solutions to help to manage this process silently.
2. Do not set the FE to compact on close. Since the user never changes objects in the FE, it should never bloat unless you have made the mistake of using Make table queries or delete/append queries - we can offer alternatives if you need to do this.
3. Under NO conditions should you ever C&R a database on a server drive. It is always better to download it to a local drive. Do the C&R, Then move it back.
