AccessBlog.net

News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

Monday, August 01, 2005

How to create a GUID autonumber field with DAO

The problem that once you create field with dbGUID type, and assign a default value to it as GenGUID() it starts working, but on new row it shows "#Name". The trick is to set dbSystemField attribute flag for this field, then "#Name" will gone.
Here a procedure which Access MVP John Spencer build to demonstrate this:

Sub BuildGUIDAutonumber()
'Test Procedure

Dim dbany As DAO.Database
Dim tdefAny As DAO.TableDef
Dim fldAny As DAO.Field
Set dbany = CurrentDb()
On Error Resume Next
dbany.TableDefs.Delete "A__A"
On Error GoTo 0
dbany.TableDefs.Refresh

Set tdefAny = dbany.CreateTableDef("A__A")
With tdefAny
Set fldAny = .CreateField("GUIDFld", dbGUID)
fldAny.Attributes = fldAny.Attributes Or dbSystemField
fldAny.Type = dbGUID
fldAny.Properties("DefaultValue") = "GenGUID()"
.Fields.Append fldAny
End With
dbany.TableDefs.Append tdefAny
dbany.TableDefs.Refresh
End Sub

1 Comments:

Anonymous Anonymous said...

bloody great, thanks for the help.

5:04 AM  

Post a Comment

<< Home