adding a indexed field

  • Thread starter Thread starter mpsamuels01
  • Start date Start date
M

mpsamuels01

Is there a macro that can be created so that I can add an idex field to
already created tables. I have a database that has about 65 tables that
I am in the process of moving over to sql the problem is that none of
the tables have an indexed field which is a requirement for sql so I
was wondering how can I add an index field that does not allow
duplicates which means I can use the autonumber selection.
Thank You.
 
The VBA code below will add an AutoNumber field to all non-system,
non-attached tables in the MDB, and create a primary key index on that
field.

Note that this code assumes that none of the tables has an existing primary
key or an existing AutoNumber field - or, for that matter, any field with
the name 'ID'. You'll need to modify the code if any of these assumptions
don't hold true for your database.

Public Sub AddAutoNumber()

Dim db As DAO.Database
Dim tdfs As DAO.TableDefs
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index

Set db = CurrentDb
Set tdfs = db.TableDefs
For Each tdf In tdfs
If ((tdf.Attributes And dbSystemObject) = 0) And _
((tdf.Attributes And dbAttachedTable) = 0) And _
((tdf.Attributes And dbAttachedODBC) = 0) Then
Set fld = tdf.CreateField("ID", dbLong)
fld.Attributes = dbAutoIncrField
tdf.Fields.Append fld
tdf.Fields.Refresh
Set idx = tdf.CreateIndex("PrimaryKey")
Set fld = idx.CreateField("ID", dbLong)
idx.Fields.Append fld
idx.Primary = True
tdf.Indexes.Append idx
End If
Next tdf

End Sub
 
Back
Top