Access unable to append record to a table due to key violations

I have a form in an Access 2010 file that is used to move records to one of two tables (both of which are linked).  If the user selects "Reject" the record is moved to the Reject table.  Today the user was able to move 18 such records to the Reject table but there is one that will not move.  I tried running the append query manually and receive a message saying "Access unable to append record to a table due to key violations".  I'm not able to determine what these violations are.  

The data for the record looks good (no corruption).  All required fields are populated appropriately with text, date, or numeric values.    The primary key in the receiving table is an autonumber field and the append query makes no reference to it.  All indexed fields in the receiving table allow duplicates.  Visually everything looks fine but clearly something isn't right.

Any suggestions on what else I should look at would be greatly appreciated.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
is there a relationship defined between that table and another table that enforces referential integrity?
Liberty4allAuthor Commented:
No.  The Reject table is basically a repository of records that were not approved for various reasons.  Not all fields will be populated in each record.  Data integrity is not a concern for these.
John TsioumprisSoftware & Systems EngineerCommented:
You are missing something....go to the Relationships and click to see all of them...if there isn't any then check the table for fields on your table either the PK or other that participate on Indexes/lookups...
If either of the above helps then its time to insert the data by queries no nothing just type the first only the PK (if it's not Autonumber) and keep typing and trying to insert the record until you hit the problem.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Could the autonumber be corrupted?  Open the table in DS view.  Type a character into the first field to start a new record.  Pay attention to the autonumber when it pops up.  Does that ID already exist in the table?  If it does, you will need to reset the seed although compacting might fix the problem so try that first.

Here's code to reset the seed.  Be careful though since it uses ADO.  You will need to set a reference to the ADO library in order to use this code.  Make sure that all your existing code that creates DAO objects has disambiguated the Dim statements when the objects are created.  that means Dim db as Database MUST be changed to Dim db as DAO.Database.  Same applies to Recordset objects and several others.

Function ResetSeed(strTable As String) As String
    'Purpose:   Reset the Seed of the AutoNumber, using ADOX.
    Dim strAutoNum As String    'Name of the autonumber column.
    Dim lngSeed As Long         'Current value of the Seed.
    Dim lngNext As Long         'Next unused value.
    Dim strSql As String
    Dim strResult As String
    lngSeed = GetSeedADOX(strTable, strAutoNum)
    If strAutoNum = vbNullString Then
        strResult = "AutoNumber not found."
        lngNext = Nz(DMax(strAutoNum, strTable), 0) + 1
        If lngSeed = lngNext Then
            strResult = strAutoNum & " already correctly set to " & lngSeed & "."
            Debug.Print lngNext, lngSeed
            strSql = "ALTER TABLE [" & strTable & "] ALTER COLUMN [" & strAutoNum & "] COUNTER(" & lngNext & ", 1);"
            Debug.Print strSql
            CurrentProject.Connection.Execute strSql
            strResult = strAutoNum & " reset from " & lngSeed & " to " & lngNext
        End If
    End If
    ResetSeed = strResult
End Function

Function GetSeedADOX(strTable As String, Optional ByRef strCol As String) As Long
    'Purpose:   Read the Seed of the AutoNumber of a table.
    'Arguments: strTable the table to examine.
    '           strCol = the name of the field. If omited, the code finds it.
    'Return:    The seed value.
    Dim cat As New ADOX.Catalog 'Root object of ADOX.
    Dim tbl As ADOX.Table       'Each Table in Tables.
    Dim col As ADOX.Column      'Each Column in the Table.
    'Point the catalog to the current project's connection.
    Set cat.ActiveConnection = CurrentProject.Connection
    Set tbl = cat.Tables(strTable)
    'Loop through the columns to find the AutoNumber.
    For Each col In tbl.Columns
        If col.Properties("Autoincrement") Then
            strCol = "[" & col.Name & "]"
            GetSeedADOX = col.Properties("Seed")
            Exit For    'There can be only one AutoNum.
        End If
    'Clean up
    Set col = Nothing
    Set tbl = Nothing
    Set cat = Nothing
End Function

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Liberty4allAuthor Commented:
John: I confirmed no relationships exist for the table.  Only one required field exists.  Indexes exist on five fields but all allow duplicates.  I was able to enter a record manually into the table (by copy and paste) and the record was saved without any error.

Pat: When entering a record manually in the table the autonumber for the primary key entered a value that was not a duplicate but was a value that was missing.  I was expecting the next value to be 2278 but the actual value was 56.  I confirmed this is not a duplicate.  The reason 56 was missing may have been due to the original having been deleted as a result of testing.  Not surprising.  I'm glad the autonumber attempts to fill in values that are missing in sequence but I wasn't aware it would do that.  I compacted the file as part of my trouble shooting efforts so that may have caused it to occur.  However, since I haven't seen that occur on other tables I'm wondering if it has significance in finding a solution.

I know that error messages can be misleading so am wondering if the problem lies elsewhere.  If a user can move 18 records to the Reject table without a problem but one refuses to move I assume the problem is confined to that one record.  Today, a similar situation occurred when a user tried to move a record to a different table (same database).  For what it's worth I heard yesterday that a Microsoft Jet (patch?) may have been installed in my organization.  This database is located on a Windows 2008 R2 server and operates in a Citrix environment.  I just mention this in case it's relevant.    

Any suggestions for further trouble shooting would be appreciated.
Autonumbers do NOT attempt to fill gaps.  The autonumber is corrupted and you will need to reset its seed.  Use the code I posted.  Also, make sure that the autonumber is the PK of the table.

Apparently there was a big gap and that explains why some records would get added but then some would fail.  Once the gap was filled, the next number generated was a duplicate.
Liberty4allAuthor Commented:
Pat: Thanks for identifying the problem.  Before I begin working with your code I would prefer to create a new table with a new autonumber as the primary key, then append data from the existing table to it.  That will cause the autonumbers to change for many records but in this case that should not matter.  However, doing this is of no value if the process that generates autonumbers is faulty.   Please advise how I should proceed.  Thanks.
Liberty4allAuthor Commented:
Pat: I think I answered my question above.  I replaced two tables in which the autonumber was corrupted.  The new autonumber fields increment as expected but I can only move records to one table.  I'm guessing I have no easy option but to use your code to reset it.  I appreciate you providing that but it's a bit daunting as I've not seen anything like it.  There are multiple tables in my database with autonumber values and some of these must not be altered.  Before I begin I have the following questions.
1.  I don't see any reference to a specific table in your code.  Does it look through every table and only work on those that contain autonumber fields?

2.  I see your warning on the use of ADO.  I need to study the differences between ADO and DAO.  This file doesn't contain much code but there are some DIM statements I would appreciate knowing if they must be modified.  They are listed below.

Dim ID As String
Dim cdm As Integer
Dim db As Database (change to Dim db as DAO.Database?)
Dim rs As Recordset (change to Dim rs As DAO.Recordset?)
Dim rscount As Integer
Dim rs1 As Recordset (change to Dim rs1 As DAO.Recordset?)
Dim rs1count As Integer

Dim lngLen As Long, lngX As Long
Dim strUserName As String

Dim stDocName
Dim stLinkCriteria As String

3.  When I look at Tools, References I don't find anything that refers to ADO.  Am I looking in the right place, or is the ADO library described differently?

4.  Is there any risk of creating a problem elsewhere in the file when running the code?  I will make a backup copy before starting but need to know if I should anticipate possible problems.  This database contains very important information and all care must be taken not to mess it up.
To retain the existing autonumbers, use an append query.  Select the old autonumber and map it to the new autonumber.  This is the ONLY way to preserve autonumbers.  The autonumber once generated or placed in a record CANNOT be changed.  And you can't do it with a DAO or ADO recordset loop.  An APPEND query is the only method.  And that works only if the appended value doesn't duplicate an existing value.

Before you do anything like this, you need to make at least two backups.  I zip one to prevent accidents.

Intellisense will help you determine which dim's need to be changed.

Dim something As DAO.   -- intellisense takes over and gives you a list of DAO objects.  You will find most of them to be obvious.  I disambiguate all of them but the only ones that are problematic are the ones that are also ADO objects.

There is no need to put the seed code in your FE if you are nervous.  Put it in the BE.  You will be running it manually anyway so in the BE it won't conflict with any DAO code because there should not be any in the BE.

The ADO library is - Microsoft ADO Ext. 6.0 for DDL and Security.  If you are using an old version of Access, the 2.8 version will work also.
Liberty4allAuthor Commented:

You are very adept at solving my problems for which I'm grateful.  I have never experienced corruption of an autonumber field so would not have expected this to be the source of the problem.  I was able to confirm this is a known Access issue per Microsoft so am requesting assistance from my IT group to fix it.  They won't be as knowledgeable about this as you but the info you've given me combined with what I found on a Microsoft website will hopefully be sufficient for them to do what is needed.  Knowing that Experts Exchange prefers to have questions closed as soon as possible I'm closing it now for that purpose.  If I need additional help from you I will request it.

Thanks very much.

You're welcome.  This is an issue I have personally experienced but only twice in 25 years of using Access.  That's thousands of tables operating for many years without issue.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.