Many MS Access users have reported encountering the ‘error 3259: invalid field type’ when trying to update fields in a database. It occurred when trying to add a series of fields in a table.
The error can also occur if you use an incorrect data type to modify a table or use wrong SQL queries. However, there are various other reasons that might lead to this error. Let’s discuss the possible causes and the solutions to fix the error.
Causes of the MS Access Error 3259 – Invalid Field Data Type
Here are some possible reasons that can lead to the “Invalid field data type” error in MS Access:
- Using an incorrect data type in the field of a database.
- Corrupted tables in a database in which you trying to update fields.
- Missing references in a database table.
- Collation sequences conflict.
Solutions to Fix MS Access Error 3259 – Invalid Field Data Type
Following are some methods you can try to fix MS Access error 3259 – Invalid field data type.
Method 1: Change Data Types in MS Access Database
The MS Access error 3259 can occur due to invalid data types or improper field properties. The field’s data type in the Access table describes the kind of data that you can store in that field, such as formats, maximum size of the field value, etc. You can check and modify the data types in the database to fix the issue. To change the data type in the datasheet view, follow these steps:
- In the navigation pane, double-click on the table you need to change.
- MS Access opens the table in Datasheet view.
- Select the field that you want to modify.
- On the Fields tab, click the arrow next to Data Type and then choose a data type from the list.
Method 2: Change the Data Type to NUMBER or Reset the Auto Number Field
Many users have encountered the MS Access error 3259 – invalid field data type when running the “Alter Table” command in MS Access. Some users mentioned that the error occurred when they used the “ALTER TABLE Invoices ALTER COLUMN ID COUNTER (1, 1)” code in MS Access database. They’re able to fix the issue after changing the code to ALTER TABLE XXXXX ALTER COLUMN ID NUMBER. However, this method will only work when there is an empty table.
If you have a table with referenced tables, you can try resetting the Auto Number. Here are the steps to reset the AutoNumber field in a table with referenced tables:
- Remove the relationship between the tables.
- Set the AutoNumber field of the main table to a Number data type and remove the primary key.
- In the main table, create a new field of AutoNumber data type and then save it.
- In the referenced table, create a new field of Number data type and then save the applied changes.
Now, follow the below steps to create an update query to update the changes:
- Click on the Create tab and then click on Query Design to create a new query.
- In the Show Table dialog box, click Tables. Select the main table and the referenced table, and then click Add.
- Once you are done with this, click on Close.
- In the main table, click on the field that was previously linked to the referenced table and then drag that field to the previously linked field of the referenced table.
- On the Design option, click Update in the Query Type group.
- From the referenced table, double-click the new field to add it to the field list.
- Type [Main TableName].[New AutoNumber field] in the Update To field to update the new field values in the referenced table.
- On the Design tab, click Run.
- A dialog box will appear with a message “You are about to paste # row(s) into a new table.” Click Yes to add the rows.
- Next, close the query.
- Now, from the main table and the referenced table, delete the original linking field.
- Change the name of the new AutoNumber field to the original name.
- Recreate the primary key and the relationship between the tables.
Method 3: Change the “New database sort order” Settings
Sometimes, changing the sort order of the affected database can fix the issue. Follow these steps:
- Open the database and then go to File > Info > Options.
- In Access Options, click General and then change the “New database sort order” settings to General-Legacy.
Click OK.
Method 4: Restore Database from Backup
The Access error 3259 can occur due to missing objects or references in a table of the database. If you have created a backup copy of the database, then you can restore the database from backup.
Method 5: Repair the Database
Some users have reported receiving Access error 3259 – “Invalid Field Data Type” while trying to add fields in a corrupted table of the database. The corruption in database can create issues such as collation sequences conflict and more.
You can use the Microsoft Access built-in ‘Compact and Repair’ feature for repairing corrupt .MDB/.ACCDB database file. To run this tool, follow these steps:
- First, open your Microsoft Access and create a blank database.
- Click File > Close.
- Click Database Tools > Compact & Repair Database.
- A new ‘Database to Compact From’ dialog box is displayed.
- Search and double-click on the database you need to repair.
If the Compact and Repair utility fails, use an advanced Access database repair tool, such as Stellar Repair for Access to repair corrupted objects in the database. The tool can repair severely corrupted Access database file (MDB/ACCDB) and recover all its components, such as tables, reports, forms, modules, etc. Also, the tool allows you to preview the recoverable data before saving it.
Closure
There are several reasons that can trigger the MS Access error 3259 – “Invalid Field Data Type”. Follow the methods suggested in the blog to fix the error. In case of corruption in the database, you can use the MS Access’ built-in ‘Compact and Repair’ utility to repair the database. However, this utility may fail to fix severely corrupted Access database. In that case, you can repair the database file using Stellar Repair for Access. It is a professional tool that can fix severely corrupted Access database files. The tool is compatible with MS Access 2021, 2019, 2016, and lower versions.
Was this article helpful?
