Procedure for Blocking Multiple Users?

[JonMulder]

Greetings,

On a daily basis, I need to have exclusive use of the database to import several tables from an Oracle server and update queries. I do have a function that returns the number of current users. If that number is "1", then I know I'm OK to begin the import (which takes about 15 minutes). However, I want to prevent other users from accessing the tables during that time. I thought I could do it with a public variable ("pubbooBlockUsers") but now realize that the public variable is created for each user so that won't work.

I have an "IsOpen" function that can check if an Access Table Object or Query Object is currently open, and I can skip that query or table. But I need to update ALL the Oracle tables and Access queries.

I have only about twenty staff hitting the database through out the day, so generally, it's in single-user status. Nonetheless, I'd like to get code in place for those few times when I'm updating and a user tries to get in.

Can anyone direct me to some code snippet that would prevent users from accessing the tables and queries?

Thanks for any help!

Jonathan Mulder
Engineering Geologist
California Department of Water Resources

[orange]

Denis has a lot of tips here
http://www.datawright.com.au/access_...tip_boot_users

Just curious though, why do you import tables? I'm sure you have reasons.

[JonMulder]

Thanks Denis! I went to your link and that looks like it'll do the trick.

The Oracle tables are about 150 miles away located in Sacramento and are quite large. There are ~35,000 water well records and ~1.2 million water level measurement records. I've tried to link down to the tables, but it really slows down.

Your link also mentioned something about needing exclusive access for "compact and repair". Right now, I have the database set to compacting on closing. Will that be a problem with multi-users? I haven't noticed any problem

Jonathan Mulder

[orange]

I'm not Denis, I'm just aware of the routine he has for getting users out of the system.


You could try googling to see what others are doing re Compact and Repair.
Denis is known as SydneyGeek in forums. He's a good guy and will answer issues if you contact him.
This is his homepage http://www.datawright.com.au/index.htm
Good luck.