How To Fix MS Access Error 3259- Invalid Field Data Type

Summary:

Are you encountering invalid field data type error in your Access database? If yes, then you are at the right location as this blog emphasizes on how to fix “Invalid Field Data Type” error.  So, explore the ways to fix the Access runtime time error 3259-invalid field data type and it’s related issues.

Practical Scenario:

After re-installing my computer completely, suddenly I get this error:
Run-time error 3259 invalid field data type on alter table
when running this query:
ALTER TABLE Invoices ALTER COLUMN ID COUNTER (1, 1) on a MS Access database.

The data type didn’t change, actually nothing changed except for the re-install..

The field ID is a Long Integer field that is set to auto increment for every Invoice that the table holds. Since data resides in this table only temporarily I reset the auto increment after every batch. I think it may have something to do with references missing or changed in different versions.. But I can’t seem to figure out which one or why..

Do I need to be more explicit in my query? Anyone ever experience this before?

About The Access Runtime Error 3259:

Error name: Invalid Field Data Type

Error code: error 3259

MS Access Error 3259- “Invalid Field Data Type”

Access facilitates with so many different data types and each of the type has a specific purpose. This error comes when data type specified in the Field object’s Type property is not valid. 

Well you can fix this issue by modifying or changing the data type setting for a field.  So here is the complete solution on how to perform this.

How To Fix MS Access Invalid Field Data Type Error?

Data types in Access provide a basic form of data validation because it helps in ensuring that users enter the proper types of data in your table fields. Learn how to change the data type in Access Database.

Steps to Modify Or Change The Data Type Setting For A Field

Change Data Types In Datasheet View

  1. In navigation pane, locate and double tap the table which you want to change.
  2. Access opens the table in datasheet view.
  3. Make selection for the field that you want to modify.
  4. In the Propertiesgroup on the Fields tab, click the arrow in the drop-down list next to Data Type and select data type.

Note:

If you are using access 2007 on the datasheet tab, in the Data Type & Formatting group, tap to the arrow in the drop-down list next to Data Type, and then select a data type.

  1. At last Save your changes.

Change Data Types In Design View

  • If your table is open in datasheet view, make a right click on the document tab for the table and click Design View.

-or-

But if in case your table is not open, in navigation pane, make a right click the table that you want to change and then tap to the Design View on the shortcut menu.

  • Specify the field that you want to change and select a new data type from the list in the Data Type
  • At last save your changes.

Issue 1: Microsoft Office Access “Can’t Change The Data Type” Error

When you perform operation on access 2007/2010/2013/2016 table, you might experience the following error.

Microsoft Office Access can’t change the data type. There isn’t enough disk space or memory.

This error also encounters at the time of performing save operation. This enables you to change Data Type and its properties.

Have a look over the screenshot of the problem:

Microsoft Office Access can’t change the data type. There isn’t enough disk space or memory.

Reason:

This error usually comes when you exceed the maximum number of columns allowed in the table. Or when then you use maximum numbers of locks for a single file.

Therefore, you need to increase the maximum number of lock per file by editing a registry entry for the local computer.

Solution To Fix “Can’t Change The Data Type” Error

To fix this access “Can’t Change The Data Type” issue the recommended solution is to make modification the windows registry.

It’s not a recommended solution from Microsoft and so do this editing of registry at your own risk. To perform this just follow down the below mentioned steps:

  • Open the registry by typing ‘Regedit’ in the Run Window. Win+R | Regedit | Enter
  • Navigate to the following key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\ACE
  • In the right pane make search for the ‘MaxLocksPerFile’ entry and then modify its DECIMAL value to 100000 or 200000 or Hexadecimal value to 30d40. By default the decimal value assigned is 95000.  
  • Now close the registry editor and restart Microsoft Access to apply the settings.

Note:

Change the indexed property of field and duplicate data already exists in the table; you can either reset the indexed property back to previous setting or delete the duplicate records from the table.

Issue 2:  Hyperlink Data Type Field Not Working In Microsoft Access

What Is Behind The Hyperlink Data Type?

When you store data in Hyperlink column it will change the data you entered. The hyperlink data type is not a plain text. It’s actually a composite data type which consists of a DisplayText, the Address and optionally a SubAddress and a ScreenTip (Tool tip).

If you manually enter the text or paste it into fields from clipboard, the text gets analyzed by access and moreover depending on the actual link text, additional info is added and the data is stored internally in a concatenated string in the format of DisplayText#Address#SubAddress#ScreenTip.

You may also like:

How To Fix “Microsoft Access Database Engine Cannot Open Or Write To The File” Error?

Fix Access Forms And Reports Not Showing Image Issue

How To Fix Hyperlink Data Type Issue In Microsoft Access?

It’s better if you store your hyperlink addresses in plain text format. Now you want your email address or URL to be clickable and this appears like a link when displayed in a form in your applicable GUI.  You can still achieve that with formatting properties and few lines of VBA in your form.

Formatting part is quiet easy in latest version of Microsoft access. Just go to the Property Sheet for the textbox control that is displaying the hyperlink and set the DisplayAsHyperlink property to “Screen Only” and the ForeColor property to “Hyperlink Color”. – Done.

 Fix Hyperlink Data Type issue In Microsoft Access

In order to hyperlink-textbox to respond in just in single clicks, as a built-in hyperlink, just write an event procedure for it’s on Click-Event. Call the application, FollowHyperlink method in your procedure and after then pass the value of textbox as an argument.

Have a look over the working sample for such an event procedures.

Private Sub txtHyperlinkFullAddress_Click()

On Error GoTo txtHyperlinkFullAddress_Click_Err

If Not IsNull(Me.txtHyperlinkFullAddress.Value) Then

 Application.FollowHyperlink Me.txtHyperlinkFullAddress.Value

 End If

Exit Sub

txtHyperlinkFullAddress_Click_Err:

Call MsgBox(“Error ” & Err.Number & ” – ” & Err.Description & ” (in ” & Me.Name & “.txtHyperlinkFullAddress_Click)”)

End Sub

If that textbox is used partly for displaying hyperlink and also for editing it then it is recommended to use the On Dbl Click-Event (double click) to call up the hyperlink.

Wrap up:

Hopefully now you must have acquired good knowledge on how to deal with the access invalid field data type error.  And other data type related issues commonly encountered in Access Database. So try the solution and do share your experiences with us in our comment section.

How To Fix MS Access Error 3259- “Invalid Field Data Type” And It’s Related Issues…!