Automatic Compact and Repair's backup file name inconsistent

Question

I have not been able to determine why, but when some users repair a shared Access database, it creates the backup file with one name format, and other users have a different name format.
- This is not a manual repair.  It is an automatic one initiated by MSAccess.exe when attempting to open the file.

e.g.  "Backup of GlobalDb.accdb" vs "GlobalDb_Backup.accdb"

Is there a way I can standardize this.  I am trying to keep track of how often it occurs and when to see if there are any patterns and to try to convince management to go another way, but this is making it a little more difficult.

I also find it annoying. :)

Thank you.

Answer 1

Are all your users running the same version, build of Access?

Users should typically not be compacting/repairing a database, this is normally something that is done by the Admin.  Why do they need to do it?  Is this a frequent thing, which would indicate a db design issue?

How exactly are they performing the Repair?  They aren't doing a Compact and Repair Database?  If so, C&R does not normally produce a new file with a new name.  In my experience, Backup files only occur when Access crashes or fails in its operation, so indicative of a problem.

---

Daniel Pineault, 2010-2019 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

Users should not be sharing a front end file or an unsplit database file, so I'm assuming that you are calling the CompactRepair method to create a backup of a back end file rather than as an attempt to repair a corrupted file.  The latter should be a task for the database administrator, as Daniel has pointed out.

You might like to take a look at BackupDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file illustrates how to backup the back end file from the front end, calling the CompactRepair method to do so.  The backup file must first be created in the desired location, under a fixed name.  The demo then provides the options of replacing the backup file each time, or creating and retaining dated backups.  It also includes a means to backup the back end to a specified location on OneDrive.

In a multi-user environment, if a user attempts to backup the back end while it is currently in use by another user, or the current user has a table open, an error is raised, and the backup will not be undertaken.

---

Ken Sheridan, Stafford, England

Answer 3

There is no backup routine being run.
This file is one of many copies of a shared Access database that exists on a network share.
The backups, which occur on all the copies at different times, occur because of Access's automatic repair when opening if it detects the file is corrupt.  The many copies were created to limit the sharing of the files.

We tried other things, but the Access database is updated often enough and people would not update any local copies they have potentially causing issues for other users, so we decided to keep several copies of the file (9 copies currently) on a network share.

We have tried to determine the reasons for the corrupt databases, but have not figured out the reasons behind this other than momentary network drops.  They are also certain actions inside access that place the database in an unknown state which closing typically addresses.  Thankfully they are front end databases, so no real data is stored in there.

We have Office 2013 on a test server, but other than that, all users should be using Office 2019, some of those were upgrades from a previous version.  I wonder if the reason for the different names is because some were upgraded from older versions.
I can't say whether all are using the same build of Access 2019 or not, and several had Office 365 installed previously, so I'm unsure if that would have any affect on this, but most get regular updates, so they should be moving to the newer builds as they become available.

Answer 4

Why are you not using the standard set-up for which Access is designed to be used in a multi-user environment, i.e. a single back end in a shared location to which all users have full permissions, and separate copies of the front end distributed either to each user's local machine, or to 'personal' locations on the system to which only the user and the database administrator has permissions?

---

Ken Sheridan, Stafford, England

Answer 5

We don't have any updating system in place to update the front end.  The back end is shared - a SQL database.

We really want to keep the updating of the app as simple as possible by limiting the locations where it exists.

What I would prefer is to migrate the app to a .NET exe, but that will take more time than I have right now.

We had problems in the past because people took their own copies because they felt like it and then didn't update them and some things didn't work, and they didn't have access to some features, and sometimes it caused problems for others.  Especially with some people working from home, them keeping a file local was a downside.  That was why we created the multiple copies, and we also have it as a published app on our terminal server.

Unfortunately I can't control the behaviour of some people. 🙄

Answer 6

We don't have any updating system in place to update the front end.

For an automated solution see:

https://www.autofeupdater.com/

---

Ken Sheridan, Stafford, England

Answer 7

I appreciate the suggestion, but we don't want that.  I am sure I could create an update system myself if we wanted it.

One concern is that users tend to leave it open for days on end, until we need them to get out, either because of an update, or a problem with the database.

If they had their own copies, probably 1/3 would leave it open for weeks if they can, preventing deploying updates.

With it being on a server I can see who has it open and ask them to close, or force them out.

I'd rather just know why the backups names are inconsistent and deal with that.

Thank you anyway.

Answer 8

The answer to your question is no. No one can "standardize" the naming of these files being created. As previous people have said, you should use ACCESS in the manner in which it was developed. If you don't, you will continue to suffer crashes caused by various means. The GlobalDb_Backup.accdb file is being created by ACCESS as a result of it crashing for some reason. It probably crashes because it is not being used as designed. The GlobalDb_Backup file is useless. It is not really a backup file as you would think. It's created because of ACCESS crashes. Fix the crashes and you fix the *_Backup files being created. As to the Backup of GlobalDb.accdb file, I have no idea how it is created. I would ask users if when ACCESS crashes, they name it themselves.

