Corrupt records since upgrading to Access 2003 1

[earlex]

Hello,

Our simple Access 97 database worked fine for years until we upgraded it to 2003. Now we regularly get corrupt records in the main table - either #error in every field, or random characters often in Japanese?! Grrrrr!

It's shared by 4 users on a Windows 2003 server, and is only 5mb in size. A Compact and Repair usually fixes the problem (but we lose the original record) until it happens again.

Any pointers greatly appreciated!

Thanks - Jason

 

[lupins46]

Is the application split into Front-end and Back-end files?
If not it should be for multi-user operation (this was true for A97, also).
But I don't know why you should get problems in A2003 that you didn't get in A97. I assume everything compiles without errors?

 

[earlex]

Hiya,

The database isn't split into Front and Back end.

We have discovered that it seems to be a multi-user issue - when two users try to access the same record at the same time, it corrupts. The affected table also has an autonumber field in it - don't know if that's relevant.

In Options - Advanced, the default open mode is set to 'shared', default records locking is set to 'no locks' and open databases using record-level locking is ticked.

Any help appreciated!

Thanks - Jason

 

[PHV]

default records locking is set to 'no locks'
Try to set it to 'updated record'

The spelling may be different as I don't play with an english version of ms-office.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lupins46]

You should split the database into front-end and back-end for multi-user operation.
There is a Database Splitter tool on the Tools menu for this. You also need to get familiar with the Linked Tables Manager tool on the same menu.
The front-end should go on each user's local pc with the backend tables in the shared folder.

The locking choices should not have any impact on corruption issues but they have user operational consequences.

Record level locking does not operate at all unless you also choose Edited Record as the default lock type.
Bear in mind that any forms you have already created will be set to No Locks, because that was the default when they were created. You will need to go int the form properties of each form and change to Edited Record if that is what you want. (do this before splitting the database)

 

[earlex]

Thanks guys,

I have changed the locking to 'edited record' and that seems to be preventing the users from accessing the same record at the same time.

I will now have a look at splitting the database.

Cheers! Jason