Using Data Access Objects (DAO) with WTL

Introduction

Microsoft tells us the DAO model is obsolete. DAO360.dll is the end of the line. No new projects should be started with DAO.

However, there are certain environments where DAO is still an excellent tool. If your customer works in a "non-admin" environment, you do not have a lot of options for creating local databases. ADO may be a viable option. But, that also depends on the needs of your client. Creating a QueryDef that is visible to MS Access is problematic with ADO. If your client will be using MS Access for data analysis, then DAO is still your friend.

We recently needed to rapidly extract some data and place it into a Microsoft Access database used by engineers. The project was written in WTL, and I blindly started out with ADO and got the project up and running in a day. A couple of days later, the engineers came back to me with a couple of "simple" requests. The first request was they didn't like the column widths when they opened the tables in Access. The second request was for specialized formatting of the data, without changing the underlying data.

I diligently trotted off, thinking this was going to take half an hour to complete. I couldn't have been more wrong! Have you ever tried to set the width of a column in a table or datasheet view using ADO or ADOX? I dug through the documents, and couldn't find anything in ADOX or ADO about the visible column width. I naively started to obtain a pointer to the "Format" property for a column in ADOX. It didn't work. After several hours, I began to think maybe DAO wasn't as dead as Microsoft would have us believe? I knew how to do all this stuff in DAO, but ADO didn't seem to offer a very good path to meet my customer's desires.

Then, I ran across "How to use DAO in Visual C++ without MFC" by Yuriy Tkachenko. I decided the heck with ADO and the rules Microsoft has given us to live by. After importing the DAO DLL and a couple of hours of work, I had a working product that filled my client's needs. The amazing thing was, the program ran faster. A lot faster than my ADO product.

Background

My client wanted their data in an Access database (MDB file format). Why? Portability. Their desktops are locked down, and installing SQL Server Personal Edition or Oracle Express Edition is not practical. A file based data system which does not require Admin privileges to install a piece of server software is much to be desired. I started out using ADO. But, I was not able to provide the level of column formatting the client desired.

This article is an extension of Yuri's article. I go into much greater detail regarding the use of DAO. There is no demo code, only code snippets that demonstrate how to create a database file using DAO, create tables, and index the tables. I apply column properties that are accessible using DAO, but impossible to access using ADO or ADOX. I also use code and patterns common to the ATL and WTL programmer, in the code snippets.

DAO - It's still useful

Basically, you do the same sorts of things with DAO that you perform with ADO. Only, you find all the nice Access widgets are accessible.

You'll need to include this import statement. I typically place this import in my stdafx.h file. Your mileage may vary.

#import <C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll> 
                                              rename("EOF", "EndOfFile")

Creating a DBEngine object in WTL

Once you've imported DAO36.dll into your project, you need to create an instance of DBEngine.

You'll need to create some variables in your class header for the DAO COM pointers:

private:
    CComPtr<DAO::_dbengine> m_DBE;
    DAO::WorkspacePtr m_Workspace;
    DAO::DatabasePtr m_CurrDB;

To create the instance, you can place these statements in your class constructor:

try
{
    m_DBE.CoCreateInstance(__uuidof(DAO::DBEngine));
}
catch(_com_error &e)
{
    ::MessageBox(m_hWnd, (LPCTSTR)e.Description(), 
                (LPCTSTR)e.Source(), MB_ICONSTOP);
    m_szSQLState = e.Error();
    _tcscpy_s(m_szSQLErrMsg, iERR_MESG_CHARS, (LPCTSTR)e.Description());
}

Use DAO to create a database file

After you have created an instance of the DBEngine component, you are ready to create a database file (MDB).

This snippet also introduces access to the DAO error collection in the catch block of the code:

try
{
    m_CurrDB = m_DBE->CreateDatabase(_bstr_t(szMDFFilePath), 
               _bstr_t(DAO::dbLangGeneral), _variant_t(DAO::dbVersion40));
}
catch(_com_error &e)
{
    DAO::ErrorsPtr pErrs = m_DBE->Errors;
    DAO::ErrorPtr pErr;
    long count = pErrs->Count;
    if (count > 0)
    {
        pErr = pErrs->Item[0];
        m_strMsg.Format(_T("Error: %d, Description: %s"), 
                        pErr->Number, (LPCTSTR) pErr->Description);
        MessageBox((LPCTSTR)m_strMsg, (LPCTSTR)e.Source(), MB_ICONSTOP);
    }
    else
        MessageBox((LPCTSTR)e.Description(), (LPCTSTR)e.Source(), MB_ICONSTOP);
}

Creating a new TableDef with DAO

The previous section demonstrated creating a database file using DAO. Now, we will add a table definition. I want to point out that you are better off simply designing a table definition and appending it to the TableDefs collection before you attempt to modify field properties, add indexes, or try any other Access widget wizardry. It seems to be acceptable to modify directly accessible properties, e.g., AllowZeroLength before the tabledef is saved. The properties accessed through a collection, e.g., "Format", do not seem to be accessible until after the tabledef is saved.

Here is some simple code to add a tabledef to our previously created database:

