[AccessD] Acc97: Creating GUID Autonumber using VBA code...

I happened to run across this today when I was looking for something else. I
found it at http://www.mvps.org/access/tables/tbl0016.htm.  Maybe it will
help?
To create the field using VBA and DAO,  you can use this function.
'  ********* Code Start ***********
' This code was originally written by Dev Ashish
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Function fCreateAutoNumberField( _
                ByVal strTableName As String, _
                ByVal strFieldName As String) _
                As Boolean
'
'   Creates an Autonumber field with name=strFieldName
'   in table strTableName.
'   Accepts
'       strTableName:   Name of table in which to create the field
'       strFieldName:    Name of the new field
'   Returns True on success, false otherwise
'

On Error GoTo ErrHandler
Dim db As DAO.Database
Dim fld As DAO.Field
Dim tdf As DAO.TableDef

    Set db = Application.CurrentDb
    Set tdf = db.TableDefs(strTableName)
    '   First create a field with datatype = Long Integer
    Set fld = tdf.CreateField(strFieldName, dbLong)
    With fld
        '   Appending dbAutoIncrField to Attributes
        '   tells Jet that it's an Autonumber field
        .Attributes = .Attributes Or dbAutoIncrField
    End With
    With tdf.Fields
        .Append fld
        .Refresh
    End With

    fCreateAutoNumberField = True

ExitHere:
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
    Exit Function
ErrHandler:
    fCreateAutoNumberField = False
    With Err
        MsgBox "Error " & .Number & vbCrLf & .Description, _
            vbOKOnly Or vbCritical, "CreateAutonumberField"
    End With
    Resume ExitHere
End Function
'  ********* Code End ***********


Jeanine Scott
Sr. Systems Analyst
Spindustry Systems
515-669-2074
jscott at spindustry.com

CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is
for the sole use of the intended recipient(s) and may contain confidential
information. Any unauthorized review, use, disclosure, or distribution is
prohibited. If you are not the intended recipient, please contact the sender
by reply e-mail and destroy all copies of the original message including any
attachments.

-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Jim Dettman
Sent: Friday, February 07, 2003 10:34 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Acc97: Creating GUID Autonumber using VBA code...

Shamil,

  I answered a question on this on experts exchange a while back.  I'm
trying to remember..  It was some kind of bug and the field needed to be
created in a very specific way.  I'll see if I can dig it out.

Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net

-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Shamil
Salakhetdinov
Sent: Friday, February 07, 2003 9:15 AM
To: AccessD
Subject: [AccessD] Acc97: Creating GUID Autonumber using VBA code...


Hi All,

Is the subj described somewhere? Should I RTFM with more attention and
accuracy?
The following code doesn't produce desired result:

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

    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs("tblTest")
    Set fld = tdf.CreateField("fldTest", dbGUID, 16)
    fld.Properties("DefaultValue") = "GenGUID()"
    tdf.Fields.Append fld

dbAutoIncrField value for Attributes property can't be used at all producing
error 3001 - Invalid argument
Any solutions?

TIA,
Shamil

--
e-mail: shamil at smsconsulting.spb.ru
Web: http://www.smsconsulting.spb.ru/shamil_s

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com