MS Access run-time error 3259 invalid field data t

Question

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?

Answer 1

I was having the same problem. Apparently it was because I had created a new table by copying the structure of an existing one that already had an AutoNumber field. For whatever reason it was giving me this error on the copy. It worked after deleting the table and recreating it from scratch.

Answer 2

I was having the same problem, and it has been solved by "Compact & Repair" the MS Access database.

Answer 3

I was having the same problem. I was checking "Hoew to alter the data definition of a linked table" when it occurred to me that I could try changing the field datatype to NUMBER and see if that code worked.

So I changed the code to:

ALTER TABLE XXXXX ALTER COLUMN ID NUMBER

Run the code with no problems! Checked the table design, and the field datatype had changed from Autonumber to Number.

So, I decided to try the original code again:

ALTER TABLE XXXX ALTER COLUMN ID COUNTER (1,1)

and... IT WORKED!!! O.O

I don't understand why... but it worked...

Try changing the datatype to NUMBER via code, run it, then change it back to COUNTER(1,1), and see if you got the error fixed, like mine did...

Answer 4

Late reply, but others may find this problem too.

By changing

DoCmd.RunSQL "DELETE * FROM YourTable"
CurrentDb.Execute "ALTER TABLE YourTable ALTER COLUMN ID COUNTER(1,1)"

to

DoCmd.RunSQL "DELETE * FROM YourTable"
CurrentDb.Execute "ALTER TABLE YourTable ALTER COLUMN ID COUNTER(1,2)"

Mine did not give an error anymore, and it did "reset" the auto number.

Answer 5

ALTER COLUMN ID NUMBER worked for me too, but it can be used probably only in case of empty table, otherwise setting back the ID field as Autonumber (ALTER COLUMN ID COUNTER(next ID number here,1)) would not work. At least manually can't be set.
This is .mdb Access 10 (2002) file put in C://directory/ above any user directory, under Windows XP, opened by Runtime 2010. I have never noticed such behavior when it was inside user directory, like My Documents or Desktop, but maybe this is coincidental and it will occur sometime. It happened first time after few years of using this file with that code.

Edited next day:
It seems to be partially explained. The reason is collating order. The database was created and used previously with this setting:
Tools > Options > General > New database sort order > Polish
but in the middletime, this setting in the program was set to > General
And now it displays that error. It can be fixed easily setting back > Polish
and running Compact and Repair database (ALTER COLUMN ID COUNTER begins to work again). I have repeat this few times so far and each time it worked. But with General Compact and repair database doesn't help ever.

This subroutine:

Private Sub IDProperties()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim TableName As String
Dim i As Integer
Dim str As String

TableName = "the name of table"

Set db = CurrentDb()
Set tdf = db.TableDefs(TableName)

For i = 0 To tdf.Fields("ID").Properties.Count - 1
    On Error Resume Next
    str = str & vbNewLine & tdf.Fields("ID").Properties(i).name
    If Err > 0 Then
        str = str & vbNewLine & Err.Number & " " & Err.Description
        Err.Clear
    End If
    str = str & " = " & tdf.Fields("ID").Properties(i).Value
    If Err > 0 Then str = str & "; " & Err.Number & " " & Err.Description
    On Error GoTo 0
Next
Set tdf = Nothing

db.Close
Set db = Nothing

Debug.Print str

End Sub

returns i.e. collating order of the ID field. For tables which return 3259 error with sort order = General, ID CollatingOrder = 1045. If such table is deleted and imported a fresh one, ID CollatingOrder = 1033 and 3259 error does not occur. So it seems, that Access changes collating order of the ID field during import. That would be second way to fix that. Or importing entire database to a fresh file. Third way is copying such table - it apparently changes ID's CollatingOrder too. The best would be changing collating order by VBA, but it is read only property.

Private Sub GetCollatingOrder()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim TableName As String

Set db = CurrentDb()
Set tdf = db.TableDefs("table name")

MsgBox "Collating order" & _
   vbNewLine & "- database: " & db.CollatingOrder & _
   vbNewLine & "- table: " & tdf.Fields("ID").Properties("CollatingOrder").Value
   'or tdf.Fields("ID").CollatingOrder

Set tdf = Nothing
db.Close
Set db = Nothing

End Sub

Only database sort order can be changed:

Application.SetOption "New Database Sort Order", 1033 ' or 1045 for example

but it changes property of someone's database ... And maybe still compact and repair is required.

Edited later:
Now MsgBox shows 1045 for both: General and Polish (and both: db and ID) and stopped displaying error ... Very strange. Maybe because of several compact and repair.
Edited later:
Next day again the same error if New database sort order not changed + Compact and Repair. So General stopped working after rebooting. The CollatingOrder property values in the MsgBox are different for ID and entire database before Compact and Repair, the same after Compact and Repair (preceeded by New database sort order change).