Locking problem and unwanted backups created

[berengaria]

Hi all,

I'm a new member, in need of help.

I use Office Access 19 and have a very basic database created in 1999. My colleague has had problems opening the file, over the last couple of days and I've been trying to work out what is happening to it. I've noticed that it has copied itself to a "backup_backup" and leaves the original file as a lower KB. The locking file has stayed in place although I think this might be because she has been opening several copies of the database at the same time as it's been so slow. We share this database on a shared drive, although only one person uses it at a time. We've tried shutting the pc/laptops down and it is has remained in place, the only way I got rid of was to delete it and take a copy and reinstate a renamed copy on our shared drive.

Today it did this again while my colleague was using it, creating a copy at 10:52am.

My Colleague has just logged off at 13.29 and this time the locking file has closed down, however the entries she has been making today are on the "backup" version timed at 13.29 with the lower KB and nothing on the "backup_backup" version timed at 10.22am.

Any idea what is going on, any help would be appreciated.

 

[theDBguy]

Hi. Welcome to AWF!

Sounds like it could be corruption. Have you tried doing a compact and repair? If so, you might have to create a blank database first and then import all your objects into it to create a fresh, clean copy.

 

[arnelgp]

which version did you create the db (2000?).
what version does the "other" pcs have?
you might consider upgrading your db to Newer version.

 

[ebs17]

We share this database on a shared drive

DropBox, OneDrive oder Offline-Ordner zerstören deine Access Datenbank

Warum DropBox, OneDrive und Offline-Ordner deine Access-Datenbank zerstören werden, wenn du sie mit anderen Benutzern teilst.

codekabinett.com

 

[The_Doc_Man]

First, since that was your first post, ... Hello and welcome to the forum.

Next...

That "create a backup" behavior is the way Access tries to preserve your data when confronted with an unstable situation. If you are sharing a one-file database (one that has not been split), you CANNOT share that safely for simultaneous use. The correct safeguard if your situation is based on one-user-at-a-time is to set the database to open exclusively. That will have two major effects. First, no one else can interfere. The database itself will enforce the "one-at-a-time" aspect. Second, OPEN EXCLUSIVE handles the locking differently, so the persistent .LDB file issue should go away.

Having said that, the IDEAL situation is that you split the database to front-end/back-end (FE/BE). Assure that the FE correctly links to the BE, perhaps by linking using UNC rather that Drive Letter file mapping. Then every user gets a copy of the FE, to be used locally. The BE remains shared but with non-exclusive connections. If you do this, all queries and recordset operations and anything else that offers the option should be set to either Optimistic Locking or No Locks, depending on function. If you do this, it will run smoother.

You mentioned speed issues. In the configuration you originally mentioned, opening a monolithic database multiple times from the same place is going to make it run SLOWER because of file locking. Each instance of Access counts as a separate task. (You could confirm this through the Windows Task Manager, because Access would have several child processes open at once, and that would be visible through the Processes tab.) The locking issues of having multiple simultaneous connections from the same machine - and same monolithic file - would probably be the source of those problems.

Eberhard's note relates to what KIND of shared drive you are using. If it is merely shared via LAN connections, perhaps as network-attached storage using Windows protocols, that is likely to offer no problem. But if that is a Cloud-style connection (Drop Box, One Drive, etc) then there is a chance it won't support the protocols that Access needs to operate. If that shared drive CAN be mapped to a drive letter then it should be OK. (Note that the ability to map to a drive letter should not be taken to mean that I recommend using it via a drive letter.) If the drive is more like a cloud than a local network drive, it will be poison to your database.

You said that this problem has occurred for the last couple of days. The question is, what changed just before this problem jumped up and bit you? This question COULD include a Windows patch or Office patch, since this message first appears on a Friday and a couple of days ago would lead to Wednesday morning, which is the morning after the standard Microsoft patch distribution schedule.

 

[berengaria]

which version did you create the db (2000?).
what version does the "other" pcs have?
you might consider upgrading your db to Newer version.

