Link to home
Avatar of richardbarlow
richardbarlow

asked on

Remove a field from a table using VB then create a new field to replace it with a different data type

I need to remove an AutoNumber field from a temporary table (because I can't update an AutoNumber field with a query) and replace it with a Number field. I've found some code snippets but cannot get it to work.

The database is called catalogue.mdb in c:\catalogue
The table is tblTemp
The AutoNumber field is ID
I want a new Number field called ID
The cosde breaks at the 4th line with "Method or data member not found" and highlights .tblTemp at the end of that line

Can anyone suggest where I've gone wrong?
Thanks
Dim db As DAO.Database
    Set db = OpenDatabase("c:\catalogue\catalogue.mdb")
    Dim td As DAO.TableDef
    Set td = db.tblTemp
    'remove the ID field
    td.Fields.Remove ("ID")
    Dim fld As DAO.Field
    ' add a new field with the old name without the autonumber attribute set
    Set fld = td.CreateField("ID", dbLong) ' no autonumber
    td.Fields.Append fld

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of richardbarlow
richardbarlow

ASKER

Very good. You are the man