Active record changes when other users save their changes

[guinness]

Hi guys

My project nears completion and I'm at the stage of testing my split database with multiple users entering data (into different records) using the same table at the same time.

The database records productivity reporting for two managers based on data entered by about 14 users.

One of two main update forms is giving me a problem however. The form is made up with 4 subforms linked by payroll number. If two users are editing seperate records everything is fine until user 1 hits save. User two's record will jump to that of user 1 in mid edit. Any changes that user two has made to their data does save however it's actually possible to end up with user two inputting data into user 1s record without noticing.

Any ideas?

 

[gemma-the-husky]

does each user have a separate copy of the database, or are they all using the same copy at the same time.

they need to have an individual copy of the front end.

 

[guinness]

Thanks for replying Dave

Each user has their own front end. The form has four subforms so I'm going to try and set the locks to edited record on each sub form and see if that maybe makes a difference. If I can crack this and my speed issue I'm up and running.

Fingers crossed

 

[gemma-the-husky]

It shouldn't do what you describe. I could understand it maybe if users were using the same front end at the same time - but not if they are using different front ends

I can only think there is something strange in your dbs design.

 

[guinness]

Cheers Dave. I'm close to throwing the towel in on this one. I've had it running with three seperate users at three seperate sites and the performance was slow and the rcord jumping was a pain but I could have jsut about coped.

On friday I tested it with two users one in England and one in Scotland and it ground to an almost complete halt. It was actually slower than the spreadsheet it's intended to replace. Back to the drawing board.

 

[The_Doc_Man]

You can have a strange result if you are using explicit recordsets opened with the dbConsistent option and both parties are trying to change the same record and you have specified pessimistic locking and you have allowed someone to edit a primary key and you are allowing edits directly through table view or a query in table view. Otherwise, your description makes no sense.

 

[gemma-the-husky]

Cheers Dave. I'm close to throwing the towel in on this one. I've had it running with three seperate users at three seperate sites and the performance was slow and the rcord jumping was a pain but I could have jsut about coped.

On friday I tested it with two users one in England and one in Scotland and it ground to an almost complete halt. It was actually slower than the spreadsheet it's intended to replace. Back to the drawing board.

there is a problem though - one in England, one in Scotland - definitely not recommended to use Access over a WAN - OK using remote desktop, to a term inal server - but over a WAN its difficult, because the backend will general need to transfer large amounts of data over a very slow WAN.

 

[guinness]

Suggestions that I have had are:

Don't have North and south in at the same time
Have seperate back ends and then a third dbase to bring the info back together

Neither have me jumping for joy and the English side still seems paimfully slow even with the Scottish site logged out. Things were speeded up to almost tolerable using a table with a blank 'always open' form. A bit like wedging a door open but still less tha satisfactory.

Ideas floating round my head are either a cloud type storage location as both sites have internet access or possibly, although I've no idea how to go about it, distributing using a citrix server.

I'll post if I get a solution

Thanks guys

 

[gemma-the-husky]

guiness

whatever is happening, you shouldn't be getting this anomalous behaviour under NORMAL circumstances

there is nothing in access that would cause activity by one user to affect another user - UNLESS .... unless you both have the same copy of the front end database open together, or unless your app is doing something strange

So let's think about what COULD cause this ...

let's say in your app, you have a one-record table in the back end, and your app causes this to be populated - now if you are looking at YOUR record in the table, and another user causes this one-record table to be populated with a different record - then this WILL most likely cause YOUR system to refresh to the new record. (and you WERE talking about the active/selected record)

So you may need to look at your app design, in case something like this is happening. If this is the issue, then the selected item table probably needs to be in the front end, not the back end.

-----------
speed is a different issue - you are just very unlikely to get acceptable speed over a WAN. Access needs to move large chunks of data, reliably - so if your backend is situated on the England LAN, then Scotland will get poor performance.

That's why using a Terminal Server works - Scotland will actually be running, on a Terminal Server situated on the England LAN - and its just screen refreshes that have to move over the WAN, not masses of data.