Primary Key Field (Auto Increment) is Creating Duplicate Record Va

[Steven Reames]

Recently, my primary key field (which is auto increment) is generating
duplicate record values. This happens both in the form and in the backend DB
when I try to create a record in the table directly. I have done a compact
and repair on he file. When I reopen it and try the same, it tries to choose
the next value up from the last one it tried to create (but which is still a
duplicate value, a few hundred numbers back from where it should be
incrementing at). Therefore, I can't currently add new records to this table.

What gives and what's the solution.

 

[John W. Vinson]

On Thu, 11 Dec 2008 10:27:04 -0800, Steven Reames <Steven

Recently, my primary key field (which is auto increment) is generating
duplicate record values. This happens both in the form and in the backend DB
when I try to create a record in the table directly. I have done a compact
and repair on he file. When I reopen it and try the same, it tries to choose
the next value up from the last one it tried to create (but which is still a
duplicate value, a few hundred numbers back from where it should be
incrementing at). Therefore, I can't currently add new records to this table.

What gives and what's the solution.

What version of Access? Do you have all the Office service packs and updates
installed? This was a recurrent bug in several older versions, but to my
knowledge it has been fixed with the latest service updates.

 

[Clifford Bass]

Hi Steven,

And if needed, to get it to allow you to add new records, create an
append query that appends one row and that includes the autonumber field as a
field to append, with the first value after your highest value. You will
need to include something for each and all of the required fields. Then go
in either delete the record (you will lose the one number) or edit the record
to contain actual/real data.

Clifford Bass

 

[Steven Reames]

This is taking place in both Access 2003 and Access 2007 (SP1)

 

[Paul Shapiro]

It still occurs if you run an append query that assigns a value to an
auto-increment field. Allen Browne's web page describes the issue and gives
some code to fix it.

Here is the routine I use:
Public Function pjsAutonumberResetToNextNumber() As Boolean
'This routine checks all user tables in the current database. _
If any autonumber seed values are not equal to the next available data
value, _
the seed value is reset. _
NOTE: It does not process linked tables, only native tables in the current
project.

'IF the following objects are declared as ADODB and ADOX objects, _
the project must have references for both the Microsoft ActiveX Data
Objects 2.x _
and the Microsoft ADO Ext 2.x for DDL and Security Libraries (where 2.x
is 2.1 or later.)
Dim cnn As Object 'ADODB.Connection
Dim cat As Object 'ADOX.Catalog
Dim tbl As Object 'ADOX.Table
Dim col As Object 'ADOX.Column
Dim rst As DAO.Recordset

Dim fSuccess As Boolean
Dim fIsAutoNumber As Boolean 'Is the current column an
autonumber?
Dim lngSeedCurrent As Long 'Current seed value
Dim lngSeedNew As Long 'New seed value
Dim lngValueMax As Long 'Maximum value of column in the
table
Dim strSQL As String

'Set connection and catalog to current database.
Set cnn = CurrentProject.Connection
Set cat = CreateObject("ADOX.Catalog") ' = New ADOX.Catalog
cat.ActiveConnection = cnn

'Assume we succeed (will be set false if we fail)
fSuccess = True

'Loop through all the tables, ignoring system tables
For Each tbl In cat.Tables
'Only process user tables, not system or temporary. _
Code modified 2007.07.03 to include linked tables. _
This needs testing to verify correct operation.
If tbl.Type = "TABLE" Or tbl.Type = "LINK" Then
'Loop through all the columns in this table
For Each col In tbl.Columns
'Is this an autonumber column?
fIsAutoNumber = col.Properties("Autoincrement")
If fIsAutoNumber Then
'Make sure the autonumber is set to increment, not
random
If col.Properties("Increment") <> 1 Then
col.Properties("Increment") = 1
End If

'Get the current seed value
lngSeedCurrent = col.Properties("Seed")

'Get the current max column value
strSQL = "Select max([" & col.Name & "]) as MaxValue
From [" & tbl.Name & "];"
Set rst = CurrentDb.OpenRecordset(strSQL)
lngValueMax = Nz(rst.Fields(0), 0)
rst.Close
lngSeedNew = lngValueMax + 1
'Is calculated seed different than the current seed?
If lngSeedNew <> lngSeedCurrent Then
'Reset the seed to the next value
col.Properties("Seed") = lngSeedNew
tbl.Columns.Refresh
'Were we successful?
fSuccess = fSuccess And (col.Properties("seed") =
lngSeedNew)
End If

'Finished with this table since each table can only have
1 autonumber
Exit For
End If
Next col
End If
Next tbl

ExitHandler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set col = Nothing
Set tbl = Nothing
Set cat = Nothing
Set cnn = Nothing
pjsAutonumberResetToNextNumber = fSuccess
Exit Function
End Function

 

[Steven Reames]

This works for the time being. Thanks.

 

[John W. Vinson]

It still occurs if you run an append query that assigns a value to an
auto-increment field. Allen Browne's web page describes the issue and gives
some code to fix it.

Here is the routine I use:

Thanks, Paul. Saved for future reference!

 

[Clifford Bass]

Hi Steven,

You are welcome.

Clifford Bass

 

[Clifford Bass]

Hi Paul,

Thanks for sharing that! Pretty interesting.

Clifford Bass

It still occurs if you run an append query that assigns a value to an
auto-increment field. Allen Browne's web page describes the issue and gives
some code to fix it.

Here is the routine I use:

[snip]

 

[Bill]

I have the same problem. I've downloaded Office2003SP3 (for Access 2003) to
my c drive where the database structure is. The data is on the V drive. The
problem continues. Should I also download SP3 to the V drive as well?

Further, I tried to use an append query. For tblMAIN, the field is
ContactID, Append To is Contact ID, and criteria is 11055 (the next number).
For the other fields, I included test data in the criteria row. When the
query was run, it was too complex. Any suggestions? Thank you.

 

[Paul Shapiro]

Allen Browne's website has code to restore correct auto-numbering starting
from the highest existing data value.
http://www.allenbrowne.com/ser-40.html

Allen has also posted this code to reset the autonumber initial seed value,
and optionally the increment to be used between autonumbers.
1. Create a new query.
2. In first dialog, choose Design view.
3. Cancel the second dialog (Add Table.)
4. Switch to SQL View (left of ribbon.)
5. Paste this in (first value is the seed, second is the increment):
ALTER TABLE [Table1] ALTER COLUMN [ID] COUNTER(686486, 1);
6. Substitute your table name for Table1, and the name of your autonumber
field for ID.
7. Run the query.
8. No need to save the query.