Why so difficult in Access - Sequential Annual Coding in Microsoft Access


	Back to Sequential Annual Coding Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Why so difficult in Access

Thomas Gonder

3 years ago

I've done sequential numbers in dbs other than Access for years. I'm surprised how complicated it gets here, when it's a pretty trivial task in the programming language of other dbs.

Here are some of the problems I see in all three video methods using dcount or dmax.
1) working around deleted records
2) having to read a table or index (of hundreds of thousands) of records to get a "sequential" number.
3) random autonumbers go negative and aren't sequential if used as a source.

Here's what's done everywhere else I've done this in dbs and I presume in Access for its own autonumbers.

1. A table is built to hold all the different types of needed sequential numbers.
2. The code needing a sequential number calls a sub that locks then reads the record in the above table.
3. Add one to the number (last number used is stored in the read record).
4. Test is done to make sure a record for the new sequence isn't already existing (if so add one and repeat)
5. Write the +1 sequential number back to the table (which unlocks the record).
6. Return the guaranteed sequential number back to the calling program.

The big problem as I've seen it in Access so far, there isn't a simple way to read&lock a record and then write it back. The read and write are usually one line of code each, not some big SQL or DAO or other complex operation.

There is dLookup as a cheat but I don't think it locks the record, and it doesn't seem to have a corresponding "dWrite".

Maybe I just haven't yet stumbled across the easy way to do this in Access?

Richard Rost @Reply

3 years ago

You make a good observation. I used to do something very similar to this years ago in a database I built in C (before my Access days). I stored the next number in a text file, and I used another file to indicate if the text file was locked. I was thinking of doing something like this in Access too. I've discussed locking specific records manually before to indicate if a user was editing them - to prevent collisions - and in my experience it works better that Access' built-in record locking which sometimes doesn't tell you a record is locked until AFTER you finish your edits.

Record Locks

	Kevin Yip @Reply 3 years ago
Hi Thomas, it has to do with how the Office object models are designed. The Recordset object model is structured in specific ways, so you need to read and write records (and do other things) in specific ways. The object models are shared across all Microsoft programming platforms. So, expect more of that, whether you program in C#, C++, VB, VB.net, etc., as long as you deal with Office.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Sequential Annual Coding.

Next Unseen