BOOL CMainDlg::InitMDBFile()
{
    // Declare variables
    DAO::_FieldPtr fldNew;
    DAO::_FieldPtr fldIdx;
    DAO::_TableDefPtr tdfDestTable;
    DAO::_IndexPtr idxPtr;
    DAO::PropertyPtr pPrp;
    DAO::PropertiesPtr pPrps;
    try
    {
        // Create tabledef tbEngine
        tdfDestTable = m_CurrDB->CreateTableDef(_T("tbEngine"));
        // Add a text field
        fldNew = tdfDestTable->CreateField(_T("SerialNo"), DAO::dbText, 12);
        tdfDestTable->Fields->Append(fldNew);
        // Add a text field
        fldNew = tdfDestTable->CreateField(_T("PartNo"), DAO::dbText, 12);
        // Set zero length allowed property
        fldNew->put_AllowZeroLength(VARIANT_TRUE);
        tdfDestTable->Fields->Append( fldNew);
        // Add an integer
        fldNew = tdfDestTable->CreateField(_T("OPT_FLAGS"), DAO::dbInteger, 0);
        tdfDestTable->Fields->Append( fldNew);
        // Add an integer
        fldNew = tdfDestTable->CreateField(_T("STATUS"), DAO::dbInteger, 0);
        tdfDestTable->Fields->Append( fldNew);
        // Add an integer
        fldNew = tdfDestTable->CreateField(_T("LOCATION"), DAO::dbInteger, 0);
        tdfDestTable->Fields->Append( fldNew);
        // Add a text field
        fldNew = tdfDestTable->CreateField(_T("RFID"), DAO::dbText, 10);
        // Set zero length allowed property
        fldNew->put_AllowZeroLength(VARIANT_TRUE);
        tdfDestTable->Fields->Append( fldNew);
        // Append the tabledef before we start modifying DAO widgets
        m_CurrDB->TableDefs->Append(tdfDestTable);

        //Now, we can add a primary key:

        idxPtr = tdfDestTable->CreateIndex(_T("PrimaryKey"));
        idxPtr->Primary = VARIANT_TRUE;
        fldIdx = idxPtr->CreateField(_T("SerialNo"), DAO::dbText, 12);
        // This is a bit tricky. You have to cast the _variant_t Fields
        // to a collection pointer called IndexFieldsPtr. The index
        // fields collection is passed back to the calling code as a variant.
        DAO::IndexFieldsPtr pFlds = idxPtr->Fields;
        pFlds->Append(fldIdx);
        tdfDestTable->Indexes->Append(idxPtr);
        m_CurrDB->Close();
    }

The catch block is the same as the previous section. So far, so good.

Please pay attention to one very important point. You have to create the index fields a second time, in the index that you will append to the tabledef you just created. Note 1: You cannot use the field object you created to append to the tabledef to append to the index. I know. I tried. It didn't work. Note 2: DAO::dbNumeric and DAO::dbDecimal don't work. Sorry. Don't blame me.

Adding properties to a column with DAO

This code demonstrates adding columns to a table. Assume you have added a table definition to the TableDefs collection. You then need to get a pointer to the column you want to modify and then create the property. That's right. Even though we know Access has a Format property, we still have to create it for our column:

    // We have to create the format property, since it doesn't yet exist
    // See KB 190522 in MSDN
    // http://support.microsoft.com/kb/190522
    fldNew = tdfDestTable->Fields->GetItem(_T("RPM"));
    pPrp = fldNew->CreateProperty(_T("Format"), DAO::dbText);
    pPrp->put_Value(_variant_t(_T("0.0")));
    // Append the property format to the properties collection
    fldNew->Properties->Append(pPrp);

    fldNew = tdfDestTable->Fields->GetItem(_T("CURDATETIME"));
    pPrp = fldNew->CreateProperty(_T("ColumnWidth"), DAO::dbInteger);
    pPrp->Value = _variant_t((long)(1.5 * 1440));
    // Append the property format to the properties collection
    fldNew->Properties->Append(pPrp);

That's about it for creating tables. Next, we'll look at executing simple queries.

Executing an SQL action query with DAO

Executing an SQL statement that does not return data is simple. Building on the last few DAO posts, the following is how you can execute an SQL statement that does not return any data (i.e., an action query):

try
{
    m_CurrDB->Execute(_bstr_t(szStmt));
    vRecsAffected = m_CurrDB->RecordsAffected;
}

catch(_com_error &e)
{
    DAO::ErrorsPtr pErrs = m_DBE->GetErrors();
    long nCount = pErrs->Count;
    if (nCount > 0)
    {
        DAO::ErrorPtr pErr = pErrs->GetItem(0);
        m_szSQLState = pErr->Number;
        _tcscpy_s(m_szSQLErrMsg, iERR_MESG_CHARS, 
                 (LPCTSTR)pErr->Description);
    }
    else
        ::MessageBox(parent_hWnd, (LPCTSTR)e.Description(), 
                    (LPCTSTR)e.Source(), MB_ICONSTOP);
}

Executing an SQL query that returns data with DAO

This is a quick example of how to execute a query that returns data using DAO. One quick note. This example opens a recordset, which will need to be closed somewhere in your code. You don't want to leave a dangling recordset pointer!

DAO::RecordsetPtr pRec = NULL;
try
{
    // This statement will open a recordset for read-write.
    // Be sure to check the Updateable property before attempting
    // to update the returned recordset.
    pRec = m_CurrDB->OpenRecordset(_bstr_t(szStmt), DAO::dbOpenDynaset);
    // The following will open the recordset as read only
    // pRec = m_CurrDB->OpenRecordset(_bstr_t(szStmt), DAO::dbOpenDynaset, DAO::dbReadOnly);
    vRecsAffected = m_CurrDB->RecordsAffected;
}

catch(_com_error &e)
{
    DAO::ErrorsPtr pErrs = m_DBE->GetErrors();
    long nCount = pErrs->Count;
    if (nCount > 0)
    {
        DAO::ErrorPtr pErr = pErrs->GetItem(0);
        m_szSQLState = pErr->Number;
        _tcscpy_s(m_szSQLErrMsg, iERR_MESG_CHARS, 
                 (LPCTSTR)pErr->Description);
    }
    else
        ::MessageBox(parent_hWnd, (LPCTSTR)e.Description(), 
                    (LPCTSTR)e.Source(), MB_ICONSTOP);
}

Creating a QueryDef with DAO

There are two ways to create a QueryDef using DAO. You have to be careful, because the first method will automatically append the QueryDef to the QueryDefs collection. Later, if you attempt to execute an append operation, you'll receive an invalid operation exception.

First method: Declare a pointer to a new querydef:

// Smart pointer defined when DAO DLL is imported
DAO::_QueryDefPtr        qdfTakeOffView;

Next, you need to create the querydef:

// The current database pointer exposes the CreateQueryDef method
qdfMyNewView = m_CurrDB->CreateQueryDef(_T("qdfMyNewView"),
                                        _T("SELECT * FROM EXISTING_TABLE;"));

That is all there is to creating a new QueryDef. One thing you need to know, however: CreateQueryDef, when called in this manner, automatically appends the querydef to the QueryDefs collection. So, don't try to do something like this:

// Don't do this if you created a named QueryDef!
m_CurrDB->QueryDefs->Append(qdfMyNewView);

If you try to append an existing query to the collection, you will get an "Invalid Operation" exception.

Second method: You may create an unnamed querydef and then modify the properties.

// Create an empty QueryDef object
qdfMyNewView = m_CurrDB->CreateQueryDef(vtMissing, vtMissing);
// Assign the name property
qdfMyNewView->Name = _T("qdfMyNewView");
// Assign the sql string
qdfMyNewView->SQL = _T("SELECT * FROM EXISTING_TABLE;");
// It is OK to append this query to the QueryDefs collection
m_CurrDB->QueryDefs->Append(qdfMyNewView);

If you create an empty QueryDef, you have to append it to the QueryDefs collection.

Advantages of QueryDefs

What are the advantages of creating QueryDefs? Well, DAO allows you to access VBA expressions directly in your query. This can be a security risk, so be careful.

// Define the unformatted sql string
LPCTSTR szQueryTmpl = 
    _T("SELECT DATA.SerialNo, DATA2.Rfid, DATA.CurrentDT,")
    _T(" DateDiff(\"s\",\"%s\",[CurrentDT]) AS RelativeSampleTime,")
    _T(" DateDiff(\"s\",\"%s\",[CurrentDT]) AS NormalizedSampleTime,
    _T(" FROM DATA INNER JOIN DATA2 ON DATA.SerialNo = DATA2.SerialNo")
    _T(" WHERE (((DateDiff(\"m\",\"%s\",[CurrentDT])) Between 60 And -60))")
    _T(" ORDER BY Location, CurrentDT");

// Create the buffer to hold the formatted sql string
LPTSTR szQryBuf = new TCHAR[2048];
// Just some time parsing stuff
// You have to include atlcomtime.h to get this class
COleDateTime curTime;
if (curTime.ParseDateTime(szDatetime))
{
 COleDateTimeSpan sixtySecs(0, 0, 0, 60);
 curTime -= sixtySecs;
 // Format the query string with our time values
 if (-1 != _stprintf_s(szQryBuf, 2048,
                       szQueryTmpl,
                       szDatetime,
                       (LPCTSTR) curTime.Format(_T("%Y-%m-%d %H:%M:%S")),
                       szDatetime))
 {
     // Obtain an existing query using QueryDefs and the exposed Item object
     qdfMyNewView = m_CurrDB->QueryDefs->Item[_bstr_t(_T("qdfMyNewView"))];
     // Assign the new string to the QueryDef
     // This automatically persists the query string, it's
     // saved in the MDB file.
     qdfMyNewView->SQL = _bstr_t(szQryBuf);
 }
...

As you can see in this example, a VBA function DateDiff is called directly in the query. Pretty cool, eh?

Summary

Using DAO as a COM object is simple and effective. Even though DAO is obsolete, it can still come in handy in certain situations.