How to use GUID fields in Access from Visual C++ (170117)

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.

Creating a GUID field by 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.

Sample code

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

The DDL method above will also work using the CDatabase::ExecuteSQL method against the Microsoft Access Open Database Connectivity (ODBC) driver.

Creating a GUID field by using CDaoTableDef::CreateField

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

Sample code

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 by 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}

Microsoft Access requires a similar textual format when using GUIDs in SQL statements:

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

The following code will insert a GUID field into the MyGUIDTable table.

Sample code

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

You can also use a GUID in the SET portion of an UPDATE SQL statement:

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

Note You do not need quotes around the GUID field.

Using literal GUIDs with the Microsoft Access ODBC driver

Because ODBC defines the curly brace as an escape code for vendor specific escape clauses, you must turn off escape clause scanning when you use literal GUIDs in SQL statements with the Microsoft Access ODBC driver. Note that this functionality is not supported in the Microsoft Access ODBC driver that ships with MDAC 2.1 or later.

If you do not turn off the escape clause scanning, you get the following error message from the driver: The following example demonstrates how to use literal GUIDs with the Microsoft Access ODBC driver.

Sample code

CString strSQL, strDescription;
CDatabase db;
// Open Microsoft Access database using ODBC.
db.OpenEx( "DSN=NW97;", CDatabase::noOdbcDialog );
// Drop and re-create the test table.
try
{
    strSQL = "DROP TABLE Literal";
    db.ExecuteSQL( strSQL );
}
catch(CDBException *e)
{
    e->Delete();  // Ignore errors.
};
strSQL = "CREATE TABLE Literal (LinkID GUID, Description TEXT)";
db.ExecuteSQL( strSQL );
// Turn off driver scanning for escape clauses to give the raw
// SQL to Access.
::SQLSetConnectOption( db.m_hdbc, SQL_NOSCAN, SQL_NOSCAN_ON );
// Run INSERT SQL statements to add two records.
strSQL = "INSERT INTO Literal (LinkID, Description) VALUES "
         "({guid {11223344-1122-1122-1122-AABBCCDDEEFF}}, 'Guid 1')";
db.ExecuteSQL( strSQL );
strSQL = "INSERT INTO Literal (LinkID, Description) VALUES "
         "({guid {11223344-1122-1122-1122-FFFFFFFFFFFF}}, 'Guid 2')";
db.ExecuteSQL( strSQL );
// Turn the driver scanning for escape clauses back on.
::SQLSetConnectOption( db.m_hdbc, SQL_NOSCAN, SQL_NOSCAN_OFF );
// Now use a literal GUID in a WHERE clause of a select
// statement to find the record.
CRecordset rs( &db );
// Turn off scanning for recordset.
::SQLSetStmtOption( rs.m_hstmt, SQL_NOSCAN, SQL_NOSCAN_ON );
// Open recordset using a literal GUID in a WHERE clause.
strSQL = "SELECT * FROM Literal WHERE LinkID = "
         "{guid {11223344-1122-1122-1122-FFFFFFFFFFFF}}";
rs.Open( CRecordset::forwardOnly, strSQL, CRecordset::readOnly );
// Confirm that you found the correct record.
rs.GetFieldValue( "Description", strDescription );
AfxMessageBox( "Found " + strDescription );
				

Note You must add the common language runtime support compiler option (/clr:oldSyntax) in Visual C++ 2005 to successfully compile the previous code sample. To do this, follow these steps:

1. Click Project, and then click ProjectName Properties.
   
   Note ProjectName is a placeholder for the name of the project.
2. Expand Configuration Properties, and then click General.
3. In the right pane, click to select Common Language Runtime Support, Old Syntax (/clr:oldSyntax) in the Common Language Runtime support project settings.
4. Click Apply, and then click OK.

For more information about common language runtime support compiler options, visit the following Microsoft Web site:

Inserting and updating GUID fields by 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 a record using CDaoRecordset method.
rs.AddNew();
   rs.SetFieldValue(_T("MyGUIDField"),varGUIDValue);
rs.Update();
				

The same process applies to updating GUID fields, except that you would use the Edit rather than the AddNew method.

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: You can use the Seek method with GUIDs.

Sample code

// 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 the CDaoTableDef::CreateField method.

Sample code

// 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();