HOWTO: Use GUID Fields in Access from Visual C++

• Microsoft Visual C++, 32-bit Editions, versions 4.0, 4.0a, 4.1, 4.2, 4.2b, 5.0, 5.0sp1 on the following platform: - x86

SUMMARY

Microsoft Access 95 introduced a new GUID (Globally Unique Identifier) data type used for database replication. You can use GUID fields (called Replication ID fields in Access) to store any GUID number in a compact 16-byte binary format that can be indexed and used as a primary key for a table. This article explains how to create and use Microsoft Access GUID data type fields with Visual C++ MFC database classes.

MORE INFORMATION

Creating a GUID Field Using SQL DDL

Microsoft Access GUID fields can be created using a "CREATE TABLE" SQL DDL statement. The following code will create a table called MyGUIDTable with a GUID field named MyGUIDField in database c:\db1.mdb using SQL DDL:

   CDaoDatabase db;
   db.Open(_T("c:\\db1.mdb"));
   db.Execute(_T("CREATE TABLE MyGUIDTable (MyGUIDField GUID)"));

Creating a GUID Field Using CDaoTableDef::CreateField()

You can also use a CDaoTableDef to create GUID data type field using the CreateField method:

   CDaoDatabase db;
   db.Open(_T("c:\\db1.mdb"));

   // Create new tabledef and create GUID field.
   CDaoTableDef td(&db);
   td.Create(_T("MyGUIDTable"));
   td.CreateField("MyGuidField", dbGUID, 16);  // Note: field size is 16.

   // Append table to tabledefs collection to create physical table.
   td.Append();

Inserting and updating GUID fields using SQL

You can both insert and update GUID fields using standard SQL INSERT INTO and UPDATE syntax. GUIDs are commonly displayed in textual format as:

   {11223344-1122-1122-1122-AABBCCDDEEFF}

   {guid {11223344-1122-1122-1122-AABBCCDDEEFF}}

   CDaoDatabase db;
   db.Open(_T("c:\\db1.mdb"));
   db.Execute(_T("INSERT INTO MyGUIDTable (MyGuidField) VALUES
      ({guid {11223344-1122-1122-1122-AABBCCDDEEFF}})"));

   SET MyGuidField = {guid {11223344-1122-1122-1122-AABBCCDDEEFF}}

Inserting and Updating GUID Fields Using CDaoRecordset

When using GUID fields with DAO objects, you create a VT_BSTRT type COleVariant field using the same textual GUID syntax as the SQL method above. This code demonstrates how to insert a GUID field using CdaoRecordset methods:

   // Open database and recordset objects.
   CDaoDatabase db;
   db.Open(_T("c:\\db1.mdb"));
   CDaoRecordset rs(&db);
   rs.Open(dbOpenTable,_T("MyGUIDTable"));

   // Create VT_BSTRT variant for GUID.
   COleVariant varGUIDValue(_T("{guid    {11223344-1122-1122-1122-
   AABBCCDDEEFF}}"), VT_BSTRT);

   // Add record using CDaoRecordset method.
   rs.AddNew();
      rs.SetFieldValue(_T("MyGUIDField"),varGUIDValue);
   rs.Update();

NOTE: When calling GetFieldValue(), the variant is returned as type VT_BSTR.

Using Seek And FindFirst with GUID Fields

Microsoft Access does not allow you to use GUID fields with the Find methods available to CDaoRecordset. If you attempt to use a Find method with a GUID field, the following error message appears:

   GUID not allowed in Find method criteria expression

   // Open database.
   CDaoDatabase db;
   db.Open(_T("c:\\db1.mdb"));

   // Create a table called MySeekTable with a GUID primary key field named
   // ID and a field named Found.
   db.Execute(_T("create table MySeekTable (ID guid constraint PKEY primary
   key, Found text)"));

   // Insert a few records using an insert into statement.
   db.Execute(_T("insert into MySeekTable (ID, Found) values ({guid
   {11111111-aabb-aabb-aabb-aabbccddeeff}},'Guid1')"));
   db.Execute(_T("insert into MySeekTable (ID, Found) values ({guid
   {22222222-aabb-aabb-aabb-aabbccddeeff}},'Guid2')"));
   db.Execute(_T("insert into MySeekTable (ID, Found) values ({guid
   {33333333-aabb-aabb-aabb-aabbccddeeff}},'Guid3')"));

   // Open table-type recordset (must use table-type for Seek) and select
   // index for seek.
   CDaoRecordset rs(&db);
   rs.Open(dbOpenTable,_T("MySeekTable"));
   rs.SetCurrentIndex(_T("PKEY"));

   // Construct the GUID you want to find and seek the GUID.
   COleVariant varGUIDValue(_T("{guid {22222222-aabb-aabb-aabb-
   aabbccddeeff}}"), VT_BSTRT);

   if (rs.Seek(_T("="),&varGUIDValue)) {
      // GUID found. Retrieve and display value.
      CString strResult = V_BSTRT(&rs.GetFieldValue(_T("Found")));
      AfxMessageBox("Seek of guid found '" + strResult + "'");
   } else {
      // GUID not found.
      AfxMessageBox("Seek of guid failed.");
   }

Using GUID Fields with MFC ClassWizard-Generated Recordsets

The MFC ClassWizard automatically binds GUID fields to a CString class member and sets up the proper DoFieldExchange macros. No additional changes are needed to display, insert, and update GUID fields when you use the MFC ClassWizard to create CRecordset or CDaoRecordset derived classes. The fields are displayed in the Microsoft Access GUID format:

   {guid {11223344-1122-1122-1122-AABBCCDDEEFF}}

Creating "AutoNumber" GUID Fields Programmatically

Microsoft Access also provides an "AutoNumber" type GUID field. You can use this type of field for the primary key of a table and let Access automatically generate each new GUID. The GUIDs are randomly generated by the Microsoft Jet database engine each time a record is inserted into the table using an complex algorithm that will avoid duplicates over all databases in the world until the year 3400 AD.

You cannot create an "AutoNumber" type GUID field using SQL DDL, you must use DAO code. The following code demonstrates how to create an "AutoNumber" type GUID field using CDaoTableDef::CreateField method:

   // Open database and create tabeldef.
   CDaoDatabase db;
   db.Open(_T("c:\\db1.mdb"));
   CDaoTableDef td(&db);
   td.Create(_T("MyGUIDTable"));

   // Create and fill out a CDaoFieldInfo structure.
   CDaoFieldInfo fi;
   fi.m_strName = _T("ID");
   fi.m_nType = dbGUID;
   fi.m_lSize = 16;
   fi.m_nOrdinalPosition = 0;
   fi.m_bAllowZeroLength = FALSE;
   fi.m_bRequired = FALSE;
   fi.m_lAttributes = dbSystemField;
   fi.m_strValidationRule = _T("");
   fi.m_strValidationText = _T("");
   fi.m_strDefaultValue = _T("GenGUID()"); // This generates the GUID.

   // Create field using CDaoFieldInfo constructor and append table.
   td.CreateField(fi);
   td.Append();

REFERENCES

Microsoft Jet Database Engine Programmer's Guide, p. 246

Keywords          : MfcDAO MfcDatabase
Technology        : kbMfc
Version           : 4.0 4.0a 4.1 4.2 4.2b 5.0
Platform          : NT WINDOWS
Issue type        : kbhowto