Access for multi-users 2

Topic

New Reply

I have an Access 97 split database which I wish to be multi-user. I have the following settings on the front and back-end databases:-
Default record locking – Edited record
Default open mode – Shared
Run permissions – Users
Both the front and back-end reside on a server. The main form has an unbound combo box for filtering in queries relating to other forms which can be opened from the main form. The main form stays open. If one user opens the main form then another user can open it but the combo box cannot display any options. Any ideas?
Rob

Reply | Quote

Replies

Access databases are multi-user by default. Set your locking to No Locks. A lock will still be placed when the edit is written to the record. Also, make sure you’re using CurrentDb rather than DBEngine(0)(0) when you reference the database.

Your most critical step is to make sure that your users are not opening the database exclusive, which is perfectly possible even if you specified Shared as the default.

Reply | Quote

Thanks Charlotte. I don’t know how you get time to answer these questions but I’m very grateful.

I mainly use queries for everything in my databases and so I almost completely avoid code. My point is that I don’t refer to CurrentDb or DBEngine(0)(0) anywhere in code (easier to maintain and upgrade). Are there any ramifications of your comment on CurrentDb anywhere else other than in code?

Also I have been wondering if I should have put the front-end of the database on the clients rather than on the server but then I would have to automate updates of the front-end to the clients. Would I get any better performance/sharing by putting the front-end on the clients?

Reply | Quote

You might check with your system administrator and make sure that all your users have the right to create files in that particular folder on the server. Otherwise, only the first person who opens the database can access it other than read-only.

You would see major performance improvements by putting the front-ends on the local machines. The problem of updating the front-ends is a perennial one, and everyone has to find a way that works in their particular situation.

I don’t quite understand what you mean by using queries for everything. Do you mean that you let your users see queries instead of using forms? If so, you certainly won’t have to worry about keeping busy fixing the data errors and broken queries.

Reply | Quote

Unfortunately the buck stops here (I am the system administrator). I presume you mean the folder with the front-end in it? If so I will check that again.

When I say I use queries I just mean that I don’t access tables directly through code. I only use code for opening and closing forms and reports and for calculations within forms. So the question was are you only referring to an instance of CurrentDB within code or is there some other place from which it is referred ie settings or parameters.

Reply | Quote

The only place you can use CurrentDb is in code.

Reply | Quote

No, it isn’t meaningless. It controls what type of lock it is and, to a certain extent, when it’s placed. For instance, pessimistic locking happens as soon as you open the table, optimistic locking happens when you start to edit a record.

If you use No Locks, you still get a lock on the edited record or page when you actually start to edit it unless you’re using transactions. In transactions, the lock is placed when you hit the Update instruction rather then when you begin the edit.

What you use and how are tuning and user-friendliness issues in Access. If you lock a record or page or table as soon as you enter it, you’re probably going to annoy a lot of other users. On the other hand, if you’re an administrator doing updates to reference tables, you probably do want to lock them while you’re in them to avoid confusing your users.

Reply | Quote