How to temporarily lock a multiuser Access database so that no changes take place while doing something that requires the data to remain constant in all tables

I need to make sure that no others users can make changes (i.e. INSERTS, UPDATES & DELETES) in a centralized Access 2007 database while a certain VBA macro is running, and then I would release the lock.

How can I do that in VBA, DAO or ADO?

N.B.: The lock, however, would not prevent others users from executing VBA code that does simple SELECT queries.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leo AlexanderCommented:
My recommendation would be to prevent users from being in the database, altogether, while your script is running. Based on your last sentence, you would want them to still be able to run simple select queries...but this may be much more challenging depending on how your database is currently configured.

Which is why I would think you would have less of a hard time implementing the vba code is prevent them from being in the database at all. It would just require a simple table (a trigger) with a true/false and a query statement. If your table field is set to true (or false) then the program will quit when the user opens the database. You can also specify an exclusion to where it excludes your windows login, or event a set of login names if you would like to have multiple admins. All that is required is the get windows user vba code that is floating around all of the internet (unless you already have it).

Unless you are adamant about having the users still be in there, then that would be an entirely different route (but could be a similar approach using the current user code)
J2015Author Commented:
The users (i.e. via the Access front-end their are running...) must be permitted to remain connected to the database at all times and be allowed to perform other tasks, like clicking buttons that perform simply queries that display results in reports, etc.

If nothing is provided in VBA, DAO, or ADO to automatically perform the database locking mechanism that I'm looking for, then perhaps a way to do it would be to implement a queue manager.

This is how I imagine it... In every place in my VBA code where an action query (i.e. an INSERT, UPDATE or DELETE operation) actually takes place, the code would first need to wait for an authorization from this queue manager before being able to proceed. This queue manager would simply be based on a very simple table containing two fields: an AutoNumber field, and a text field containing the network ComputerID of the user. When the VBA code on the user's machine is ready to execute an action query, it sends a request to the queue manager to create a record in this table containing the user's ComputerID. The VBA code then constantly issues requests to the queue manager to return the ComputerID associated with the lowest AutoNumber in this table until one matches his ComputerID. Once his action query or transaction is completed, the user sends a request to the queue manager to remove his record from the table, so that the person with the next AutoNumber can proceed, and so on.
Dale FyeOwner, Developing Solutions LLCCommented:
There is no built-in way to do this, but would be left with the situation that one of these other processes could already be in progress when you want to initiate Macro.

Yes, you could implement a query manager subroutine for your action queries.  You would have to set a flag in the database before running the macro, and clear the flag upon completion of the macro.

You would pass the subroutine the name of the action query you want to run.  The subroutine would check to see whether that flag is set, and if so, would enter a loop until the flag is cleared.  You might even display some sort of processing message before entering the loop, so the person trying to run the other code would understand why the process they are trying to run has paused, and would allow them to back out of the process if they chose to do so.

But you would have to search your application for all of the action queries and insert this subroutine call for each of those queries, no small task, even with one of the many programs like Rick Fisher's Find and Replace.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Robert ShermanOwnerCommented:
Is the macro in question making changes to the data or are you just needing to query a non-moving target and thus the need for this read-only state?  

If the lock/read-only mode you are searching for is just to allow a consistent snapshot or point-in-time report, then there may be ways of handling that.  

However, if the lock-out is to maintain consistency of the database while some macro is updating data, then what the others have already said applies.
J2015Author Commented:
The macro will make changes to the data, but only if a certain number of conditions preexist in the data. Determining all these conditions takes a few milliseconds but I need to make absolutely sure that the data remains constant during the entire process, otherwise the conclusion to go ahead and make the changes to the data might be based on conditions that are no longer true. That would cause a highly undesirable database integrity problem. The system being designed is to help a group of health workers coordinate clinical meetings among themselves, patients, and a limited set of meeting places, with the objective of totally preventing scheduling conflicts in a very busy environment.
Attempting to retrofit this is dangerous.  What are you going to do with your bound forms?  You would have to convert them to being unbound so you could remove all the hidden database operations and write them yourself with DAO.

Also, why would you ever run this type of batch activity during business hours?  It would be far better to schedule the batch operation for off shift.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You could loop through all open forms, flush the data to disc, then set the AllowAdditions, AllowDeletions and AllowModifications to False (in effect making them read only), and then also make sure any button command on a form that might had data impact be disabled during the process. That would be a monumental task, to be sure, and I'm not sure I'd be confident enough with the process, and the concept of forcing a user's changes is unsettling (especially when dealing with medical data).

That said - it would seem that if I can make data changes NOW that would affect the scheduling of a meeting invalid, then what's to stop me from making data changes AFTERWARD that would also invalidate that scheduling? In other words - BEFORE I schedule a meeting, if DataPointA must be True, then what happens if DataPointA changes AFTER the meeting is scheduled?

As Pat mentioned, often mission-critical processes that are highly dependent on multiple (and moving) data points are best done at a specific time, when you can be certain there is no data manipulation taking place.
Dale FyeOwner, Developing Solutions LLCCommented:
Wish we could have been more helpful.

BTW, a "C" is an inappropriate grade for the solutions you were provided.  You received a number of excellent responses detailing many of the details involved with trying to accomplish what you asked about, from some of the most knowledgeable Access experts on EE.  The fact that it does not turn out to be as simple as you would like should not affect the grade.  The only valid reasons that I can think of for a bad grade would be if an expert was rude, failed to continue the discussion to resolve your question, or you received an answer which is incorrect.

Getting an answer which is not what you wanted to hear, but which is accurate is not a valid reason for a poor grade.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.