Adding and changing fields with code

[NormDuffy]

I have a need to change an autonumber field in a table to a long integer type then to add another autonumber field as a key field. I can't find a way to change the field type. I can delete the field and add a new one which works great but I need to change the existing field properties first.

The following code is based on some sample code that I have modified to do most of what I need but have just got a little stuck on some things.

Sub ModifyTable()
Dim dbsThisDatabase As Database
Dim tdfThisTable As TableDef, fld1 As Field, fld2 As Field
Dim idx As Index, fldIndex As Field

Set dbsThisDatabase = CurrentDb
Set tdfThisTable = dbsThisDatabase.TableDefs!tblPresortedMasterFile

Set fld1 = tdfThisTable.CreateField("ContactID", dbLong)
fld1.Attributes = fld1.Attributes + dbAutoIncrField

tdfThisTable.Fields.Append fld1
' Create primary key index.
Set idx = tdfThisTable.CreateIndex("PrimaryKey&quot
Set fldIndex = idx.CreateField("ContactID", dbLong)
' Append index fields.
idx.Fields.Append fldIndex
' Set Primary property.
idx.Primary = True
' Append index.
tdfThisTable.Indexes.Append idx

dbsThisDatabase.TableDefs.Refresh
Set dbsThisDatabase = Nothing
End Sub

 

[andrewvanbeck]

Why not:

Make a new double-word field.

Copy the contents of the autonumber field into that.

Delete your autonumber field.

Create a fresh autonumber field.

- Access hates you doing anything to autonumber fields, often you better off making your own numbering system.

Andrew.

 

[NormDuffy]

Thanks Andrew
I had considered this but it also apears difficult to rename a field. To keep the existing field name this action would require a new field to be created then updated to the old ID numbers. Then I would need to delete the old field and create a new one with the same name and then update this field with the data etc etc.

I can do it all easily with the sendkeys option but if the user happens to swap to another window during the modification then the keys can be sent to the wrong window and this all gets a bit messy

I was hoping that maybe there would be a simple way to just change a field type.... but it looks as not all things are easy with Microsoft. Also the table / tables are supplied by other customers and somtimes relationships may exist. Gets complex and a bit messy.

Then maybe I could just break the app into sections and prompt the user to manually change the field type but you can't always trust users.
Cheers
Norm