Creating a table with an autonumber

[JackStockton]

I can create a table with an autonumber field that increments with this
code.

CREATE TABLE tblTest (TestID Counter CONSTRAINT PrimaryKey PRIMARY KEY,
Test TEXT (32))


What I really want though is a autonumber field that uses a GUID. If I
use the Access Table Designer, I just set the field Data Type to
AutoNumber, then change the Field Size from Long Integer to Replication
ID.

Can this be done either via a SQL statement(s) or a VBA function? Do I
have to do this manually with the Table Designer?

 

[Van T. Dinh]

CREATE TABLE tblTest
( TestID GUID CONSTRAINT PrimaryKey PRIMARY KEY,
Test TEXT (32))

 

[JackStockton]

That does create the primary key field as a GUID, but it doesn't
auto-generate the key as a records are added.

 

[Dirk Goldgar]

I can create a table with an autonumber field that increments with
this code.

CREATE TABLE tblTest (TestID Counter CONSTRAINT PrimaryKey PRIMARY
KEY, Test TEXT (32))


What I really want though is a autonumber field that uses a GUID. If
I use the Access Table Designer, I just set the field Data Type to
AutoNumber, then change the Field Size from Long Integer to
Replication ID.

Can this be done either via a SQL statement(s) or a VBA function? Do
I have to do this manually with the Table Designer?

You can do it with DAO:

Sub TestReplID()

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

Set db = CurrentDb

Set tdf = db.CreateTableDef("tblTest")
Set fld = tdf.CreateField("TestID", dbGUID)
fld.Attributes = fld.Attributes Or dbSystemField
tdf.Fields.Append fld
Set fld = tdf.CreateField("Test", dbText)
tdf.Fields.Append fld
db.TableDefs.Append tdf

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

End Sub

 

[JackStockton]

Finally got it...I needed to set the DefaultValue = "GenGUID()"

Thanks for the help

Jack

Public Function MakeTable(strRootTableName As String)
On Error GoTo proc_err
Dim dbs As dao.Database
Dim tdfNewTable As dao.TableDef
Dim newField As dao.Field
Dim idxPrimary As dao.index

Set dbs = CurrentDb()

' Create a new Tabledef Object for the new Task table
Set tdfNewTable = dbs.CreateTableDef("tbl" & strRootTableName)
' Create fields and indexes
With tdfNewTable
Set newField = .CreateField
With newField
.Name = strRootTableName & "ID"
.Type = dbGUID
.Size = 16
.Attributes = dbSystemField
.DefaultValue = "GenGUID()"
End With
.Fields.Append newField
Set newField = .CreateField
With newField
.Name = strRootTableName
.Type = dbText
.Size = 50
.AllowZeroLength = False
End With
.Fields.Append newField

Set idxPrimary = .CreateIndex("PrimaryIndex")
With idxPrimary
.Fields.Append .CreateField(strRootTableName)
.Unique = True
.Primary = True
End With
.Indexes.Append idxPrimary

' Append the new TableDef object to the database.
dbs.TableDefs.Append tdfNewTable
End With

Application.RefreshDatabaseWindow
Set tdfNewTable = Nothing
Set newField = Nothing
Set idxPrimary = Nothing

dbs.Close

Err.Clear

If Err = 0 Then
MakeTable = True
Else
MakeTable = False
End If
proc_exit:
Exit Function
proc_err:
MsgBox Err.Number & " " & Err.Description, vbOKOnly + vbCritical,
"Error creating Table tbl" & strRootTableName
MakeTaskTable = False
Resume proc_exit
Resume Next
End Function