How are records stored in a MS Access page? Can a record be stored in two pages?

I have two VC++ 6 apps writing/updating data in a common Access 2000 database - both apps are downloading information from different dataloggers.

Every so often we get an exception when trying to do an add/edit/update:

(3260) Could not update; currently lock开发者_StackOverflowed by user 'admin' on machine 'abc123'.

I am confident that the problem is related to record locking in the database.

We're using CDaoRecordset to access the data. From reading docs it appears that the default is to optimistically lock the database page that the record is in. If another process tries to edit/update a record in the same page they get the error we're seeing.

Moving to SQL Server isn't an option at this point - it's not a simple change because of how things are structured.

I'm wondering if I can make the records in the affected tables large enough so that only one record fits into each 2k data page. If I make each record 1100 bytes long then each record should get its own page, solving the locking problem. I know it will use up more disk space however that is not a problem at the moment as the affected tables are pretty low volume ones.

Would that work? Can a record in Access 2000 be split across two pages?

Bloating records just to avoid a locking conflict is not a very practical method. For one thing, Access databases are limited to 2 Gb. That includes system tables. I suggest you trap the error and try the action again. Not knowing C++, I can't give you the code to do that but I assume you already know how to do that.

From viewing differences in behavior batween Access 97/Jet 3.5 and Access 2000/Jet 4.0 I believe that new records in Access 2000 are already placed in their own pages. I noticed a lot more "bloating" in Access 2000 database files compared to Access 97 files.

Thus I suspect your problem is more to do with basic table and index locking rather than record locking. It can take a while, that is milliseconds, to update all the indexes. As Bill states retry the operation. I'd suggest adding a delay of say 10 or 50 milliseconds. And limit the retries to say 10.

A random Autonumber PK may allow you to have increased concurrency, since Jet/ACE tables are stored clustered on the PK (i.e., in PK order). With a random Autonumber, the compacted table will stored in an essentially random order, rather than with the older records at the top and the newer records clustered together at the end.

However, records added after a compact will be in temporary fragmented data pages instead of in clustered PK order, so I don't actually know if this enhances concurrency in a practical sense. It may be only theoretical (i.e., you'd have to compact after every record was added).