It was created in 1999, I'm not sure what version it would have been back then. We all use Microsoft Office 19 Professional Plus with Windows 10, and Microsoft 365.

I

 

[berengaria]

First, since that was your first post, ... Hello and welcome to the forum.

Next...

That "create a backup" behavior is the way Access tries to preserve your data when confronted with an unstable situation. If you are sharing a one-file database (one that has not been split), you CANNOT share that safely for simultaneous use. The correct safeguard if your situation is based on one-user-at-a-time is to set the database to open exclusively. That will have two major effects. First, no one else can interfere. The database itself will enforce the "one-at-a-time" aspect. Second, OPEN EXCLUSIVE handles the locking differently, so the persistent .LDB file issue should go away.

Having said that, the IDEAL situation is that you split the database to front-end/back-end (FE/BE). Assure that the FE correctly links to the BE, perhaps by linking using UNC rather that Drive Letter file mapping. Then every user gets a copy of the FE, to be used locally. The BE remains shared but with non-exclusive connections. If you do this, all queries and recordset operations and anything else that offers the option should be set to either Optimistic Locking or No Locks, depending on function. If you do this, it will run smoother.

You mentioned speed issues. In the configuration you originally mentioned, opening a monolithic database multiple times from the same place is going to make it run SLOWER because of file locking. Each instance of Access counts as a separate task. (You could confirm this through the Windows Task Manager, because Access would have several child processes open at once, and that would be visible through the Processes tab.) The locking issues of having multiple simultaneous connections from the same machine - and same monolithic file - would probably be the source of those problems.

Eberhard's note relates to what KIND of shared drive you are using. If it is merely shared via LAN connections, perhaps as network-attached storage using Windows protocols, that is likely to offer no problem. But if that is a Cloud-style connection (Drop Box, One Drive, etc) then there is a chance it won't support the protocols that Access needs to operate. If that shared drive CAN be mapped to a drive letter then it should be OK. (Note that the ability to map to a drive letter should not be taken to mean that I recommend using it via a drive letter.) If the drive is more like a cloud than a local network drive, it will be poison to your database.

You said that this problem has occurred for the last couple of days. The question is, what changed just before this problem jumped up and bit you? This question COULD include a Windows patch or Office patch, since this message first appears on a Friday and a couple of days ago would lead to Wednesday morning, which is the morning after the standard Microsoft patch distribution schedule.

My colleague alerted me about the problem on 11 January which would have been a Wednesday. I had just updated by running a year end query to change the year as we use it for a membership database.

 

[berengaria]

First, since that was your first post, ... Hello and welcome to the forum.

Next...

That "create a backup" behavior is the way Access tries to preserve your data when confronted with an unstable situation. If you are sharing a one-file database (one that has not been split), you CANNOT share that safely for simultaneous use. The correct safeguard if your situation is based on one-user-at-a-time is to set the database to open exclusively. That will have two major effects. First, no one else can interfere. The database itself will enforce the "one-at-a-time" aspect. Second, OPEN EXCLUSIVE handles the locking differently, so the persistent .LDB file issue should go away.

Having said that, the IDEAL situation is that you split the database to front-end/back-end (FE/BE). Assure that the FE correctly links to the BE, perhaps by linking using UNC rather that Drive Letter file mapping. Then every user gets a copy of the FE, to be used locally. The BE remains shared but with non-exclusive connections. If you do this, all queries and recordset operations and anything else that offers the option should be set to either Optimistic Locking or No Locks, depending on function. If you do this, it will run smoother.

You mentioned speed issues. In the configuration you originally mentioned, opening a monolithic database multiple times from the same place is going to make it run SLOWER because of file locking. Each instance of Access counts as a separate task. (You could confirm this through the Windows Task Manager, because Access would have several child processes open at once, and that would be visible through the Processes tab.) The locking issues of having multiple simultaneous connections from the same machine - and same monolithic file - would probably be the source of those problems.

