CreateField - AutoNumber

[vipersmind]

I wish to make a new table with ID as a autonumber field.
I am using the code below to achieve most of this.
I can't find the right data type to get the ID as autonumber

Can you?

Function makestag1()

    Dim db As Database
    Dim tbl As TableDef
    Dim fld As Field
    Dim idx As Index
    
    'create references to the database
    
    Set db = CurrentDb()
    
    ' specify what to call the new table
    
    Set tbl = db.CreateTableDef("tbl_Validation_Development_FromToStag1")
    
    ' create the field references for the new table
    
    Set fld = tbl.CreateField("ID", [COLOR=red]dbGUID[/COLOR] )
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("HoleID", dbText, 20)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("SampleID", dbText, 10)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("From", dbDouble)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("To", dbDouble)
    tbl.Fields.Append fld
    
    ' append the new fields to the table
    db.TableDefs.Append tbl
    
    ' create a primary key index
    Set idx = tbl.CreateIndex("PrimaryKey")
    Set fld = idx.CreateField("ID")
    idx.Primary = True
    idx.Unique = True
    idx.Fields.Append fld
    tbl.Indexes.Append idx
         
End Function

 

[llkhoutx]

Try dBGuid as COUNTER or AUTOINCREMENT.

 

[vipersmind]

No avail

llkhoutx

I have tried many different combination up there but no change
Most are not recognised 'user defined' error

 

[llkhoutx]

On page 795 of Norvalis' & Jones' Access 2002 VBA Handbook, it is stated that

"You cannot designate an autonumber data type directly when you create a field using the CreateField method because Jet does not recognize the AutoNumber data type. Instead, set the datatype to dbLong and then set the field's Attribute property to the intrinsic constant dbAutoIncrField . . ."

Your relevant code should be

Set fld = tbl.CreateField("ID", dbLong)
fld.Attributes = dbAutoIncrField
tbl.Fields.Append fld

 

[vipersmind]

llkhoutx

You, like many people on this site, are a lifesaver,
Thank you for your help.

Cheers
Cress