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?

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


--
HTH
Van T. Dinh
MVP (Access)



"JackStockton" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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?
>

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

"JackStockton" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)
> 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

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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