How to lock database from other programs while being updated

[vamoose]

This example is using Access 2000 on all 3 machines.

-Machine #1 is a server computer on which the Access database file resides. It contains only 2 tables with a small amount daily data in each, about 100 records total.

-Machines #2 and #3 are PC's that both access, retrieve and update data from the same file on Machine #1.

My problem is that the data is getting all jumbled or mixed up, I have to assume that both PC's are trying to access, retrieve and update the database at the same time ?

So the question is how to prevent this from happening and let only 1 PC at a time connect to the server database file ?

Thank you for your support.

 

[RuralGuy]

If your application is split into Data and Application:

http://allenbrowne.com/ser-01.html

...there should be no problem with many people accessing the data at the same time.


RuralGuy (RG for short) aka Allan Bunch MS Access MVP acXP winXP Pro
Please respond to this forum so all may benefit

 

[oharab]

What do you mean "all jumbled up"?
If you mean "out of numerical/alphabetical/etc order" then put a sort order on your queries.

If you mean fields being overwritten and updated with the wrong data, then you may need to investigate transactions on your updates.

To actually answer your question, you could create another small table with only 1 record and only 1 field in it. When the 1st database connects, it runs some code to set the field value to "locked". When another database connects to your data file, it should check the table to see if someone's in it, and report the user as appropriate.
All that said, it shouldn't be a problem, so if you could post some code and explain more about the data issues, there may be more ways we can help.

Cheers

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[GingerR]

The main perk of a database is having multiple users at one time. Do you have record locking set? If so, User 1 opens a single record and when User 2 tries to open or save changed data in that record, they get a message that someone else is in the record.

Are your users opening the tables directly? Or do you have a user interface set up with forms that regulate what data they are entering and how?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html