Multiuser Autonumber based on MichaelRed's FAQ 1

[KornGeek]

I am creating a multiuser database in Access 2000, and I wanted to implement an Autonumber for the generation of Transaction numbers, and also for various internal ID's as well. To generate the autonumbers, I modified the code posted by MichaelRed in faq700-184 "Why AutoNumber shouldn't be used in MultiUser databases (And How to ge the Unique Number)". During my testing, I have discovered that I am on occasion getting duplicate numbers. It seems to not be locking the Autonumber table correctly.

First of all, let me state that I am not faulting MichaelRed or his code. On the contrary, I am quite thankful for it, and I'm trying to figure out what I did wrong. Please bear with me, this may get long.

The modifications I have made involve passing the routine a string to let it know which type of Autonumber I need. My modifications start by taking the string and setting some variables:
Dim strBase As String 'the equivalent of his "Basic Data"
Dim strQuery As String 'the equivalent of his "qSelQIMax"
Dim strTable As String 'the equivalent of his "tblNewQINum"

If strType = "Value1" then
strBase = "tblBase1"
strQuery = "qryAuto1"
strTable = "tblAuto1"
else...
End If

I then use these strings in place of the harcoded strings in the code. We had two users generating the same type of autonumber at the same time, stepping through the code. The first user reached the lines:
Set MyDbs = CurrentDb()
...
Set qSelAutoMax = MyDbs.OpenRecordset(strQuery, dbDenyRead)
Set tblNewAutoNum = MyDbs.OpenRecordset(strTable, dbDenyRead)

and everything seemed to be working correctly. He then stopped and waited. When the second user encountered these lines, we expected an error, but got none. Instead they both were looking at the same values.

The way I have this set up, each type of Autonumber that this function generates has it's own tblAuto and qryAuto that it uses for generating autonumbers, so I don't believe the problem is related (directly) to having multiple types of autonumber. My tables are in a shared back-end on the network, and the forms, queries, modules (including this code), etc. are all in a front-end on the users' local machines. I am using DAO 3.6 I believe.

If anybody (MichaelRed or anybody else) can give me some insight into what I'm doing wrong, I would greatly appreciate it.

 

[MichaelRed]

I just finished reviewing another user's changes, and found they had introduced no real errors in the CODE mods, bbut did have some confusion in other areas. Your approach seems quite a bit different and -from the post- I do not see why there would be an issue, unless the two users were actually accessing differnt tables for their respective new values and they just HAPPENED to have the same value.

If you e-mail me the complete reworked procedures and samples of the tables I will take a BRIEF look at them, but NO guarntee of results.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Rick39]

KornGeek - Not sure if this is contributing to your problem, but may be of interest:

http://support.microsoft.com/default.aspx?scid=kb;en-us;225926

 

[KornGeek]

Thank you both for your responses.

MichaelRed,
I have emailed you a minimal version of the database where I was still able to reproduce the error. Thank you for your effort.

Rick39,
I don't think that is related to this problem, because this code is explicitly locking the record against reads, rather than expecting Access to do it. Thank you for your input. Even if it's not the answer to my current problem, additional insight is always useful.

 

[KornGeek]

After doing some more digging, I found some information that I believe is directly related to this issue.

First of all, in

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dao360/html/damthopenrecordset.asp

it states:
If you open a Recordset in a Microsoft Jet workspace and you don't specify a type, OpenRecordset creates a table-type Recordset, if possible. If you specify a linked table or query, OpenRecordset creates a dynaset-type Recordset.

Where it describes dbOpenTable (an optional type argument), it states:
Opens a table-type Recordset object (Microsoft Jet workspaces only).

And where it describes dbDenyRead:
Prevents other users from reading data in a table (Microsoft Jet table-type Recordset only).

What I believe is happening is that it is that it can't open the linked table as a table-type Recordset, and it can't deny reads unless it is a table-type. I believe that I've located what's causing the problem, but I have not yet figured out a resolution.

I was planning to simply trap for the duplicate primary key error, and then go grab another autonumber if this happens. However, when I attempted to implement this, it caused a host of other problems in my database because of the temporarily incorrect ID numbers.

Can anybody offer any additional insight in to how I might work through this?

Thank you.

 

[KornGeek]

I believe I may have found a solution in thread181-351903 (which I somehow missed last night).

I'll give it a try and let you all know.

 

[KornGeek]

I have modified my code to incorporate the method used in that thread, and everything seems to be working correctly. The general concept is that you don't open the recordset of the table in your current database, you open it as a table in the backend database.

Thank you MichaelRed for both your initial FAQ (which saved me many hours of effort) and for your personal help with this matter via email.