Avatar of Todd MacPherson
Todd MacPherson
Flag 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:

Autonumber
Long
Increment
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?

Thanks

PBLack

'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
    recMulti.MoveFirst
    Do Until recMulti.EOF = True
        strSave = "SELECT * FROM tblStandImprovement;"
        Set recSave = db.OpenRecordset(strSave, dbOpenDynaset)
        With recSave
            .AddNew
                !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)
                Else
                  !SL_FILE = "NA"
                End If
                !SL_INV = Me.txtInv
                !SL_PID = Me.txtPID
                !SL_STAT = True
            .Update
            .Close
        End With
        Set recSave = Nothing
        recMulti.MoveNext
    Loop
End If

Open in new window

screen-shot-dup-pk.JPG
Microsoft Access

Avatar of undefined
Last Comment
Todd MacPherson

8/22/2022 - Mon
Todd MacPherson

ASKER
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?
Todd MacPherson

ASKER
Using Access 2007
Todd MacPherson

ASKER
Screen shots of table and code showing duplicated PK
dup-pk.jpg
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Todd MacPherson

ASKER
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!

https://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28081512.html#a39033995

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
peter57r

Were you intending to post a link to a different question?
Todd MacPherson

ASKER
nope why?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Todd MacPherson

ASKER
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
peter57r

I didn't understand why you would post a link to your own question?
Todd MacPherson

ASKER
I am an idiot that is why....

sorry about that

http://support.microsoft.com/kb/884185
Your help has saved me hundreds of hours of internet surfing.
fblack61
Todd MacPherson

ASKER
It has been a long day and I just started :)
Todd MacPherson

ASKER
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.
ASKER CERTIFIED SOLUTION
Jim Dettman (EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Todd MacPherson

ASKER
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
    recBE.MoveFirst
        strPathToBe = recBE!FilePath
    recBE.Close
Else
    Beep
    MsgBox "Database tables are not where they should be. Corruption has taken place. Contact Admin.", vbCritical
    DomCmd.Quit
End If

Set recBE = Nothing
db.Close
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)"

dbBackend.Close
Set dbBackend = Nothing

End Sub

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Todd MacPherson

ASKER
@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.
Jim Dettman (EE MVE)

<<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.
Todd MacPherson

ASKER
@Jim not even when I set the back end top compact on close?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Jim Dettman (EE MVE)

<<@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.

Jim.
Todd MacPherson

ASKER
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
 or

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?
PBLack
Todd MacPherson

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Todd MacPherson

ASKER
Creating my own PK was the best way to fix this. Thanks Jim!