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()
{
DAO::_FieldPtr fldNew;
DAO::_FieldPtr fldIdx;
DAO::_TableDefPtr tdfDestTable;
DAO::_IndexPtr idxPtr;
DAO::PropertyPtr pPrp;
DAO::PropertiesPtr pPrps;
try
{
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);
idxPtr = tdfDestTable->CreateIndex(_T("PrimaryKey"));
idxPtr->Primary = VARIANT_TRUE;
fldIdx = idxPtr->CreateField(_T("SerialNo"), DAO::dbText, 12);
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:
fldNew = tdfDestTable->Fields->GetItem(_T("RPM"));
pPrp = fldNew->CreateProperty(_T("Format"), DAO::dbText);
pPrp->put_Value(_variant_t(_T("0.0")));
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));
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
{
pRec = m_CurrDB->OpenRecordset(_bstr_t(szStmt), DAO::dbOpenDynaset);
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
:
DAO::_QueryDefPtr qdfTakeOffView;
Next, you need to create the querydef
:
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:
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.
qdfMyNewView = m_CurrDB->CreateQueryDef(vtMissing, vtMissing);
qdfMyNewView->Name = _T("qdfMyNewView");
qdfMyNewView->SQL = _T("SELECT * FROM EXISTING_TABLE;");
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 QueryDef
s? Well, DAO allows you to access VBA expressions directly in your query. This can be a security risk, so be careful.
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");
LPTSTR szQryBuf = new TCHAR[2048];
COleDateTime curTime;
if (curTime.ParseDateTime(szDatetime))
{
COleDateTimeSpan sixtySecs(0, 0, 0, 60);
curTime -= sixtySecs;
if (-1 != _stprintf_s(szQryBuf, 2048,
szQueryTmpl,
szDatetime,
(LPCTSTR) curTime.Format(_T("%Y-%m-%d %H:%M:%S")),
szDatetime))
{
qdfMyNewView = m_CurrDB->QueryDefs->Item[_bstr_t(_T("qdfMyNewView"))];
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.