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.

 

[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.