Changing the field type

[FBxiii]

Hi.

I have a system where a file is imported to a new table and is given an
AutoNumber field so each record has a sequential reference number.

At the end of the import process, I need to 'juggle' the reference number
between records so they are sorted differently on a report.

I am trying to change the field type to an Integer but get an "invalid
operation" error.

Here is my code, am I doing anything wrong? Can this be done?

Dim tdef As TableDef
Set db = OpenDatabase("*DB_Path*")
Set tdef = db.TableDefs("*TableName*")
tdef.Fields("Record_ID").Type = dbInteger

Any ideas?

Cheers,
Steve.

 

[Douglas J. Steele]

It can't be done in that way.

One way is to add a new field, run an Update SQL statement to populate the
new field, then delete the old field when you know it's populated.

Another way is to use DDL:

Dim strSQL As String

strSQL = "ALTER TABLE *TableName* " & _
"ALTER COLUMN Record_ID INTEGER"
Set db = OpenDatabase("*DB_Path*")
db.Execute strSQL, dbFailOnError

 

[FBxiii]

Hi.

I have tried your suggestion and it works a treat!

Thanks,
Steve.