Locking records when multiple users are updating a database 2

[Mannstech]

Hi,

I have set up a simple database (in Access 2003) with a simple data entry form that is being used by five users to update client's details. I need to allow all users to access and add/update records at the same time but I'm not sure how to set record locking.

Unfortunately I don't have any coding skills (to allow me to add some VB code) but understand that Access will allow records to be locked.

Can anyone point me in the right direction please?

Many thanks.

 

[kjv1611]

I believe that if you just set up the database, and put separate front ends on each machine, the record locking should be set correctly by default. Or at least it has for me before. If not, then it will be simply turning on or off record locking. I'm sure you can make it as complex as desired, but that should be the simplest way to look at it. And for what you are talking about, probably simpler is better.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Mannstech]

Thanks kjv.,

Can I ask what you mean by "separate front ends"? Does the data sit on one machine i.e. a server, and then users connect individually from their PC's? Or do I have to create a new front end form? If so, how do I do that?!

Many thanks.

 

[kjv1611]

Does the data sit on one machine i.e. a server

Yes.

and then users connect individually from their PC's?

Yes.

Or do I have to create a new front end form?

Well, sorta - but not really.

Actually, here is what you do:

1. Split the Database:
a. Tools
b. Database Utilities
c. Database Splitter

2. This will create a "front end" and a "back end" Basically, the back end will have all the tables, and the front end will have your forms and modules. I cannot recall for sure whether the queries go to the backend or front end.

3. Put a copy of the "front end" on each user's pc. This way, if necessary for a particular user, you or they can customize the front end to their needs, while leaving the back end as standard.

This should help with the record locking, I believe. But I've not done very many where I needed multiple front ends for one back end of a database, as those I've setup were used by only as many as possibly 4 or 5 different people, but never at the same time, that I know of.

If you are still having problems (or you may even want to check this first), be sure to check the locking level of records in the form(s) used in the database.

--

"If to err is human, then I must be some kind of human!" -Me

 

[danvlas]

Somewhere in Tools-Options you will find 'Default Record Locking'. Set it to 'Edited Record'Make sure that the database is opened in 'Shared' rather than 'Exclusive'mode.
Then go to your existing forms (Properties), and set their Locking property to 'Edited record'

And split the database, it's always the right thing to do.
When splitting, use Network Neighborhood, NOT mapped drives!


Queries stay in the front end.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Crowley16]

use Network Neighborhood, NOT mapped drives!

unless you work in my company who's it dept thinks it's more sensible to use mapped drives since they map them within a group policy and change server names every once in a while, grmbl, grmbl...

--------------------
Procrastinate Now!

 

[danvlas]

Any .chance to change the IT Dept? While it's still possible?


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[kjv1611]

[ROFL]

--

"If to err is human, then I must be some kind of human!" -Me

 

[Mannstech]

Thanks guys, that's been really helpful!