Link to home
Start Free TrialLog in
Avatar of Todd MacPherson
Todd MacPhersonFlag for Canada

asked on

Access Error 3022. (Update of question) Access is creating duplicate Autonumber PK.

I will leave the rest of the question as is BUT I have confirmed that Access 2007 is generating a duplicate PK when it is set to:

No Duplicates Allowed
Field name is SL_PK and it is selected as PK and is indexed.

Screen shot attached. Any ideas how to overcome this?

My code works 99% of the time but once and awhile I get Access Error 3022: The changes you requested to the table were not successful because they would create duplicate values in the primary index, primary key, or relationship...

The code is an edit mode that I created that deletes all records from the table where criteria are found. There can be unlimited records based on what is in tblMulti.

There is a typical PK in each and it is set to autonumber no duplicates allowed.

I can not find where I went wrong. Can you please look at this and point me in a direction to fix it?



'delete original records and this works 100% of the time
strDelete = "DELETE * FROM tblStandImprovement WHERE SL_BLNUM = '" & Trim(Me.txtBlNum) & "' AND SL_SECT = '" & Format(Me!txtSect, "00") & "' AND SL_INV = '" & Me.txtInv & "';"
db.Execute (strDelete)

'cycle through tblMulti and add values from it into tblStandImprovement. A unique record is created in tblStandImprovement for each record in tblMulti and this is where the error is thrown and none of the new records were added
strMulti = "SELECT * FROM tblMulti;"
Set recMulti = db.OpenRecordset(strMulti, dbOpenDynaset)
If recMulti.EOF = False Then
    Do Until recMulti.EOF = True
        strSave = "SELECT * FROM tblStandImprovement;"
        Set recSave = db.OpenRecordset(strSave, dbOpenDynaset)
        With recSave
                !SL_TRTP = Trim(Me.txtTranType)
                !SL_ERCOR = Me.cboCorType
                !SL_CORBL = Me.txtCorBlkNum
                !SL_LIC = Me.cboLic.Column(0)
                !SL_REGNO = Me.cboRegion.Column(0)
                !SL_UPNUM = Me.txtUp
                !SL_BLNUM = Trim(Me.txtBlNum)
                !SL_SECT = Trim(Format(Me.txtSect, "00"))
                !SL_RTCD = Me.cboRteCd.Column(0)
                !SL_RATE = Me.txtRate
                !SL_TRTCD = Me.cboTrtCd.Column(0)
                !SL_MAPNO = Trim(Me.txtMapNo)
                !SL_STDTE = Me.cboStYr & Me.cboStMt & Me.cboStDy
                !SL_ENDTE = Me.cboEdYr & Me.cboEdMt & Me.cboEdDy
                !SL_STYR = Me.cboStYr
                !SL_STMT = Me.cboStMt
                !SL_STDY = Me.cboStDy
                !SL_EDYR = Me.cboEdYr
                !SL_EDMT = Me.cboEdMt
                !SL_EDDY = Me.cboEdDy
                !SL_HA = Trim(Me.txtHa)
                !SL_TOTST = Trim(Me.txtPreSW)
                !SL_PREDN = Trim(Me.txtDen)
                !SL_SPEC = recMulti!MU_SPECIES
                !SL_AVHGT = recMulti!MU_HGT
                !SL_PSTDN = recMulti!MU_DENSITY
                !SL_AVDBH = recMulti!MU_DBH
                !SL_TRTID = Trim(Me.txtCodeID)
                !SL_COM = Trim(Me.txtCom)
                If Not IsNull(Me.txtRqd) = True Then
                  !SL_FILE = Trim(Me.txtFile)
                  !SL_FOLD = Trim(Me.txtFolder)
                  !SL_FILE = "NA"
                End If
                !SL_INV = Me.txtInv
                !SL_PID = Me.txtPID
                !SL_STAT = True
        End With
        Set recSave = Nothing
End If

Open in new window

Avatar of Todd MacPherson
Todd MacPherson
Flag of Canada image


