This blog is completely dedicated to providing solutions regarding one of the very common MS Access errors. i.e Access Error 3022: “The changes you requested to the table were not successful “. To know how to fix this specific error code you must try the following fixes mentioned in this post.
Practical Scenario :
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 cannot find where I went wrong. Can you please look at this and point me in a direction to fix it?
About Access Run-time Error ‘3022’:
You may fetch this ms access error when you try to insert a new record in an Access table that already contained an Autonumber field. This specific issue occurs in Microsoft Office Access 2007 or in Microsoft Office Access 2003.
At that time the application throws the following error message:
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.
SCREENSHOT OF THE ERROR:
Note: check out that the table may not have any relationship or indexes.
The Access “Run-time error ‘3022’: The changes you requested to the table were not successful usually occurs when they would create duplicate values in the index, primary key, or relationship.
So you need to change the data in the field that contain duplicate data, remove the index or redefine the index to permit duplicate entries and try again. This problem generally arises at the time of saving changes to a report or generating a report.
Causes Of Access Error 3022:
- Well, the most common cause of occurrence of this Access error ‘3022’ is corruption in the specification set.
- The access database was compacted somehow.
- The table contain autonumber field which is not correctly reseeded.
- You must have installed Microsoft Jet 4.0 Database Engine Service Pack 8 (SP8).
- Maybe you have Appended the data from linked table 1 to linked table 2 by using the Current Database option in place of Another Database Delete the record from linked table 1 and re-append the exact record from linked table 2 using the Current Database option.
Resolution To Fix “The Changes You Requested To The Table Were Not Successful” error
You need to compact the Current Spec Set see compacting an FRx Database. If these compacting procedures fail, then contact the FRx Support to repair the file. But if it succeeds, but the report component still gives the error message then try exporting the component into a new specification set or recreate the component.
Workaround 1:: Fix The Changes You Requested To The Table Were Not Successful in Access 2003/2007
To fix this issue in Access 2007 database you need to re-create the append queries in dbFrontEnd.accdb database. To do this, tap to the Append in the Query Type group on the Design tab, and then click to the Another Database option.
To fix this issue in Access 2007 database you need to re-create the append queries in the dbFrontEnd.mdb database. For this, just make a click in the Append Query present on the Query menu in the query design view and then click Another database.
To fix this issue, the very first thing you need to do is compact the database and after then reset the Autonumber field seed. To compact the database, follow these steps:
- Start your Access application.
- After then Open the Access database. Note: If this opens the Security Warning dialog box, then tap to the Open option.
In Access 2007
- You need to tap to the Microsoft Office Button, now go to the Manage option, and then click Compact and Repair Database.
In Access 2003
- Go to the tool menu, and click to the Database Utilities, and click Compact and Repair Database.
- If you see such Security Warning dialog box, then tap to the Open option.
In order to reset the Autonumber field seed, you can try one of the following methods:
Method 1: Use a Data Definition query
Open database that has the table in Access 2007.
- Go to the Create tab; click Query Design in the Other
- In the Show Table dialog box, tap to the close option.
- Now go to the Design tab, click SQL view in the Results
- Type the following in the Query1 window:
ALTER TABLE TableNameALTER COLUMN AutoNumFieldNameCOUNTER(iMaxID,1);
TableName is a placeholder for the name of the table. AutoNumFieldName is a placeholder for the name of the Autonumber field. iMaxID is a placeholder for the current maximum value in the field plus 1.
- From the Design tab, tap to the Run option in Results
- Click queries under objects in the database window.
- After then click to the New, and click to the Design View and then click OK.
- In the opened Show Table dialog box, click to the close option.
- From the query menu click SQL Specific, and then click Data Definition.
- From the Data Definition Query window, type the following command. ALTER TABLE TableNameALTER COLUMN AutoNumFieldNameCOUNTER(iMaxID,1); Note:
Here the TableName clearly signifies the name of the table. And AutoNumFieldName indicates the name of Autonumber field. Whereas, the iMaxID represent the current maximum value in the field plus 1.
- On the Query menu, click Run.
Method 2: Run Visual Basic for Applications code
- Now go to the Create tab, click the down arrow under Macro and then click Module.
- Go to the database window, click Modules under Objects, and then click New.
- After then in the Visual Basic Editor paste the following code.
Dim iMaxID As Long
Dim sqlFixID As String
iMaxID = DMax(“<AutonumberFieldName>”, “<TableName>”) + 1
sqlFixID = “ALTER TABLE <TableName> ALTER COLUMN <AutonumberFieldName> COUNTER(” & <iMaxID> & “,1)”
Note The placeholder <AutonumberFieldName> represents the name of the Autonumber field. The placeholder <TableName> represents the name of the table.
- From the run menu, click Run Sub/UserForm.
Close table before you use either of the method. You don’t need to save the query or the module after you successfully used any the above two method
Workaround 2:: Access Error ‘3022’ : The Changes you Requested to the Table were not Successful in Access
In order to resolve the Access Error ‘3022’: The Changes you Requested to the Table were not Successful in Access 2013/2016 database. User needs to design the tables to eliminate the possibility of entering duplicate values in the primary key field or field that uses a unique index.
If the used primary key needs to support duplicate values, then you should remove the current primary key and add an AutoNumber field to your table which works as a primary key.
To remove the primary key
Removing the primary key won’t delete the field or fields from your table. Rather it removes the primary key designation from those fields.
It also removes the index of the primary key that was created for the primary key.
- Before deleting the primary key, make sure that it doesn’t contain any table relationships. If you still try to remove the primary key which contains the relationships, Access warns you that you must delete the relation first.
- Now in the navigation pane, make a right-click to the table in which you want to remove the primary key and tap to the Design View option.
- Tap to the current row selector for the latest primary key.
- If the primary key contains a single field, click the row selector for that field.
- But if it consists of multiple fields, hit the row selector for any field in the primary key.
- Now go to the Design tab in the Tools group and click to the Primary Key option.
After doing this complete procedure you will see that the key indicator is removed from the field or fields that you previously specified as the primary key.
To Add An Autonumber Primary Key
When any user creates a new table in the datasheet view, Access by default creates a primary key for you and assigns it the AutoNumber data type.
If you have an existing table to which you want to add a primary key field then opens it up in the design view:
- Go to the Navigation Pane, and make a right-click to the table which you want to add the primary key after then hit the Design View.
- Now locate the first empty row in the table design grid.
- In the Field Name column, assign some name.
- In the Data Type column hit the drop-down arrow and tap to the AutoNumber option.
- Within the Field Properties, in New Values, click to the Increment option to use incremental numeric values for the primary key, or click Random to use random numbers.
You may want to see the indexes for the table to evaluate their impact on performance, or to ensure that particular fields are indexed.
Hopefully, all the above mentioned manual fixes help you in resolving this MS Access run-time error 3022. After using these fixes, do share your opinions in your comment section.