I have a situation, I have a multiuser access 2000 database that has a table with an autonumber primary key field in it . When the users are entering information it is trying to duplicate previous autonumber entries
and will not let them enter there data. does anyone know a workaround for this issue? Please Help Thank you in advance
Here's a piece of code you can run on your affected database (either from another database, or from a module in the problem database). You have to have the Microsoft DAO Object Library selected as a reference for it to work though.
Pass the subroutine the full drive/path/name of the database you want to correct, ex:
Public Sub ResetAllAutoNumberFields(varData As Variant)
Dim dbSelectedMDB As DAO.Database
Dim lngFieldIndex As Long
Dim lngTableIndex As Long
Dim strAutoNumberField As String
Dim strTableName As String
Set dbSelectedMDB = OpenDatabase(varData)
' look through every table in the mdb
For lngTableIndex = 0 To dbSelectedMDB.TableDefs.Count - 1
' look through every field in each table
For lngFieldIndex = 0 To dbSelectedMDB.TableDefs(lngTableIndex).Fields.Count - 1
If dbSelectedMDB.TableDefs(lngTableIndex).Fields(lngFieldIndex).Type = 4 Then
Select Case dbSelectedMDB.TableDefs(lngTableIndex).Fields(lngFieldIndex).Attributes
Case 16, 17, 18
' this is the autonumber field
strAutoNumberField = dbSelectedMDB.TableDefs(lngTableIndex).Fields(lngFieldIndex).Name
strTableName = dbSelectedMDB.TableDefs(lngTableIndex).Name
"INSERT INTO [" & strTableName & "] ( [" & strAutoNumberField & "] ) " & _
"SELECT TOP 1 [" & strTableName & "].[" & strAutoNumberField & "] AS ID " & _
"FROM [" & strTableName & "] " & _
"ORDER BY [" & strTableName & "].[" & strAutoNumberField & "] DESC;"
Set dbSelectedMDB = Nothing
I've found a cause of this problem (though I can't say it's the only possible cause):
Someone was editing a record WHILE the database was copied or zipped.
Have also had same problem occasionally on one large database with several tables containing >100k records. Thanks for the reply for code to sort it out. We found it was always compact of back end data tables which caused the autonumber to be reset to a number lower than a previously used number. Problem resolved by upsizing back end to SQL server. Also had corruption of autonumber field in multiuser environment when forms did not use record locking; record locking (lock current record) on forms solved it, except for when compacting, which never went away until we went to SQL server for back end. Now have db running well with >30 concurrent users on access front ends with no stability problems. By the way, all sorts of problems got MUCH worse (before migration to SQL server,) when front ends were running on terminal server!!!!! Be VERY cautious about using access front ends on terminal server, with access back end as well!!!!! Terminal server works good with back end in SQL server.
I have elimintaed this problem by using dao to addnew when someone opens the dataentry form
Using the currentuser to populate the new record it is a simple open/close routine that is quick and efficient
then the user is basically editing their record
No lock conflicts