Run Time Error 3022 (1 Viewer)

SAE · Mar 7, 2018

Apologies if this has been covered before. I have had a quick search and couldn't find a thread with my exact problem.

The thing is I have a table with a primary key, as you should, but say I add 10 records which then have primary key values 1,2,3,..., 10 and then delete the records with keys 4,5,and 6, I am then left with a table with key values 1,2,3,7,8,9,10.

If I then do a compact and repair and start adding new records, via a form, via a query on the table. the first new record will get an ID value of 4 i.e. Access is reusing previously deleted ID values, then the next one 5, the next record, 6 and then when i try and add another record I get the RunTime error 3022 that I am trying to create a record with a duplicate Index blah blah blah as there is already a 7. Primary key is set to Long Integer, Increment, Indexed: (Yes) No Duplicates which is the standard by default.

Why is this happening ??? Why can't Access skip existing values and add new records at the next available number.

Or is there something in my query I need to change?

I am only developing the DB at the moment and am adding and deleting records to test my forms / queries / calcs. But worry if this will happen once the DB is rolled out.

So, to date it is only a accdb file as I am yet to split and save as accde.

Will this stop happening once the DB is split, accde'd ???

Thanks,

arnelgp · Mar 7, 2018

maybe this will give you some insight. https://www.codeproject.com/Questions/314799/How-To-Reset-The-AutoNumber-Field-In-MS-Access
also, you don't use Autonumber as Primary key when you often compact and repair.
if you have other table with FK to your autonumber on the above table, then
those records will be orphaned or can point to a wrong parent record.

SAE · Mar 7, 2018

Thanks,

I'll have a read and try and get my head around it. I have always used the Autonumber as the value for my primary keys and never had this happen before. I will look into other solutions in the future.

BTW, this isn't a parent table, it is a child table so nothing will ever point to the PK in this table, I do use the PK for other purposes though which I wont get into here as they don't relate to this issue.

SAE · Mar 7, 2018

@ArnelGP,

Reading your post again you make note that a FK may point to a wrong PK after a C&R.

I have never had an Auto number field get renumbered as part of a C&R. I found it just makes the numbers available to be reused.

So, yes in some cases if you delete a PK record in a parent table leaving the records in the child sub-table and C&R and then add new parent records the child records may then point to the new PK record ... never thought of that scenario before ...

So, carrying on. How do you suggest I number my PK field in my parent tables if not by Autonumber ??? Just run a query and use a "Max(WhateverFieldValue) +1" as the new value. I use this method in other areas of my DBs but never parent keys.

BTW, I use a lot of Temp tables as I have to import data from Excel, process, and append to main tables so I tend to need to C&R a lot. Pls dont say this is bad DB practice unless you know what I am doing it for ...

Mark_ · Mar 7, 2018

SAE said:
BTW, I use a lot of Temp tables as I have to import data from Excel, process, and append to main tables so I tend to need to C&R a lot. Pls dont say this is bad DB practice unless you know what I am doing it for ...

There is another thread recently opened about using temp tables and repeated C&R.

In general, if you have a separate database that handles these types of actions for you many issues such as this are avoided. The "For temp purposes ONLY" goes ahead, does your import, does any data validation/corrections/feeding of squirrels/what have you, then goes ahead and updates your "Live" database.

As on live data resides in the "For temp purposes ONLY" DB, you can freely delete all data if you start having strange issues.

Having your live database do the same isn't "Bad". There are other tricks though that can often make your life easier.

SAE · Mar 8, 2018

@Mark,

Further explanation for reference. I have a split DB with the BE on the office server and the FE on each users C drive.

As part of the working process sometimes people need to upload data from Excel. To do this I import the xlsx worksheet into a tbl_TempData table in the FE and then the user can change, edit, confirm it is correct, and then press the "Upload Data" button and then the tbl_TempData gets appended to the main data table in the BE.

Naturally this process will screw up if there are 2 users doing the same operation and the tbl_TempData was in the BE or another shared DB file.

I have found that this is the most efficient way (for me) to do this.

Thoughts ??? Are there other / better ways of doing this.

However, getting back to the initial problem. I have found this gets messed up if there were records deleted in the main BE table, a C&R was done on the BE, and then when I try and append records I get Error 3022, or some records don't get appended. i.e. if there is a 3 record gap in the PK field in the BE table and I am appending 5 records, then records 4 and 5 don't get appended.

The solutions seems to be NEVER C&R a BE data file.

The_Doc_Man · Mar 11, 2018

SAE,

The solution is to never C&R a BUSY data file on the BE server. But databases NEED to be C&R'd now and then.

As to your other problem - if two users cannot import at the same time because they use the same BE temp table, the temp table is in the wrong place. There are two common approaches for this.

1. Search this forum for discussions of a script to automatically update the FE file by running a COPY operation to the user's PC from the shared folder with the BE file (or related to that folder). The script removes the old FE, copies the new FE, and then launches it EACH TIME. Then you can put temp tables in the FE. Yes, this could cause bloating of the FE - but the next time you run, you start with a clean copy.

2. Create a tertiary file that your FE can COPY (using the file system object) by deleting the old copy and downloading a new copy. In this file, have linked tables set up as your temporaries. You don't have to reload or C&R the FE so often if your temporaries are in a file that gets replaced.

To be honest, I've never seen autonumbers reset in such a way as to use a number lower than or equal to the highest extant assigned number. When the table has been emptied, they reset. But something is wrong if Access is re-using lower numbers while higher numbers still exist.

asamass suggested re-installing Access, which actually makes sense if this problem is only happening for some users. If someone has a corrupted installation, there is no way to be sure what it will do. It only takes one to screw it up. AND ... since this is a BE file, and if you are the only person who ever does the C&R, I'd start with YOUR copy of Access as a potential culprit.

SAE · Mar 12, 2018

@Doc_Man,

Thanks for the feedback and suggestions.

The temp tables are in the FE and not the BE, i thought i had mentioned this but maybe not (too many posts to go through). Just in the very rare case that 2 users could be importing values at the same time (never say never around here ...)

Having the Temp tables in a third DB is a good idea too, I will have a play with this option (next time).

I already run a script that copies an updated FE from the server to each users C drive when I make changes. I have an "Interface" DB that has a button for each user DB, when a user click that button it checks which REV is in the DB they have on C: and which REV is in the DB on the Server and then copies the new FE if the server has a higher REV number. Works well as I don't have to go around and ask people to update their FE which I did when we only had 5 users.

I have noticed Access reusing old IDs for a while. Previously when I created a New Project / Option / Rev I could find the Rev by using a query with a MaxOfProjectID on the assumption the New Project ID would be the highest ID number. However this isn't always the case. I have noticed more and more that a new project could take the ID of a project previously deleted if I had done a C&R. I now create a "ImTheNewProject" Yes/No field and filter for Yes to find the ID of the new project. Then I Update all "ImTheNewProject" field values to No once I have save the New Project ID to a global Variable.

The_Doc_Man · Mar 12, 2018

Out of curiosity, what is the highest value attained by your current autonumbered ID?

