May 1, 2006 at 7:43 am
hi I am currently using an access database which very soon the tables only will be migrated into sql server. my question is sometimes when another user is in the database, i can't go to the design view of my queries or save changes... I get the following error message:
Microsoft Access can't save design changes or save to a new database object because another user has the file open. To save your design changes or to save to a new object, you must have exclusive access to the file.
as of now i have no security in this access database only there will be in sql server tables once i migrated, will this take care of this problem? do i have to anything to my access front end database?
any help will be apreciate it!!!
Jessica
May 2, 2006 at 4:15 am
Ideally, each user should have their own copy of the Access database. Because the data has to be shared, this in turn means that the data has to be held in a separate database and everybody links to that database. The copy that everybody sees is called the front end, the data is stored elsewhere and is called the back end.
Moving to SQL Server enforces this to the extent that the data is definitely in a separate database. However, what you need to do is to make sure that everybody has their own copy of the front end or that you have your own private copy which you work on.
You then have the problem of how to make sure that everybody is working with the same copy of the Access front end. What we do here is to have a login script which copies the latest version of the front end, stored on a server share, to a directory on the user's local machine. When the user logs off, the local directory is cleared. This is only one among a number of strategies that you can adopt.
May 3, 2006 at 5:48 am
jessica, your issue is pretty much exactly what the error message sounds like. It's one of the severe limitations of Access. Because it's a file-based system, it won't let you modify the design of an object that someone else currently has open.
The simplest solution to this is to have a development system and a production system. Make your changes (and test them) on the development system. Then, kick everyone out of the production system and copy the changes over.
This will by and large be cleared up by moving to SQL Server as a back end. Because of the very different way in which SQL Server applies changes, it can finish up with one person, then apply your design changes, then move on to another person. So, the locks are less absolute.
Jeff, for most cases, I prefer to simply have the front end check against a field (in a configuration table) in the back end to see if it is the most current version. Unless you have frequent changes to your front end, this is much kinder on network traffic.
May 3, 2006 at 9:48 am
thank you so much, I understand if you want to change/design , but what about when you want to run a form or a report (these items will still be in access with the backend tables in sql server) would this lock my database if two users are in same one at the same time?.
May 3, 2006 at 10:03 am
The short answer is, no.
The longer answer is, check out SQL Server Books Online (the help file for SQL Server) for details on locking. Depending on exactly how you set up the links to the database, etc., you will get row locks, page locks, or table locks. However, what this does is simply prevent any further transactions from altering that data while it is locked. And, in some cases, from seeing the data. What you will experience will be more along the line of the database appearing to "hang," and you may get deadlock reports. If you read up on it, though, you should be able to see how to eliminate pretty much all of that on the scale you're talking about.
But, again, I strongly recommend that you create a parallel database that you use for development, and only copy the changes over to the production database as "patches." There's nothing quite as bad as having a slightly incorrect stored procedure render all of your production data invalid.
May 3, 2006 at 10:10 am
thanks for your explanation, with parallel do you mean a replica of my access database for only development? and the patches would be vb scripts? which one would my users use the deveplopment?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply