MS Access: Generate GUID - sql equivalent uniqueidentifier newid() function in access

I needed to generate GUID in access just like newid() function in SQL Server. This function generates 36 character string in this way 'BC6A36CA-4393-43FE-B9C3-84CF3A5D73B0'. The same has to be generated in MS access. I found two ways to to do this task.

Method 1: 
Create below function in your access vba form.  

Public Function GenerateGUID()
  GetGUID = Mid(CreateObject(“Scriptlet.TypeLib”).GUID, 2, 36)
End Function

Copy below script to update GUID in your table.

• Table name is Table1
• Column name is GUID

Private Sub Command1_Click()
Dim db As DAO.Database
Set db = CurrentDb
Dim rst As DAO.Recordset

Set rst = db.OpenRecordset("Table1")  '(Table1 is a Tablename)'

rst.MoveFirst   '(Moving to First Row)'
Do Until rst.EOF = True
    rst.Edit
    rst!GUID = GenerateGUID()
    rst.Update
    rst.MoveNext  '(Moving to Next Row)'
Loop
Me.Refresh
MsgBox "done"
End Sub

Method 2: 
Copy below code into your access module only.

Option Compare Database
    Option Explicit
    DefLng A-Z

    Private Type GUID
        Data1 As Long
        Data2 As Integer
        Data3 As Integer
        Data4(0 To 7) As String * 1
    End Type

    Declare Function CoCreateGuid Lib "ole32.dll" (tGUIDStructure As GUID) As Long

    Const mciLen As Integer = 4     'each part's length

Public Function CreateGUID() As String
    Dim sGUID   As String       'store result here
    Dim tGUID   As GUID         'get into this structure
    If CoCreateGuid(tGUID) = 0 Then 'use API to get the GUID
        With tGUID              'build return string
            sGUID = PadLeft(Hex(.Data1), mciLen * 2) & "-"
            sGUID = sGUID & PadLeft(Hex(.Data2), mciLen) & "-"
            sGUID = sGUID & PadLeft(Hex(.Data3), mciLen) & "-"
            sGUID = sGUID & FormatGUIDData4(.Data4())
        End With
        sGUID = sGUID      'ending brace
        CreateGUID = sGUID
    End If
End Function

Private Function FormatGUIDData4(aryData4() As String * 1) As String
    Dim i       As Integer      'loop thru the array
    Dim sGUID   As String       'store result here
    Dim sTemp1  As String       'first part here
    Dim sTemp2  As String       'second part here
    For i = LBound(aryData4()) To UBound(aryData4())   'process string array
        If i < 2 Then           'first part
            sTemp1 = sTemp1 & Hex(Asc(aryData4(i)))
        Else                    'second part
            sTemp2 = sTemp2 & Hex(Asc(aryData4(i)))
        End If
    Next
    sGUID = PadLeft(sTemp1, mciLen) & "-" & PadLeft(sTemp2, mciLen * 3) 'pad left with zeros
    FormatGUIDData4 = sGUID                     'return what we created
End Function
Private Function PadLeft(sString As String, iLen As Integer) As String
' Pad with left zreos if needed
    Dim sTemp As String
    sTemp = Right$(String$(iLen, "0") & sString, iLen)
    PadLeft = sTemp
End Function

Copy below script to update GUID in your table.

• Tabale name is Table1
• Column name is GUID

Private Sub Command1_Click()
Dim db As DAO.Database
Set db = CurrentDb
Dim rst As DAO.Recordset

Set rst = db.OpenRecordset("Table1")  '(Table1 is a Tablename)'

rst.MoveFirst   '(Moving to First Row)'
Do Until rst.EOF = True
    rst.Edit
    rst!GUID = CreateGUID()
    rst.Update
    rst.MoveNext  '(Moving to Next Row)'
Loop
Me.Refresh
MsgBox "done"
End Sub