If you are going to the trouble of having a new flag in the DB to manage recognition of your new project (because you can't trust autonumber), then I might do something like create a special function and remove the autonumber. In its place put a LONG that gets allocated (perhaps have a subroutine, perhaps just use an in-line DMax + 1 method). Then you will NEVER re-use a number. But I'm still trying to recall if I have ever seen a re-use of a number lower than the highest number of the PK autonumber field.

I don't recall whether you said you removed and reinstalled Access, but surely doing so would be easier than adding another flag to your DB to denote "this is the new project." And if it fixes the problem, you are home free. If not, at least you eliminated the chance of having a corrupted MSACCESS.EXE file.

SAE · Mar 12, 2018

Currently I have only a couple of hundred as I am just developing the database and keep adding and deleting temporary records as I check things over.

For this particular table I would expect "thousands" to "tens of thousands" of records, one of my other DBs has a couple of million records in some of the tables.

No, I haven't deleted and reinstalled Access.

SAE · Mar 12, 2018

@ Doc_Man,

Here you go:

Capture 1 is a snip of the original data table. See there are gaps as I have been playing around adding and deleting records.

In Capture 2 I have added 2 records and they come in as ID 35 and 36 ... naturally ID_Rates_Labour_Base is he primary key with an Auto number.

When I try and add another record it gives the RunTime 3022 error as it is trying to add it in as ID 37 but ID 37 already exists.

The query behind the form is a direct qry of the data table filtered by project / rate table No.

SAE · Mar 12, 2018

And a copy of the table structure ...

SAE · Mar 12, 2018

And ....

When I "Append" 10 records, with a Code of 1,2,3,..., 10 via an append query.

This time it seems to want to start the Autonumber at ID 7. So:

- Record 1 doesn't get appended as there was already a record at ID 7.

- 2 & 3 come in at ID 8 and 9,

- 4,5,6 don't get appended as there are already IDs 10, 11, and 12.

- And then 7,8,9,10 get appended.

This is after I added 2 "New" records that came in as ID 36 and 44.

So, part 2 is how do I trust an Append query if only half of the items will get appended ???

The_Doc_Man · Mar 12, 2018

one of my other DBs has a couple of million records in some of the tables.

And has that one with a million record numbers ever exhibited this kind of behavior? Autonumbers get "spaced out" all of the time for a couple of common reasons - deleting earlier records due to obsolescence and abandoning a record because of errors (data or procedural) that cause the append to abort AFTER a record was allocated.

If this is happening to ONE and only one DB, the problem isn't Access, it is some form of corruption that C&R doesn't fix. Since you are developing this, it should be easy (logistically, at least) to make a new DB and then import everything to it (including data) from the miscreant DB. Have you tried that yet?

SAE · Mar 13, 2018

To be honest I generally don't go through tables with that many records.

Though one of the main tables i.e. my Project ID which currently has 27 records and I have seen the IDs be reused in this table in a different DB. From there I have a number of cascading tables, ProjectOption, Project Revision, PO No, PO Variation, PO Item etc ... and he final table can have lots of records, and I generally don't go through these tables looking for errors ...

I haven't come across the 3022 error in different DBs just the reuse of IDs. One can assume based on the way it is working that It will happen if I forced it in a similar manner.

To give an indication of how often I C&R, one of the back ended tables has 35,000 records and is up to ID 1,400,000. So the table has been deleted and appended a few times.

The_Doc_Man · Mar 13, 2018

Autonumbers come in two flavors - random and incrementing. I would fully expect to see that a C&R would reset the choices for a random autonumber. But the way I see it (and the way it is documented in other articles), this re-use behavior is not normal for an autonumber set to increment AND the table in question is not empty.

Allen Browne reports some issues for older databases in this article:

http://allenbrowne.com/ser-40.html

Therefore, this problem is not unknown. I don't see very many solutions out there, though. You can browse for yourself using "MS Access autonumber reuse" as a search string through your favorite search engine.

gemma-the-husky · Mar 13, 2018

[similar observations to The DOC Man above]

Actually, I don't understand the first post.

Say you have records with an autonumber PK, 1 to 10 in an intact sequence.
Then delete records 4 to 6

If you now do a compact and repair, then next autonumber should still be 11, not 4. It may not even be 11, (an autonumber isn't guaranteed to be sequential) but it shouldn't be less than the highest number in the table.

Occasionally the autonumber seed gets corrupted, and there is a fix for that, but it's abnormal. (The fix is to manually insert a record with the next key - in your case 11, which should reset the seed)

Is your autonumber the PK. Or is your numeric value not an autonumber?

If this is happening all the while, I would be inclined to import all your data into a new database, and see if that fixes it.

The_Doc_Man · Mar 13, 2018

As Allen Browne notes, it is possible in older databases to use an Append query that puts something in the ID field even if it is autonumbered. The supposition is that you can do this as long as you don't try to store a duplicate ID value. But to be precise, if you are autonumbering, you should never even REFERENCE the autonumbered field in any action query that is acting on that table. You still can, of course, reference the ID field from a parent table if you are populating a child table from it to populate an FK field.

So when this problem happens, is there ANY chance that you are trying to reference the PK in an update/append situation?

Run Time Error 3022 (1 Viewer)

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

Longboard on the internet

Registered User.

Immoderate Moderator

Registered User.

Immoderate Moderator

Registered User.

Registered User.

Attachments

Registered User.

Attachments

Registered User.

Attachments

Immoderate Moderator

Registered User.

Immoderate Moderator

Super Moderator

Immoderate Moderator

Similar threads

Users who are viewing this thread