Eberhard's note relates to what KIND of shared drive you are using. If it is merely shared via LAN connections, perhaps as network-attached storage using Windows protocols, that is likely to offer no problem. But if that is a Cloud-style connection (Drop Box, One Drive, etc) then there is a chance it won't support the protocols that Access needs to operate. If that shared drive CAN be mapped to a drive letter then it should be OK. (Note that the ability to map to a drive letter should not be taken to mean that I recommend using it via a drive letter.) If the drive is more like a cloud than a local network drive, it will be poison to your database.

You said that this problem has occurred for the last couple of days. The question is, what changed just before this problem jumped up and bit you? This question COULD include a Windows patch or Office patch, since this message first appears on a Friday and a couple of days ago would lead to Wednesday morning, which is the morning after the standard Microsoft patch distribution schedule.

Just noticed your other question above, We use a shared network via LAN not a cloud based network.

 

[Pat Hartman]

There are a couple of things you need to do.
1. You need to resolve the corruption. There are several techniques. Start by creating a new, empty database and import all the objects from the original. If that goes successfully. Compile the code and fix any compile errors. While you're at it make sure that property that says to require variable declarations is set. Then open EVERY code module and ensure that "Option Explicit" is the second line. If it wasn't on one or more of the code modules, you need to compile again and then ADD the declarations that are missing. Keep at it until the project compiles cleanly. Then compact again. Reset the start up properties. Save the latest version as an .accdb if you haven't already.
2. Now you need to split the FE and BE. Make a backup and zip it before going on. If you are working from a local drive, for simplicity with the split, copy the database to the server folder where the BE (the tables only) will ultimately reside. Use the internal option to split the database. You will end up with two files. The FE will have the original name and it will contain all the objects except the tables. The tables are moved to the file with the "_BE" suffix. What is left in the FE are links to the physical tables in the BE.
3. The FE should be located on your c: drive and every other user needs to have a copy of the FE located on their c: drive. you will NEVER, EVER share the FE again. There are lots of posts on distributing copies of the FE because once you make the split, you need an easy way to give all the users an updated copy of the FE. Only ONE of you will be the master of the FE and only ONE of you will make changes going forward. When changes are made to the FE, the FE will be distributed by whichever technique you choose from the options you will see. My personal preference is for a simply 4 line batch file. Each user has a shortcut on their desktop. The shortcut executes the batch file which is located in the same folder as the BE. The batch file copies down a new version of the FE from the master folder on the server. This way, each time you or the other users open the FE, you get a fresh copy.
4. Once you have the distribution in place, YOU, the DEVELOPER, need to use special procedures whenever you make changes to the FE. You are only going to have to make the mistake ONCE of overlaying your changes to wake you up to good programming practices. You as a user, use the shortcut to open the FE. You as the developer, must have a separate copy of the FE in your testing folder and that is the FE you make changes to.

There's lots more you need to know but that is your start to properly sharing an Access database.

 

[berengaria]

Thank you all for your help, with my problem in particular The Doc Man and Pat Hartman. All your comments are very much appreciated. I'm not a confident user of access as I only know the very basics and to sort this out is way beyond my abilities. I'll need to find an expert access freelancer who can sort this out for me. Not sure where I should start to look for an "expert". Is there a website or a part of this forum, where recommended experts are listed.

 

[Pat Hartman]

There are experts here who take the occasional job. You would be just some random person looking for help over the internet so be prepared to pay a significant portion of the estimated cost up front. YOU should not be paying anyway, your company should be paying so you might want to get that approved so they can generate a PO. Then, you might want to see what you can do yourself. Make sure you have a secure backup before you start.

Just to remind you, the unwanted backups are being created because you have Compact on Close set to true. To do the compact, Access starts by creating the new version and calls it yourdbname_Backup.mdb. It is when the C&R fails to complete that you end up with the backup file remaining since Access does not delete the file until the backup is completed. If you are running the application on the server rather than your local drive, this is much more likely to happen so I would remove the compact on close as the first step. Then if you have bloat problems, you need to work on changing the code to remove the bad practices which are usually make tables and delete/append queries to replace all the rows of a table.