Codwiz51's Wiki

RSS

Navigation







Quick Search
»
Advanced Search »

DAO - It's still useful (and fast enough)

RSS
Modified on 2011/04/19 07:39 by codewiz51 Categorized as CPP

Introduction

I recently had reason to start a new project using dao36.dll. Why? Microsoft says you shouldn't use it. 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 of Oracle Express edition are not practical. A file based data system which does not require Admin privileges to install a piece of server software is muched to be desired. I started out using ADO. But I was not able to provide the level of column formatting the client desired. I came across this introductory article. 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. I was never able to figure out how to set a column's Format property using ADOX.

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

It's magic!

Create Database File

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

try
{
    m_CurrDB = m_DBE->CreateDatabase(_bstr_t(szMDFFile),
         _bstr_t(DAO::dbLangGeneral),_variant_t(DAO::dbVersion40));
}

catch(_com_error &amp;e)
{
    DAO::ErrorsPtr pErrs = m_DBE->Errors;
    DAO::ErrorPtr pErr;
    long count = pErrs->Count;
    if (count > 0)
    {
        pErr = pErrs->Item&#0091;0&#0093;;
        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);
}

That's all there is to creating an MDB file.

Create Table Definition

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 type wizardry. It seems to be acceptable to modify directly accessible properties, e.g. AllowZeroLength, but not properties accessed through a collection, e.g. "Format".

Here is some simple code to add a table def to our previously created database.

BOOL CMainDlg::InitMDBFile()
{
    DAO::_FieldPtr  fldNew;
    DAO::_FieldPtr  fldIdx;
    DAO::_TableDefPtr tdfDestTable;
    DAO::_IndexPtr  idxPtr;
    DAO::PropertyPtr pPrp;
    DAO::PropertiesPtr pPrps;
    CComVariant  v;

    try
    {
        // Append table tbEngine
        tdfDestTable = m_CurrDB->CreateTableDef(_T("tbEngine"));
        fldNew = tdfDestTable->CreateField(_T("SerialNo"), DAO::dbText, 12);
        tdfDestTable->Fields->Append(fldNew);
        fldNew = tdfDestTable->CreateField(_T("PartNo"), DAO::dbText, 12);
        fldNew->put_AllowZeroLength(VARIANT_TRUE);
        tdfDestTable->Fields->Append( fldNew);
        fldNew = tdfDestTable->CreateField(_T("OPT_FLAGS"), DAO::dbInteger, 0);
        tdfDestTable->Fields->Append( fldNew);
        fldNew = tdfDestTable->CreateField(_T("STATUS"), DAO::dbInteger, 0);
        tdfDestTable->Fields->Append( fldNew);
        fldNew = tdfDestTable->CreateField(_T("LOCATION"), DAO::dbInteger, 0);
        tdfDestTable->Fields->Append( fldNew);
        fldNew = tdfDestTable->CreateField(_T("RFID"), DAO::dbText, 10);
        fldNew->put_AllowZeroLength(VARIANT_TRUE);
        tdfDestTable->Fields->Append( fldNew);
        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 posting. So far, so good. It's as easy as pie, don't you think?

Create/Add Properties

Here's how to add properties to a column in 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: (The variable declarations are given in my previous post.)

   // 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.

Execute Action Query

Executing an SQL statement that does not return data is quite simple in ADO. 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 &amp;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);
}

Open A Query As A RecordSet

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
{
    pRec = m_CurrDB->OpenRecordset(_bstr_t(szSQLStmt), DAO::dbOpenDynaset, DAO::dbReadOnly, DAO::dbReadOnly);
    vRecsAffected = m_CurrDB->RecordsAffected;
}

catch(_com_error &amp;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);
}

Create A QueryDef

Creating a querydef is quite simple using DAO as a COM object. If you've been following along with my other DAO posts, you can integrate this code into your project in just a few minutes.

First, you need to 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.