I would take the advice given and create one good development file with restricted access. I would make sure it compiles properly and works as designed. If it is already properly split, then distribute a copy to each user to be installed on their computer that is linked to the Backend data file. I would remove any code or macro having anything to do with C & R. This command should be used ONLY by the developer in the development process. Never by users.

As to users leaving their file open, that is a personnel problem that should be dealt with as necessary. There is code out there however that can detect if a user file has been idle for a period of time and then you can close that file and its connection. I have never used it myself, but there have been inquiries in this forum as to how to do that. You might want to search for code to do that.

Answer 9

We actually have been using Access for years (since Access 2.0), and it was originally developed to be used in a multi-user environment and even on stable networks.  If it wasn't then they would have the ldb and lccdb files.  It is possible that the recommendations have changed over the years.
There actually have been several support articles regarding corrupt access databases on network shares.  Some regarding fixes to Access and others changing the Network settings on the server.

Unfortunately it can be very difficult to guarantee 100% network stability.

I am not so worried about suffering from the crashes, although it is an unpleasant thing.

Also, until I am assigned time to address any of these things, there is little I can do.  I just hoped that this would be a low hanging fruit kind of thing that could be addressed.

Finally, I am 100% certain that the "Backup of GlobalDb" files are created for the same reason as "GlobalDb_Backup" and automatically by MSAccess, I just don't know why the installation on some machines does it one way vs the others.  They are not created manually by any of our users.

Answer 10

Oh and by the way, we do have a master copy of the file.  We have had this for years. :)

I appreciate everyone's attempts to assist me with this, even if some were not exactly what I was asking about.

Answer 11

e.g.  "Backup of GlobalDb.accdb" vs "GlobalDb_Backup.accdb"

Is there a way I can standardize this.

Hi MD,

Probably ypu have a routine that searches for the generated backup-files.

Is it possible to extend that routine so that it searches for both systematics? It makes "you" independant of how the backup-file is named.

Imb.

Answer 12

Unfortunately, I actually have no routine. I've only been doing a manual cleanup of these files.

It would probably make sense to have one and would likely be the fastest thing to do.

I really hoped someone from Microsoft would have chimed in on this.  Someone still could.

Answer 13

Microsoft very rarely participates in forums.

Secondly, many of the contributors here have substantial IT experience.

Such files occur from crashes, so the real question is why is your db crashing?  That should be the real focus here.  You should be seeing such files in the first place, what they are labeled is immaterial.

So why do your db crash?  Can you isolate the cause?

---

Daniel Pineault, 2010-2019 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 14

I honestly don't know why it happens. It usually occurs while one user is in and that person doesn't notice any problems. It is likely because two or more people are accessing it at the same time, but beyond that I have no idea.

I wish there was a way I could make the files read only, but still multi user as that would eliminate the problems. Any temporary data needed could be held in temp files instead. Why can't Microsoft give us that option... 

Answer 15

I honestly don't know why it happens.

Hi MD,

In one of my applications it happened regularly that there was a hick up in the program, and Access more or less asked the user to make a backup.

The reason for this was a very old(fashioned) LAN, that had its problems. After the router of the LAN was modernized, we haven't seen the problem any more.

Problems with the two simultanuous users I have not yet met. But that is perhaps because any record is only locked during a very short time. For new records all data is collected in an unbound form, and only after pressing the Save-button the record is written. For existing records the Dirty property is cleared (the value is saved) after leaving the control.

Imb.

Answer 16

We have actually replaced our switch a few times over the years.

The data is all stored in a SQL database via linked tables so it shouldn't have to modify anything in the accdb file.

Unfortunately something still happens occasionally and we get these problems. It also does not appear to be limited to one or two users, so I am at a loss as to what is triggering it. 

Answer 17

Since all your data is stored in SQL Server, the good news here is that as long as you have a matter copy of the front end, you can delete these files without issue.  They serve no purpose.

As for the issue itself, it can be traps to figure out.  You need to try and identify patterns of crashing.  Does it always happen while doing a specific action using a specific form/report, time of day, on certain computers, ...

Are you using the proper odbc drivers?

When we the last time the database was decompiled, compacted, compiled, compacted.

Any difference in behavior of you use an accde?

---

Daniel Pineault, 2010-2019 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 18

It is likely because two or more people are accessing it at the same time, but beyond that I have no idea.

It has long been recognized that this is the single greatest cause of corruption, so as long as you continue with your current modus operandi you will continue to experience this problem.  It seems to me that your choices are to either accept this and put in place a remedial protocol, or to adopt the standard practice of providing each user with their own copy of the front end.  

If you do opt to persist with your current approach, an alternative to compacting and repairing the file would be to delete the corrupt front end and replace it with a new copy of the 'master' file.

---

Ken Sheridan, Stafford, England

Answer 19

Since all your data is stored in SQL Server, the good news here is that as long as you have a matter copy of the front end, you can delete these files without issue.  They serve no purpose.

As for the issue itself, it can be traps to figure out.  You need to try and identify patterns of crashing.  Does it always happen while doing a specific action using a specific form/report, time of day, on certain computers, ...

