Creating Autonumbers GUID fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Creating Autonumbers GUID fields

Author

Topic

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-05-04 : 01:49:09

In Access, using DAO, I have this:

        Set fld = tdf.CreateField("ID", dbGUID)
        fld.Attributes = 1
        fld.DefaultValue = "GenGUID()"
        tdf.Fields.Append fld

but it doesn't work.

How do I make the ID field an Autonumber GUID?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-04 : 04:35:44

i don't know much about access but shouldn't "GenGUID()"
be without ""? because it's a function call??

Go with the flow & have fun! Else fight the flow

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-05-04 : 19:27:42

You're right -you don't know much about access!

No, it has to have the quotes...if you think about it - if I had
fld.DefaultValue = GenGUID()
then I would be setting the default value of every field to the SAME value GUID which was returned by GenGUID() at the time the table was created, rather than generating a GUID when new records are created in the future.

The equivalent would be if I set fld.DefaultValue=Now(), then every record would have the same default value.

Anyone else?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-05 : 05:01:10

touche...

Go with the flow & have fun! Else fight the flow

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2005-05-05 : 08:41:55

Rob, a shot in the dark but does this work? I dont have Access on this machine so I cant test it, but could this work:


fld.DefaultValue = "=GenGUID()"

I'm hoping Access will see the equal to sign and understand to evaluate the expression instead of treating it like a literal?

OS

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-05-05 : 19:52:33

thanks mohdowais - no - but nice try

And after an extensive search of the internet, it looks like the only way to create these is through the table designer.

In case anyone else is searching on this and wants a solution, it would be worth reading this article which suggests that using an Autonumber GUID in Access may lead to problems down the road, mainly because (he suggests) Microsoft continue to change the way this is supported in Access, with the StringFromGUID() and GUIDFromString() functions and the way GUIDs are represented.

I ended up generating my own GUIDs, and leaving the GUID fields as not Autonumbers. It just means that when I create a record (which I only ever do through VBA in this application) I generate a GUID first. This is easy enough and means I also know the ID value for the record I'm creating, rather than waiting to find out...

To use the OLE32.dll GUID generation in Access, you'll need something like this (paste it into a new module):

Option Compare Database
Option Explicit

Private Declare Function CoCreateGuid Lib "OLE32.DLL" _
    (pGUID As MyGUID) As Long

Private Declare Function StringFromGUID2 Lib "OLE32.DLL" _
    (pGUID As MyGUID, _
     ByVal PointerToString As Long, _
     ByVal MaxLength As Long) As Long

Private Type MyGUID
    GUID1 As Long
    GUID2 As Long
    GUID3 As Long
    GUID4(0 To 7) As Byte
End Type

Public Function CreateGUID() As String

    Dim uGUID As MyGUID
    Dim sGUID As String
    Dim lResult As Long

    lResult = CoCreateGuid(uGUID)

    If lResult Then
        sGUID = ""
    Else
        sGUID = String$(38, 0)
        StringFromGUID2 uGUID, StrPtr(sGUID), 39
    End If

    CreateGUID = sGUID

End Function

I've found that using this code to generate my own GUID, I'm not having the problems using the StringFromGUID() and GUIDFromString() functions supplied by Access that I was before, and it is now all working very nicely thank you.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources