How add an autoincrement GUID field by code?

  • Thread starter Thread starter Bertrand
  • Start date Start date
B

Bertrand

Hi All.
I'm trying to create a table with a autoincrement field of type GUID using
DAO code.
In the past I used this type of procedure with dbLong type as in the
following code.
But now every attempt with dbGUID fails!
I digged aroud without success and it seems that nobody adds auto guid
fields by code.

Could you please confim me if this operation is possible or not?

Many thanks
B.

Set dbDat = OpenDatabase(strPath)
Set tdf = dbDat.CreateTableDef("TestTable")
tdf.Fields.Append tdf.CreateField("Order_id", dbText, 10)
tdf.Fields.Append tdf.CreateField("Date", dbDate)
tdf.Fields.Append tdf.CreateField("Test1", dbSingle)
Set fld = tdf.CreateField("GUID", dbLong)
fld.Attributes = dbAutoIncrField
tdf.Fields.Append fld
dbDat.TableDefs.Append tdf
dbDat.TableDefs.Refresh
 
Bertrand, I've never used replication fields, so someone else could probably
give you a better answer.

If you manually create a table with an AutoNumber primary key of size
Replication ID, and then examine the properties of this field, it appears
that:
- the Attributes propeprty is dbFixedField
- the DefaultValue property is a function call to GenGUID()

Try something like this:

Function MakeGuidTable()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set db = CurrentDb()
Set tdf = db.CreateTableDef("MyGuidTable")
With tdf
Set fld = .CreateField("MyID", dbGUID)
fld.Attributes = dbFixedField
fld.DefaultValue = "GenGUID()"
.Fields.Append fld
End With
db.TableDefs.Append tdf
End Function

If you are interested in seeing the properties of the GUID field, create a
table named Table1, with an AutoNumber field named "ID" that has its size
set to Replication ID. Then this should show you the properties:

Function ShowGuidProps()
On Error GoTo Err_Handler
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Set db = CurrentDb
Set tdf = db.TableDefs("Table1")
Set fld = tdf.Fields("ID")
For Each prp In fld.Properties
Debug.Print prp.Name & Space(20 - Len(prp.Name));
Debug.Print prp.Value;
Debug.Print
Next
Exit_Handler:
Exit Function

Err_Handler:
Select Case Err.Number
Case 3219, 3267, 3251
Debug.Print "N/A";
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Handler
End Select
End Function
 
Not to answer your question, but: I believe there are some "gotchas" in
using guid fields in Access & Jet. For more information, try the
following search in google groups:

guid kaplan group:*access*

HTH,
TC [MVP Access]
 
Back
Top