I have an MS Access 2007 database that I want to update once a week by importing new data. It’s a very large db and takes a long time to update. In order to update and compact/repair it, I make all the other users log of it while it is being updated.
To avoid making people sit on their thumbs for an hour, I’m going to schedule the import and compacting to be done overnight. But I’m concerned that if someone goes home for the day without closing the database, my system won’t have exclusive access when it tries to run the update overnight.
Is there any way in Access 2007 to kick users out of the database and obtain exclusive access, to make udpates?
I don’t know about your question, but I will point out that the issues are just going to get bigger. Unless something has changed in the few years I haven’t used it, Access just isn’t made to store a lot of records that are accessed by many users.
Have you considered switching to something like say MySQL if purchasing an SQL Server isn’t an option?
Yes, we’ve been using this db for years and it’s frankly too large, fragile, and unwieldly. We have plans to replace it in about six months, but it’s what I have to work with for now.
Is all access to the database through the Access application? If so, you can create a process to automatically log users out when you need to update the database. In short, do the following:
Add a table with a Boolean field indicating if exclusive access is desired (e.g., LogOutUsers).
Create a form that checks the value of this field every minute or so. If the value is true, quit the access application. If you want to warn users, open a form dialog warning them they will be logged out in x minutes, then use a timer to quit Access after the elapsed time.
In the OnLoad event of your startup form, open the new LogOut form. You can open it hidden in the background.
In the OnLoad event of the startup form, check if the value of your LogOutUsers field is true, and if so don’t allow the application to start.