Whux
asked on
Autonumber Multiple Users Unique ?
I have a small Access db thta I created using
a Paradox db as the source. I initially created the id field as a autonumber. Then the user wanted the Access db to have the same numbers as the Paradox db since they had filed everything related to the db by id. So I redid it using the original id, just as an index. Now there is no autonumber.
Well, now, since we have several users accessing it at the same time, it appears that several users can get the same id number for a insert(since it's using max(id) fron the table). This is causing problems.
I then added a autonumber next to the id number figuring that I would get a unique id from that, add 300 to it to keep it in the same sequence range of the original number. We tested that. It worksd BUT when mutiple users went to add a new record they all get the SAME ID number not unique.
It appears that the autonumber doesn't increase until you actually write the record to the table. So in a multi user environment my users are getting the same number if they try to add records.
What gives here. Do I have to lock the records between users so that one has to finish before the other ? Am I way off base here ?
In Oracle I would use a Sequencer...that gives you a unique number every time you fetch from it, so no user get the same number twice.
a Paradox db as the source. I initially created the id field as a autonumber. Then the user wanted the Access db to have the same numbers as the Paradox db since they had filed everything related to the db by id. So I redid it using the original id, just as an index. Now there is no autonumber.
Well, now, since we have several users accessing it at the same time, it appears that several users can get the same id number for a insert(since it's using max(id) fron the table). This is causing problems.
I then added a autonumber next to the id number figuring that I would get a unique id from that, add 300 to it to keep it in the same sequence range of the original number. We tested that. It worksd BUT when mutiple users went to add a new record they all get the SAME ID number not unique.
It appears that the autonumber doesn't increase until you actually write the record to the table. So in a multi user environment my users are getting the same number if they try to add records.
What gives here. Do I have to lock the records between users so that one has to finish before the other ? Am I way off base here ?
In Oracle I would use a Sequencer...that gives you a unique number every time you fetch from it, so no user get the same number twice.
ASKER
Autonumber works correctly when its just ONE user.
However, when several users are accessing the form and they all start to add a new record they all end up with the same id, which is based on the autonumber.
I mean, I can start to add a record and get an id of 100. Let that sit go over to another person's PC bring up the form start to enter a new record there and get an id of 100 as well then to another users PC and the same...so 3 or 4 of us can start to add a record and get the same id number on the screen.Then I go back to my PC and actually save the record and it writes it as 100, go back to the other PC and it updates my record 100 with the other person's data from their record 100.
I mean the autonumber doesn't increase unless your finsihed writing your record so that if your keying the record on a autonmuber field its poossible for severla people to get the same number.
Is this a lock issue or something ?
Or am I just doing this the wrong way ?
??????
However, when several users are accessing the form and they all start to add a new record they all end up with the same id, which is based on the autonumber.
I mean, I can start to add a record and get an id of 100. Let that sit go over to another person's PC bring up the form start to enter a new record there and get an id of 100 as well then to another users PC and the same...so 3 or 4 of us can start to add a record and get the same id number on the screen.Then I go back to my PC and actually save the record and it writes it as 100, go back to the other PC and it updates my record 100 with the other person's data from their record 100.
I mean the autonumber doesn't increase unless your finsihed writing your record so that if your keying the record on a autonmuber field its poossible for severla people to get the same number.
Is this a lock issue or something ?
Or am I just doing this the wrong way ?
??????
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question appears to have been abandoned. A question regarding will be left in the Community Support area regarding its disposition; if you have any comment about the question, please leave it there.
If there is no response within seven days, a moderator will be asked to resolve the question. The recommendation will be to accept the comment of JDettman.
The link to the Community Support area is:
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
DO NOT ACCEPT THIS COMMENT AS AN ANSWER.
Regards,
ep
If there is no response within seven days, a moderator will be asked to resolve the question. The recommendation will be to accept the comment of JDettman.
The link to the Community Support area is:
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
DO NOT ACCEPT THIS COMMENT AS AN ANSWER.
Regards,
ep
Below is a custom key routine that you can use. It relies on a table with one record for each table name that you want to generate key's for.
Bounce back with any questions,
Jim.
'
' Get a unique record key.
'
Function GetRecordKey(strTableName As String, strKeyParam As String) As Variant
Const Routine = "GetRecordKey"
Const Version = "1.0"
Dim wrk As Workspace
Dim dbCurrent As Database
Dim dbRemote As Database
Dim tdfAttached As TableDef
Dim strPath As String
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim intlKeyValue As Long
Dim lngWait As Long
Dim lngX As Long
Dim intLockCount As Integer
GetRecordKey = Null
On Error GoTo GetRecordKeyError
Set dbCurrent = CurrentDb()
'
' First, get the key type.
'
Set rst1 = dbCurrent.OpenRecordset("q
rst1.FindFirst "[TableName]= '" & strTableName & "'"
If rst1.NoMatch Then
' Not good. Need an entry for the table to generate a key.
gstrMBTitle = "Generate key error"
gstrMBMsg = "Can't generate key. No entry in key table"
gintMBDef = MB_OK + MB_ICONSTOP
gintMBBeep = True
gintMBLog = True
Call DisplayMsgBox
GoTo GetRecordKeyExit
End If
'
' Now Generate a key based on the key type
'
GetAKey:
Select Case rst1![KeyType]
Case 1
' Key is simple numeric counter (ie xxxxxxx).
rst1.Edit
intlKeyValue = CLng(rst1![LastKeyValue]) + 1
If intlKeyValue > rst1![MaximumValue] Then intlKeyValue = 1
rst1![LastKeyValue] = Format$(intlKeyValue)
rst1.Update
GetRecordKey = intlKeyValue
'Case 2
' Key is a base + a numeric counter (ie. ABAxxx)
Case Else
' Undefined key type
gstrMBTitle = "Undefined call"
gstrMBMsg = "Can't generate key. Invalid key type."
gintMBDef = MB_OK + MB_ICONSTOP
gintMBBeep = True
gintMBLog = True
Call DisplayMsgBox
GoTo GetRecordKeyExit
End Select
'
' Got a key. Does it need to be tested for unique?
'
If (rst1![UniqueKey]) Then
If dbRemote Is Nothing Then
Set wrk = DBEngine.Workspaces(0)
Set dbCurrent = wrk.Databases(0)
Set tdfAttached = dbCurrent.TableDefs(strTab
strPath = tdfAttached.Connect
strPath = right$(strPath, Len(strPath) - InStr(strPath, "="))
Set dbRemote = wrk.OpenDatabase(strPath, False, True)
End If
Set rst2 = dbRemote.OpenRecordset(str
rst2.index = "PrimaryKey"
rst2.Seek "=", intlKeyValue
If Not (rst2.NoMatch) Then
rst2.Close
GoTo GetAKey
Else
rst2.Close
End If
End If
GetRecordKeyExit:
Set dbCurrent = Nothing
If Not rst1 Is Nothing Then
rst1.Close
Set rst1 = Nothing
End If
If Not rst1 Is Nothing Then
rst2.Close
Set rst2 = Nothing
End If
If Not dbRemote Is Nothing Then
dbRemote.Close
Set dbRemote = Nothing
End If
Exit Function
GetRecordKeyError:
'Table locked by another user
If Err = CNT_ERR_RESERVED Or Err = CNT_ERR_COULDNT_UPDATE Or Err = CNT_ERR_OTHER Then
intLockCount = intLockCount + 1
If intLockCount > 5 Then
GetRecordKey = Null
Resume GetRecordKeyExit
Else
DoEvents
DBEngine.Idle DB_FREELOCKS
lngWait = intLockCount ^ 2 * Int(Rnd * 20 + 5)
For lngX = 1 To lngWait
DoEvents
Next lngX
Resume
End If
Else
UnexpectedError ModuleName, Routine, Version, Err, Error$
GetRecordKey = Null
Resume GetRecordKeyExit
End If
End Function