Another way to append a query is as follows:

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

As you can see, if you create an empty QueryDef, you have to append it to the QueryDefs collection.

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 szQryFmt = 
    _T("SELECT DATA.SerialNo, DATA2.RFID, DATA.CURDATETIME,")
    _T(" DateDiff(\"s\",\"%s\",&#0091;CURDATETIME&#0093;) AS RelativeSampleTime,")
    _T(" DateDiff(\"s\",\"%s\",&#0091;CURDATETIME&#0093;) AS NormalizedSampleTime,
    _T(" FROM DATA INNER JOIN DATA2 ON DATA.SerialNo = DATA2.SerialNo")
    _T(" WHERE (((DateDiff(\"s\",\"%s\",&#0091;CURDATETIME&#0093;)) Between 60 And -60))")
    _T(" ORDER BY Location, CURDATETIME");

// Create the buffer to hold the formatted sql string
LPTSTR szQryBuf = new TCHAR&#0091;2048&#0093;;
// 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,
                       szQryFmt,
                       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&#0091;_bstr_t(_T("qdfMyNewView"))&#0093;;
     // 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);
 }
...

Export Data

Have you ever wanted to Export Data using DAO? Did you want to perform the task with only a couple of lines of code? Then check out this really simple method:
// szISAMType is an installed ISAM type
// szPath is the path where the exported file will be created
//        without a trailing '\'
// szFileName is an unqualified file name, without an extension
//        I have found the file name should start with a letter
//        or the first character is replaced by a '_' character.
// szExportBuffer is a string buffer you've already created.
// MyTarget is the name of a tabledef or querydef
LPCTSTR szISAMType = _T("Excel 4.0");
LPCTSTR szExportQuery =
        _T("SELECT * INTO [%s;Database=%s].[%s] FROM MyTarget");
_stprintf_s(szExportBuffer, 1024, szISAMType, szExportQuery, szPath, szFilename);
m_CurrDB->Execute(_bstr_t(szExportBuffer));
Nice! Next question: where do we get the list of installed ISAM formats? Answer: Simple. The registry. Look under the key HKLM\SOFTWARE\Microsoft\Jet\4.0\ISAM Formats.

There will be a set of keys for supported ISAM types, such as Lotus WK1, or dBase III, etc. Be sure you copy the exact name of the ISAM. Close won't do, it has to be exact. Or you will get no export. Not all ISAM types support exports.

Call A VBA Function From a Query

This demonstrates calling the VBA function DateDiff. Pretty cool, eh?

Have you ever wanted to Export Data using DAO? Did you want to perform the task with only a couple of lines of code? Then check out this really simple method:
// szISAMType is an installed ISAM type
// szPath is the path where the exported file will be created
//        without a trailing '\'
// szFileName is an unqualified file name, without an extension

//        I have found the file name should start with a letter
//        or the first character is replaced by a '_' character.
// szExportBuffer is a string buffer you've already created.
// MyTarget is the name of a tabledef or querydef
LPCTSTR szISAMType = _T("Excel 4.0");
LPCTSTR szExportQuery =
        _T("SELECT * INTO [%s;Database=%s].[%s] FROM MyTarget");
_stprintf_s(szExportBuffer, 1024, szISAMType, szExportQuery, szPath, szFilename);
m_CurrDB->Execute(_bstr_t(szExportBuffer));
Nice! Next question: where do we get the list of installed ISAM formats? Answer: Simple. The registry. Look under the key HKLM\SOFTWARE\Microsoft\Jet\4.0\ISAM Formats.

There will be a set of keys for supported ISAM types, such as Lotus WK1, or dBase III, etc. Be sure you copy the exact name of the ISAM. Close won't do, it has to be exact. Or you will get no export. Not all ISAM types support exports.

KB950749

Microsoft Windows Update KB950749 changes the way we interface with DAO. The COM method calls no longer accept VT_INT paramters (e.g. _variant_t(15) is cast to a variant type VT_INT.) codewiz51, 2008/07/24 21:28