Are you using the proper odbc drivers?

When we the last time the database was decompiled, compacted, compiled, compacted.

Any difference in behavior of you use an accde?

---

Daniel Pineault, 2010-2019 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Hi Daniel.

We do have a master copy of the file.

The only purpose these backup files serve at this point is to demonstrate how often these problems occur.
I am hoping that it will provide the evidence needed to demonstrate that something needs to be done about it.  I was simply hoping to improve the list so to make it clear what the cause is and the number of occurrences over a time period.

I've spent more time on this discussion then I have actually been given to address this problem (none) to date.

This is how things are when I have little to no control over what I am scheduled to work on. :)

It is sad that Microsoft chooses to rarely participate if forums.  I do understand that it could be a daunting task though due to the sheer number of posts/threads.  It would be nice if the forums had an ability such as a moderator flagging a forum post as something for Microsoft to review.  That would cut that down considerably, but that is not what this thread is about. 🙂

Note that we are using the basic SQL Server ODBC Drivers that come with windows as all machines are guaranteed to have that.

I am honestly unsure if we could use an accde.  It is not something we have ever tried and I am unsure what the differences are.

Answer 20

It is likely because two or more people are accessing it at the same time, but beyond that I have no idea.

It has long been recognized that this is the single greatest cause of corruption, so as long as you continue with your current modus operandi you will continue to experience this problem.  It seems to me that your choices are to either accept this and put in place a remedial protocol, or to adopt the standard practice of providing each user with their own copy of the front end.  

If you do opt to persist with your current approach, an alternative to compacting and repairing the file would be to delete the corrupt front end and replace it with a new copy of the 'master' file.

---

Ken Sheridan, Stafford, England

I do agree that this is one of the most likely culprits, however the files can be accessed for hours and days by multiple users without issue.  Sometimes I think it can even occur when the file is only accessed by one user.

The truth is that because I do not control what I am scheduled to work on, then I am stuck with the current approach until someone else deems that it is worthy of my time to address in some fashion.

I do appreciate your feedback though.

Answer 21

I fully agree with Ken.  Your first course of action should be to properly setup the deployment of the front-end where each user get a fresh copy of the front-end deployed locally to them and then launched.  I bet, that alone, would make the issue go away.

 

An accde is merely a locked down version of an accdb so users can't mess around with things as much as they can with an accdb.  It's not perfect, but it does help restrict users somewhat.  It is also compiled, so it guarantees that the code is error free (from the compilers perspective).

 

Even if there was a way to flag Microsoft in a discussion, everyone would use it, so it would end up being useless in the end.  Most of the people here answering are far more knowledgeable about Access than Microsoft's own support.  If you truly want Microsoft's direct help, then you need to contact support and open a ticket.  They will be more than happy to help you, for a price.

 

I fully understand having your hands tied by what you're allowed to do or not at work... Just try and make them understand the current setup is not proper and should be addressed before any more troubleshooting is done.

 

Good luck.

---

Daniel Pineault, 2010-2019 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 22

I fully agree with Ken.  Your first course of action should be to properly setup the deployment of the front-end where each user get a fresh copy of the front-end deployed locally to them and then launched.  I bet, that alone, would make the issue go away.

 

An accde is merely a locked down version of an accdb so users can't mess around with things as much as they can with an accdb.  It's not perfect, but it does help restrict users somewhat.  It is also compiled, so it guarantees that the code is error free (from the compilers perspective).

 

Even if there was a way to flag Microsoft in a discussion, everyone would use it, so it would end up being useless in the end.  Most of the people here answering are far more knowledgeable about Access than Microsoft's own support.  If you truly want Microsoft's direct help, then you need to contact support and open a ticket.  They will be more than happy to help you, for a price.

 

I fully understand having your hands tied by what you're allowed to do or not at work... Just try and make them understand the current setup is not proper and should be addressed before any more troubleshooting is done.

 

Good luck.

---

Daniel Pineault, 2010-2019 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Thank you Daniel.

I was suggesting moderators, because I figured they would be a limited group of trusted people that wouldn't just flag everything.  Also, I have experienced the issue with Microsoft's support being less than helpful.  We have been trying to track down a cause for unexplained crashes in our application and they have not been much help so far.  It appears to be due to issues outside our application, but we have not had any luck determining where yet.

Also, I appreciate the additional information on accde files.  I remember we actually had an issue years before because we were using a control that Microsoft had broken the compatibility for, so we had to add it dynamically, but I don't think we do anything like that anymore.
Those things would obviously prevent us from using accde files.  I don't know if there is anything else we do that is incompatible.  I am also unsure if that would prevent the crashes.  I do notice that the size of the access database file grows considerable when used, although I am certain we do not store data in it.  I believe it is due to how MSAccess functions when pushing data to the back end.  My guess is that it caches the data in the file before sending it.

I know that forms can be bound to ado recordsets, so I wonder if that could bypass using the database file for data storage.  Unfortunately, I have virtually no time to investigate this.