I have a process in an MS Acess database that the users will usually run once daily, but could be more or less. It takes several minutes and requires temporary exclusive access because it deletes and recreates the main table. I have code to check to see if there are other users in the db before the process starts, but is there a way to change the access to "exclusive" at the beginning, and then change it back to open access at the end? Thanks for any help.
A:
Access sets the exclusive/shared mode only when it opens the database. So it's not something you can do "on the fly" unless you have another database that does the open/close.
So you can have a "maintenance" database that does the dropping and recreating of the table in the other database - the maintenance database can open the database in exclusive mode.
BTW - it's probably not good practice to do this - the dropping/recreating the table will increase the size of your database each time and you will need regular compacts to keep the size under control. If possible find a different design that does not require this operation.
DJ
2009-05-21 17:04:18
Ok, thanks. I was doing it this way because there are 20,000+ records that get deleted and reloaded each day, and the size increase is much smaller than by just deleting the records and then adding the new ones. It's also many times faster. An occasional compact is no big deal. Thanks again.
2009-05-21 17:26:07
A:
If you run Access with the /excl commandline argument, it should open the database in exclusive mode.
But it's not clear to me if you're doing this from Access.
David-W-Fenton
2009-05-21 20:51:53