looking for a way to unlock a back end mdb file
in [Forms]
|
Prev: On Click Event
Next: Report based on field criteria
From: Paul on 13 Feb 2010 10:28 Is there any way to unlock a back end database file? I understand that one answer is to get all the users that are linked to it to close down. However, we're tried that and the file will sometimes remain locked for a few days. We're using Access 2003 in a multi-user environment, and every night we import data from other databases, then compact and repair the back end mdb file. To ensure that all the users close down their front end file at the end of every day, I have a timer event in a hidden form that checks the name of a file on the network server every 30 minutes. If the extension of that file name is "yes", it closes down. If it's "no", it does nothing. This works great 99% of the time. All the user front ends close down, the ldb file disappears and the back end file can be compacted and repaired. However, every couple of months, this technique doesn't succeed, and the back end remains locked for several days, during which time we can't compact and repair it, (because it won't run the compact and repair operation while it's locked). Thus far the locking never lasts for more than a few days, after which it will mysteriously unlock itself, and we can resume normal maintenance operations. My biggest concern is that it may reach the point where it never unlocks, and just keeps getting larger over time. I've noticed that even while it's locked I can copy the back end file to another folder (and copy and repair the new copy), but I can't delete or copy over the locked file. It would be great if there were some way to force it to unlock, so we could compact and repair it every night. Alternatively, if there were a way to delete or copy over the file, then we could replace it with the copy that was compacted and repaired in another folder. Is there any way to solve this problem of the back end mdb file that remains locked? Thanks in advance, Paul
From: Douglas J. Steele on 13 Feb 2010 10:51 The simplest way is to reboot the server. That will force the handle on the database to be released. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Paul" <begone(a)spam.com> wrote in message news:ubkPkEMrKHA.4752(a)TK2MSFTNGP04.phx.gbl... > Is there any way to unlock a back end database file? > > I understand that one answer is to get all the users that are linked to it > to close down. However, we're tried that and the file will sometimes > remain locked for a few days. > > We're using Access 2003 in a multi-user environment, and every night we > import data from other databases, then compact and repair the back end mdb > file. To ensure that all the users close down their front end file at the > end of every day, I have a timer event in a hidden form that checks the > name of a file on the network server every 30 minutes. If the extension > of that file name is "yes", it closes down. If it's "no", it does > nothing. > > This works great 99% of the time. All the user front ends close down, the > ldb file disappears and the back end file can be compacted and repaired. > However, every couple of months, this technique doesn't succeed, and the > back end remains locked for several days, during which time we can't > compact and repair it, (because it won't run the compact and repair > operation while it's locked). Thus far the locking never lasts for more > than a few days, after which it will mysteriously unlock itself, and we > can resume normal maintenance operations. > > My biggest concern is that it may reach the point where it never unlocks, > and just keeps getting larger over time. I've noticed that even while > it's locked I can copy the back end file to another folder (and copy and > repair the new copy), but I can't delete or copy over the locked file. > It would be great if there were some way to force it to unlock, so we > could compact and repair it every night. Alternatively, if there were a > way to delete or copy over the file, then we could replace it with the > copy that was compacted and repaired in another folder. > > Is there any way to solve this problem of the back end mdb file that > remains locked? > > Thanks in advance, > > Paul > > >
From: Daniel Pineault on 13 Feb 2010 11:13 The technique you employ is the proper approach. I use it on several multi-user database with no issue. If it is not managing to shut certain users, then you have another underlying problem that you need to identify and resolve. I also believe that certain system dialogs can block your shutdown routine. Personally I use a combination of automated inactive user logoff and administrative logoff. So I set the db to automatically boot users off that do no actually work with the db for 60min and also the method you are using. I suspect your issue may pertain to a specific pc or 2. Have been able to identify which user(s) are retaining a lock on the mdb? Is it always the same pc(s)? Are they up-to-date with both windows and office updates? When you say that the ldb remains, is it actually in use or did was a connection improperly closed? Can you manually delete the ldb file? Or does the system stop you from deleting it because there is still and active connection? -- Hope this helps, Daniel Pineault http://www.cardaconsultants.com/ For Access Tips and Examples: http://www.devhut.net Please rate this post using the vote buttons if it was helpful. "Paul" wrote: > Is there any way to unlock a back end database file? > > I understand that one answer is to get all the users that are linked to it > to close down. However, we're tried that and the file will sometimes remain > locked for a few days. > > We're using Access 2003 in a multi-user environment, and every night we > import data from other databases, then compact and repair the back end mdb > file. To ensure that all the users close down their front end file at the > end of every day, I have a timer event in a hidden form that checks the name > of a file on the network server every 30 minutes. If the extension of that > file name is "yes", it closes down. If it's "no", it does nothing. > > This works great 99% of the time. All the user front ends close down, the > ldb file disappears and the back end file can be compacted and repaired. > However, every couple of months, this technique doesn't succeed, and the > back end remains locked for several days, during which time we can't compact > and repair it, (because it won't run the compact and repair operation while > it's locked). Thus far the locking never lasts for more than a few days, > after which it will mysteriously unlock itself, and we can resume normal > maintenance operations. > > My biggest concern is that it may reach the point where it never unlocks, > and just keeps getting larger over time. I've noticed that even while it's > locked I can copy the back end file to another folder (and copy and repair > the new copy), but I can't delete or copy over the locked file. It would > be great if there were some way to force it to unlock, so we could compact > and repair it every night. Alternatively, if there were a way to delete or > copy over the file, then we could replace it with the copy that was > compacted and repaired in another folder. > > Is there any way to solve this problem of the back end mdb file that remains > locked? > > Thanks in advance, > > Paul > > > > . >
From: Daniel Pineault on 13 Feb 2010 11:56 Douglas, What happens if you reboot while there is a live connection? Will this possibly risk the integrety of the data? -- Hope this helps, Daniel Pineault http://www.cardaconsultants.com/ For Access Tips and Examples: http://www.devhut.net Please rate this post using the vote buttons if it was helpful. "Douglas J. Steele" wrote: > The simplest way is to reboot the server. That will force the handle on the > database to be released. > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no private e-mails, please) > > > "Paul" <begone(a)spam.com> wrote in message > news:ubkPkEMrKHA.4752(a)TK2MSFTNGP04.phx.gbl... > > Is there any way to unlock a back end database file? > > > > I understand that one answer is to get all the users that are linked to it > > to close down. However, we're tried that and the file will sometimes > > remain locked for a few days. > > > > We're using Access 2003 in a multi-user environment, and every night we > > import data from other databases, then compact and repair the back end mdb > > file. To ensure that all the users close down their front end file at the > > end of every day, I have a timer event in a hidden form that checks the > > name of a file on the network server every 30 minutes. If the extension > > of that file name is "yes", it closes down. If it's "no", it does > > nothing. > > > > This works great 99% of the time. All the user front ends close down, the > > ldb file disappears and the back end file can be compacted and repaired. > > However, every couple of months, this technique doesn't succeed, and the > > back end remains locked for several days, during which time we can't > > compact and repair it, (because it won't run the compact and repair > > operation while it's locked). Thus far the locking never lasts for more > > than a few days, after which it will mysteriously unlock itself, and we > > can resume normal maintenance operations. > > > > My biggest concern is that it may reach the point where it never unlocks, > > and just keeps getting larger over time. I've noticed that even while > > it's locked I can copy the back end file to another folder (and copy and > > repair the new copy), but I can't delete or copy over the locked file. > > It would be great if there were some way to force it to unlock, so we > > could compact and repair it every night. Alternatively, if there were a > > way to delete or copy over the file, then we could replace it with the > > copy that was compacted and repaired in another folder. > > > > Is there any way to solve this problem of the back end mdb file that > > remains locked? > > > > Thanks in advance, > > > > Paul > > > > > > > > > . >
From: Paul on 13 Feb 2010 15:30
I suspected that might be the case. However, I also suspect that out network administrators only reboot on weekends, but I will have to check with them. Thanks for pointing that out, Doug. |