Front-End & Back-End database not updating

[allenEd]

Hi,

I have written a very simple database to log purchase orders. Its just one table. I have split the database into a front and and back end database, with the front end having linked tables.

The front end databases are used by 3 users, all linked to a back end database over a network.

My query is, When any changes are made, it isn't shown on the other front end databases, unless you close and re open the table. I have noticed that if you delete a record, it shows up as "deleted" on the other users front end databases.

Can anyone explane how to have the databases all in sync.

thanks in advance,

Allen

 

[KenReay]

Hi

How are users updating, via forms or directly into the tables ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[allenEd]

I have tried both tables and forms

 

[KenReay]

Hi

ahh! you mean if two or more users have the same form open at the same record, and 1 user makes a change it does not reflect on the users screens, unless you close form and reopen it ?

Think about what you are asking for, if user 1 begins to change record A, and user 2 has same record on screen and begins to change it what do you expect to happen?, you are (I assume) using optimistic locking, this means that Access (jet) does not check for update conflicts until it tries to write the data to disk, you could change to pessimistic locking which would prevent user A from amending a record which user B was already editing.

You are entering the complex world of multi user systems, you need to read up on record locking strategies, also consider the type of record set you open, sometimes you want the user to see a 'snapshot' of the data, other times you want the user to see a dynamic dataset

These considerations are not unique to Access, they apply to any multiuser database system


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

Sorry another point are you logging the details of the items on the purchase Orders?, if yes, you almost certainly have your table design wrong, you should ahve a table for PO Headers and PO Items at least, also probably Supplier names and Addresses

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[allenEd]

Hi ken,

There are only a dozen fields, no address fields. Its not so much a problem of two users accessing the same record, but new added records not showing up on the front end database's

thanks for the help

Allen

 

[scottian]

the reason is, as kenreay has pointed out, the admin user with the backend db open is viewing a snapshot. that is to say, admin has opened a form which has 15 records then user front end either adds a record or ammends an existing record but admin doesnt see the new/ammended record unless the dataset is refreshed. scenario 2 is the front end user is viewing a record and admin user deletes the record being viewed. what will happen is that the data will remain on the users screen but when they move off the record and go back to view the record again, all the fields are populated with #DELETED# that is because the dataset is still 15 records in number and the record still has a location in the dataset but the actual data is no longer available. If you need upto the second data clarity you will need to code in some timer driven events that will refresh the data on both front and back end

Be ALERT - Your country needs Lerts

 

[allenEd]

Thanks for all your help