MORE INFORMATION
The
following files are available for download from the Microsoft Download
Center:
Release Date:
DEC-29-1998
For additional information about how to download
Microsoft Support files, click the following article number to view the article
in the Microsoft Knowledge Base:
119591 How to Obtain Microsoft Support Files from Online Services
Microsoft scanned this file for viruses. Microsoft used the most
current virus-detection software that was available on the date that the file
was posted. The file is stored on security-enhanced servers that help to
prevent any unauthorized changes to the file.
FileName Size
---------------------------------------------------------
AdoGUID.bas 3KB
AdoGUID.exe 60KB
AdoGUID.frm 25KB
AdoGUID.frx 1KB
AdoGUID.mdb 80KB
AdoGUID.vbp 2KB
Readme.txt 4KB
Microsoft Access has a ReplicationID AutoNumber field that is a
16-byte (128 bit) Globally Unique Identifier (GUID) that uniquely identifies
each record in the database. Please reference the sample project for the code
that demonstrates how to SELECT specific GUIDs and Insert GUIDs using the
AutoNumber field with Microsoft Access. The following function is a code
snippet from the sample that demonstrates how to SELECT a specific GUID from an
Access table using Microsoft ActiveX Data Objects (ADO):
Sample Code
Sub AccessReQueryADO()
On Error GoTo ErrorMessage
Dim adoCn As adoDb.Connection
Dim adoRs As adoDb.Recordset
Dim strCn As String
Dim strSQL As String
strCn = App.Path & "\adoGUID.mdb"
Set adoCn = New adoDb.Connection
With adoCn
.Provider = "Microsoft.JET.OLEDB.3.51"
.CommandTimeout = 500
.ConnectionTimeout = 500
.Open strCn, "admin", ""
End With
If Option7.Value = True Then
strSQL = "SELECT * FROM GUIDtable WHERE " & _
"Instr(1,[colGUID],'" & strGUID & "')"
Else
strSQL = "SELECT * FROM GUIDtable"
End If
Set adoRs = New adoDb.Recordset
With adoRs
Set .ActiveConnection = adoCn
.LockType = adLockOptimistic
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
End With
adoRs.Open strSQL
txtMessage.Text = ""
While Not adoRs.EOF
txtMessage.Text = txtMessage.Text & _
adoRs.Fields("colGUID").Value & " | "
txtMessage.Text = txtMessage.Text & _
adoRs.Fields("colDescription").Value & vbCrLf
adoRs.MoveNext
Wend
GoTo ExitSub
ErrorMessage:
MsgBox Err.Number & " : " & vbCrLf & Err.Description
ExitSub:
Label6.Caption = "- ReQueried AccessADO GUID Table..."
Set adoCn = Nothing
Set adoRs = Nothing
End Sub
Unlike SQL 6.5, SQL 7.0 supports a GUID datatype known as
UNIQUEIDENTIFIER. This datatype is a 16-byte GUID stored in the same format as
the Microsoft Access AutoNumber (GUID) datatype. There are subtle differences
concerning how to Insert and Retrieve the GUIDs among the different database
engines. Since SQL 6.5 does not support a native GUID datatype the methods used
for storing/retrieving GUIDs are much different than SQL 7.0 or Microsoft
Access. SQL 7.0 with the OLEDB provider is almost the same as Microsoft Access
with or without the OLEDB provider (SQLOLEDB), as you will see in the sample
code. With SQL 6.5 you must store the GUID as a VarBinary(16) datatype.
Consequently, to retrieve the GUID with SQL65 you must use a stored procedure
and build a Command object with the appropriate ByteArray parameter to pass to
the stored procedure SELECT statement.
NOTE: You must use the same
code techniques with SQL 7.0 as with SQL 6.5 if you are using the ODBC provider
(MSDASQL) since in ODBC 3.51 and below the GUID datatype is not recognized.
The 16-byte (128 bit) data structure of a GUID:
typedef struct _GUID
{
unsigned long Data1;
unsigned short Data2;
unsigned short Data3;
unsigned char Data4[8];
} GUID;
* Data1
An unsigned long integer data value.
* Data2
An unsigned short integer data value.
* Data3
An unsigned short integer data value.
* Data4
An array of unsigned characters.
To demonstrate GUIDs with SQL 7.0 or SQL 6.5 in the sample
project you must specify a valid (test) SQL 7.0/SQL 6.5 server and database. To
do so, navigate to the Connection Info tab and change the Server and Database
reference. The defaults are (local) Server and the Pubs database. Also, to use
the native GUID datatype for SQL 7.0, you must change to the OLEDB provider
(SQLOLEDB) by clicking the appropriate option button in the Provider frame at
the top of the Form. If you select ODBC as the provider for SQL 7.0 then the
application uses the same code as with SQL 6.5.
NOTE: The Connection
Info tab has no bearing on Microsoft Access. The default database for Microsoft
Access is included with the sample project AadoGUID.mdb as should reside in the
Application path.
For each database you select in the sample project
you must run Create Table to create the GUID test table and then Insert to
automatically generate some test GUID data before running a ReQuery.
NOTE: CoCreateGUID() is called in the Insert sample code to generate the test
GUID values.
In the sample application there is a ByteArray2GUID()
function that is used to convert the VarBinary(16) byte array to a GUID string
for display. Also, the function is used to convert the GUID string to a byte
array for storage in the SQLServer VarBinary(16) datatype column. Note that the
function is needed when using the GUIDs interchangeably between Microsoft
Access and SQL 6.5. If you Export the Microsoft Access table to SQL 6.5 you
will see that the bytes are not stored in the same order in which they display
in the Microsoft Access table view. For example:
Reversed... Not Reversed...
>----------------<|>---------------<
20C68F83-9593-0011-BFBB-00C04F8F8347 'SQLServer view after table Export.
838FC620-9395-1100-BFBB-00C04F8F8347 'Microsoft Access view.
NOTE: The bytes are in (DWord and Word) reverse order after
Exporting the Microsoft Access table.
Because the Microsoft Access
Upsizing Wizard results in the same storage of the bytes in SQL 6.5, you must
use the ByteArray2GUID() and GUID2ByteArray() functions to remain compatible
with the storage of the GUIDs in Microsoft Access. If you do not need to Export
the Microsoft Access table to SQL 6.5 or upsize the Microsoft Access database
to SQL 6.5 then you need only store the bytes in a straightforward fashion.
The following is a code snippet from the code sample that
demonstrates the storage of the GUID in the byte format of Microsoft Access.
Sample Code
Sub SQL65InsertGUID()
'Insert GUID record.
On Error GoTo ErrorMessage
Dim adoCn As adoDb.Connection
Dim adoRs As adoDb.Recordset
Dim strGUIDtmp As String
Dim bytGUID() As Byte
Dim strCn As String
Dim strSQL As String
strCn = "Provider=" & strProvider & _
";Driver={SQL Server}" & _
";Server=" & txtServer & _
";Database=" & txtDatabase & _
";Uid=" & txtUserID & _
";Pwd=" & txtPassword
Set adoCn = New adoDb.Connection
With adoCn
.ConnectionString = strCn
.CommandTimeout = 500
.ConnectionTimeout = 500
.Open
End With
strGUIDtmp = strGUID
bytGUID = GUID2ByteArray(FilterGUID(strGUIDtmp))
strSQL = "SELECT * FROM GUIDtable WHERE 1=0"
Set adoRs = New adoDb.Recordset
With adoRs
Set .ActiveConnection = adoCn
.LockType = adLockOptimistic
.CursorLocation = adUseServer
.CursorType = adOpenForwardOnly
End With
adoRs.Open strSQL
adoRs.AddNew
adoRs.Fields("colGUID").Value = bytGUID
adoRs.Fields("colDescription").Value = "This is a test GUID"
adoRs.Update
GoTo ExitSub
ErrorMessage:
MsgBox Err.Number & " : " & vbCrLf & Err.Description
ExitSub:
Label6.Caption = "[ASCII 176] Inserted SQL65 GUID Record..."
Set adoCn = Nothing
Set adoRs = Nothing
End Sub
'======================
Function GUID2ByteArray(ByVal strGUID As String) As Byte()
Dim i As Integer
Dim j As Integer
Dim sPos As Integer
Dim OffSet As Integer
Dim sGUID(0 To 2) As Byte
Dim bytArray() As Byte
ReDim bytArray(0 To 15) As Byte
sGUID(0) = 7
sGUID(1) = 11
sGUID(2) = 15
OffSet = 0
sPos = 0
'AABBCCDD-AABB-CCDD-XXXX-XXXXXXXXXXXX 'Microsoft Access view.
'DDCCBBAA-BBAA-DDCC-XXXX-XXXXXXXXXXXX 'SQLServer view.
'Need to loop through to build the GUID byte array in the Microsoft
'Access storage format since the first eight bytes are reversed.
For i = 0 To UBound(sGUID)
For j = sGUID(i) To (OffSet + 1) Step -2
bytArray(sPos) = "&H" & Mid$(strGUID, j, 2)
sPos = sPos + 1
Next j
OffSet = sGUID(i)
Next i
For i = 17 To 31 Step 2
bytArray(sPos) = "&H" & Mid$(strGUID, i, 2)
sPos = sPos + 1
Next i
GUID2ByteArray = bytArray()
End Function
REFERENCES
For more information, please see the following article in
the Microsoft Knowledge Base:
176790 : How To Use CoCreateGUID API to Generate a GUID with VB
Microsoft SQL 7.0 Books Online; search on: "UNIQUEIDENTIFIER"
Microsoft SQL 6.5 Books Online; search on: "VarBinary"
Microsoft Access Help; search on: "ReplicationID - AutoNumber field"