OK this is getting weird. I did step through and it showed nothing.
So just before .Update I put in MsgBox !SL_PK and it showed a supposed new PK generated but it is already in the table.

That blows me away. Any ideas why an autonumber PK is generated twice?
Using Access 2007
Screen shots of table and code showing duplicated PK
this is a 'known' problem in Access 2003 and Access 2007. If it is a known problem why in hell wouldn't M$ fix it? This is a BUG. Fix the damn thing!

Sorry to rant but it is hard enough to do my job without having to compensate for M$ foibles!

Edited slightly for content by JDettman
Avatar of peter57r
Were you intending to post a link to a different question?
nope why?
This code is the suggested fix but for non linked tables.

Is there anyway to use this code in the front end for the back end tables? It is giving me an error that it can not preform this operation on linked tables which makes sense but does not solve my problem.

Sub ResetAuto()

Dim iMaxID As Long
Dim sqlFixID As String

  iMaxID = DMax("SL_PK", "tblStandImprovement") + 1
  sqlFixID = "ALTER TABLE tblStandImprovement ALTER COLUMN SL_PK COUNTER(" & iMaxID & ",1)"
  DoCmd.RunSQL sqlFixID
End Sub
I didn't understand why you would post a link to your own question?
I am an idiot that is why....

sorry about that
It has been a long day and I just started :)
I just thought about going out and getting MS Office 2010 Pro but no sense since some of the users have an OS of Windows XP 64 bit which is not supported.
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
OK here is what I have done. Created a new module and made a call to reseed the PK by opening the back end

I will test it now to make sure it works

Sub ResetAuto()

Dim db As DAO.Database
Set db = CurrentDb

Dim strBE As String
Dim strPathToBe As String
Dim recBE As DAO.Recordset

strBE = "SELECT * FROM tblDataFile;"

Set recBE = db.OpenRecordset(strBE, dbOpenDynaset)

If recBE.EOF = False Then
        strPathToBe = recBE!FilePath
    MsgBox "Database tables are not where they should be. Corruption has taken place. Contact Admin.", vbCritical
End If

Set recBE = Nothing
Set db = Nothing

Dim dbBackend As DAO.Database
Dim iMaxID As Long
Dim iMaxIDF As Long

iMaxID = 0
iMaxIFF = 0

Set dbBackend = OpenDatabase(strPathToBe)
iMaxID = DMax("SL_PK", "tblStandImprovement") + 1

dbBackend.Execute "ALTER TABLE tblStandImprovement ALTER COLUMN SL_PK COUNTER(" & iMaxID & ",1)"

Set dbBackend = Nothing

End Sub

Open in new window

@Jim I will give it a go. It says right at the top it may be fixed by compact and repair. Both the front end and back end compact when they close so that should take care of that right?

Anyway I will get back to you because my code is reporting locked tables.
<<Both the front end and back end compact when they close so that should take care of that right?>>

 No.  When the front end closes, only the FE get's a C&R.   You can C&R a BE from a FE, but you must not have any connections to it.

@Jim not even when I set the back end top compact on close?
<<@Jim not even when I set the back end top compact on close? >>

  Correct.  When you open a FE, the connection the the BE is nothing more then accessing a file. Your not opening the DB directly in the interface.

  When you close the FE, the compact and repair on close applies to it,  not the BE.

 For the compact and close to work on the BE, you would need to open the BE directly with Access, then close it.

As it turns out compacting does not fix the problem. IMO Microsoft really dropped the ball on this.

I have two choices to make:

1) Upgrade to Office 2010 (2013 is not an option as it only supports Win 7 & 8) potentially forcing one or two users to upgrade their XP 64-bit systems

2) Manually programming the PK which is a pain in the ass to monitor because my app is complete.

I wonder if M$ will pay me for my time fixing their bug?
I took the sure bet and removed the autonumber from the PK. Tested my code and it is working. Thanks for the help as allways.
Creating my own PK was the best way to fix this. Thanks Jim!