Microsoft has published KB article 955223 regarding issues caused by KB950749.

I did a little more work on the problems I have found after applying KB950749.

After KB950749 was installed by Windows Update, we started getting exceptions from some of our old DAO legacy applications.

This particular type of line is used throughout our code:

fldNew = tdfDestTable->CreateField(_T("MyField5"), DAO::dbText, 12);

Out of the blue, the above line of code started throwing exceptions with a DAO error of 3421 We traced it back to the installation of KB950749 which is a fix for some MSJet 4.0 security issues. Uninstalling KB950749 enables the legacy applications to work. However, this is not desirable, due to the fixes contained in KB950749.

After a little experimenting, here's what works and what doesn't. I doubt this affects many developers.

fldNew = tdfDestTable->CreateField(_T("MyField0"), (short) DAO::dbText, (short) 12); // works
fldNew = tdfDestTable->CreateField(_T("MyField1"), (long) DAO::dbText, (long) 12); // works
fldNew = tdfDestTable->CreateField(_T("MyField2"), CComVariant(DAO::dbText), CComVariant(12)); // works
fldNew = tdfDestTable->CreateField(_T("MyField3"), _variant_t(DAO::dbText), _variant_t(12));  // Exception DAO error 3421
fldNew = tdfDestTable->CreateField(_T("MyField4"), (int) DAO::dbText, (int) 12); // Exception DAO error 3421
// The following line used to work before KB950749 was installed.
// It is representative of what we have in production
fldNew = tdfDestTable->CreateField(_T("MyField5"), DAO::dbText, 12); // Exception DAO error 3421

The crux of the problem lies in the code generated by #import. Here is a snippet of the code created by the compiler:

inline _FieldPtr _TableDef::CreateField ( const _variant_t &amp; Name, const _variant_t &amp; Type, const _variant_t &amp; Size )
{
struct _Field * _result = 0;
HRESULT _hr = raw_CreateField(Name, Type, Size, &amp;_result);
if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));
return _FieldPtr(_result, false);
}

The problem is two fold. My code calls _TableDef::CreateField which assigns the constant numeric values (e.g. 12, or DAO::dbText) to a _variant_t of type VT_INT. This function then calls HRESULT _hr = raw_CreateField(Name, Type, Size, &_result); The method call raw_CreateField does not appear to accept VARIANTS of type VT_INT after the update is installed. This seems to be a change in behavior from the code prior to the update.

The good news is that if you are using MFC or VBA, you will never see this problem. MFC has some wrapper functions that correctly cast the numeric parameters using COleVariant. Luckily, VBA casts numbers to a long so you never see the issue. It's only if you are using #import and Visual C++. I use this method because I mostly program apps using WTL. (To be honest, I think I am the only person in the world that uses this method. *sigh*)

At first glance, it seems a little strange that raw_CreateField will take a long, a short or a CCOmVariant, but not an int or _variant_t. This is caused by the way _variant_t casts numbers. CComVariant defaults to longs (VT_I4), whereas _variant_t defaults to int for numbers not explicitly cast to a type.

I also found the same type of problem with CreateProperty. For the time being, you can cast a number to a long, short or use CComVariant for numeric parameters, e.g. DAO::dbText.

The moral is, if you are going to use somewhat undocumented methods (like I did), be sure to cast your parameters so the compiler doesn't have to make assumptions. It's not that I or Microsoft flubbed anything. I used sloppy coding (unknowingly) and Microsoft changed a behavior on a parameter that is documented as being a short (VT_I2). However the parameter previously accepted an int (VT_INT) and continues to accept a long (VT_I4). The developers that wrote MFC certainly did the correct conversions with their wrapper calls, I've studied their code and am making appropriate changes to my source code.

ScrewTurn Wiki version 3.0.5.600. Some of the icons created by FamFamFam.