Waiting for unlocked database

[BruceS]

Looking for a little advice...

Have an A2K database that provides source data for an online app. Each
night we need to update certain tables in that database using records that
are uploaded to our ftp site. A separate .mdb is used to massage the data
and perform the updates, which can take some time to complete. (up to 5
minutes).

I need to somehow lock the source database while I am performing these
actions, then unlock it again after I'm finished. Of course, before I do, I
need to know that no one is currently using the database and, if they are,
loop until it's free.

Is there any way I can test for this from inside the second .mdb? Does
anyone have suggestions for a better approach?

Thanks,
Bruce

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "LogUsersOff.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[BruceS]

Roger,

Thanks for replying. Your logoff mdb is clean and straightforward, but
appears to require code in the "object" database that uses the Access
interface to notify the users. That last part is my problem.

My "object" databases are all accessed via the web server and normal
Java-based html web pages (not Access dataaccess pages). The web guys don't
want to modify their source databases.

I just need to be able to test them (from a second mdb) to see if anyone
(via the web app) is connected to them so that I can replace two of the
tables without an error.

Maybe the only way is to try the update and, if it fails, trap the error and
wait 5 minutes.

Any ideas appreciated.

Thanks,
Bruce

 

[Roger Carlson]

What you're looking for is an LDB reader. There are several on the market.
Just google on Access LDB Reader. Here's the first one I found:
http://www.headwaysystems.com/products.htm.

You can also write your own. On my website, I've got one I found many years
ago. Originally created by Mark Nally for Access 2.0, it's called
WhosOn97.mdb. You can find it here:
http://www.rogersaccesslibrary.com/misc/misc.htm Maybe it will give you
some ideas.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Douglas J. Steele]

What about http://msdn.microsoft.com/library/en-us/dnimo01/html/o2k0135.asp
Roger?

 

[BruceS]

Thanks, Roger! Looks like both your code, and the routine the honorable Mr.
Steele pointed out, will do the trick!

Bruce

Bruce

 

[BruceS]

Thanks, Douglas! Believe this may do it!
Bruce

What about http://msdn.microsoft.com/library/en-us/dnimo01/html/o2k0135.asp
Roger?

 

[Marshall Barton]

Those are good ideas if you are in a position to do
something about an open copy of the mdb file. But if the
machine that has the connection open is inaccessible or if
your program is runnung unattended, then there's is nothing
you can do with the name of the offending machine. In this
kind of scenario about you can do is poll (using a Timer
event) the mdb file to see if there's a connection to it
(without identifying the offending machine) simply by trying
to open the database exclusively:

set dbChkIt = OpenDatabase("path\file.mdb", TRUE)
If Err.Number <> 0 Then Exit Sub
' Do your thing