Access 2003 Sharing ... cannot simultaneously update table

[oldhippie]

Quick question (I hope)

It's been ages since I used Access 2003. (Using Access 2007 and 2010 happily for the most part )

Just got a company who asked me to help with a VERY old DB.

It is already split. FE on server. BE on PC's. Pretty sure the PC's are all XP. Not sure what the server is using.

In the past, other customers have had no problem with file sharing when set up like this, no matter the BE. (SQL / Access / etc)

These folks, however, claim that when one person enters an order ... and a second tries to enter an order ... something odd happens in which the info from the second overlays the info from the first. (Pretty sure that's what they said)

Almost like User 1 clicks "New Record" and gets, say, record 3012. And User 2 clicks "New Record" and ALSO gets record 3012. (This is just a guess on my part.)

They claim it "used" to work correctly. Hasn't worked correctly for years. They don't recall what "changed" when it started not working.



Before I go up there this week and look like a moron with my thumb in my ear, could anyone suggest "things to do/check" to fix this?

I currently "walk on water" in their eyes for having fixed a relatively minor thing a few weeks ago and would hate to have them discover I am merely mortal!

Thanks in advance for any help!

[ItsMe]

I would look for the cleanest copy of the Front End file and secure that in a safe place. I would check the table(s) in the back end. Make sure the tables are using data type Autonumber for their Primary Key fields. Place a new, clean, copy of the Front End file on the client machines. If the problem persists, rebuild the back end by creating a new MDB file with empty tables. Import the data to the new MDB file.

[oldhippie]

Thanks! Great idea. Not sure how "clean" any of their Front Ends are, but it would certainly reset some things I'm thinking.

Other than making sure the Autonumber feature is on for new Orders, is there any other "Share"-y thing that can be checked? I seriously have never done anything other than Access-Out-Of-The-Box to allow multiple users to use the tables.

[June7]

Did you mean BE on server and FE on PCs?

How is '3012' generated - is this autonumber field?

[oldhippie]

Yikes! *headslap* Thanks for the catch!!

Yes, I meant BE on the server and FE's on the PC's.

It sounds like the order numbers are not being generated with an autonumber.

But as I was just on the phone with them suggesting this whole scenario, they allowed that if Person #1 was even editing order #3012 and Person #2 was editing order #3123, the information is also getting crossed ... with no rhyme or reason to it.

[June7]

Barring corruption, has to be a reason. Would have to examine db and code.

If the order numbers are not autonumber but instead a custom identifier generated by code, it would be possible for two users to get the same number.

[oldhippie]

Very true (and corruption is a distinct possibility ... they have server that is less than robust).

But the "same number" theory was my best guess. Turns out they have crossed updates even when updating ... when one person is on one existing order, and another person is on a completely different order.

I've never seen anything like this before, but then I've not had too many apps where multiple people are in the same Form at the same time. (Even with the form being on the FE's)