Insert Autonumber Row into selected tables

  • Thread starter Thread starter M. Wild
  • Start date Start date
M

M. Wild

I've abandoned my quest to generate an autonumber field
through a query that exports to a table. My GIS program
that eventually reads these tables, requires an autonumber
field and not a primary integer field.

Instead, I think I need to come up with a way to loop
through selected tables (up to 20), open each in design
mode, insert an autonumber field as the first field, and
save table. Should this be a macro or a VB routine?

Any help to get me started would be greatly appreciated.
Thanks,
Mike
 
M. Wild said:
I've abandoned my quest to generate an autonumber field
through a query that exports to a table. My GIS program
that eventually reads these tables, requires an autonumber
field and not a primary integer field.

Instead, I think I need to come up with a way to loop
through selected tables (up to 20), open each in design
mode, insert an autonumber field as the first field, and
save table. Should this be a macro or a VB routine?

Any help to get me started would be greatly appreciated.
Thanks,
Mike
Click to expand...

You don't actually have to open the tables in design view, by code or
otherwise, in order to add an autonumber field. You can create an
autonumber field with a simple SQL statement, but I don't know offhand
how you can make that field be the first field. You can do it via DAO,
though, using code like this:

'---- start of code -----
Sub AddAutonumber(TableName As String, FieldName As String)

' TableName is the name of an existing table
' FieldName is the name of the field you want to add.

On Error GoTo Err_AddAutonumber

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

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

Set fld = tdf.CreateField(FieldName, dbLong)
With fld
.Attributes = fld.Attributes Or dbAutoIncrField
.OrdinalPosition = 0
End With
tdf.Fields.Append fld

Exit_AddAutonumber:
Set tdf = Nothing
Set db = Nothing
Exit Sub

Err_AddAutonumber:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_AddAutonumber

End Sub

'---- end of code -----

You could call the routine like this to add an autonumber field named
"ID" to table "MyTable":

AddAutonumber "MyTable", "ID"

Now, this routine does not *index* the autonumber field you just added,
nor make it the primary key. To do either of those would take a bit
more code, and it wasn't clear from your question whether you needed it
or not.

Also, if you call this routine to add autonumber fields to each of your
twenty tables, it should be clear that those fields are all completely
unrelated. That's what you asked for, but it may not be what you had in
mind.
 
Back
Top