Open DAO recordset from Access using C++ builder 2010

Question

I'm getting quite annoyed by the lack of information about using DAO with C++ on the internetz. Since I have a thing to fix I have to fiddle a bit with it and I'm thinking of jumping out the windows instead :)

The only examples I find are VB6, VB.Net and VBA related. I have tried to apply them to C++ and have the following thing

...    
WideString sqlQuery = WideString(
                "SELECT * "\
                "FROM NODES "\
                "WHERE ID = " + IntToStr(NODE_ID));

        Dao_2k::RecordsetPtr pNewRecord;
        pNewRecord = m_dbDatabase->OpenRecordset(sqlQuery.c_bstr(), OleVariant(RecordsetTypeEnum::dbOpenDynaset));
...

But it just doesn't not want to work. I first tried with just the sql query, then I added the dbOpenDynaset setting and trying others. But execution just halts. I'm thinking there might be more variables needed to the OpenRecordset function in C++ but the IDE have no documentation about it so I'm kinda fumbling in the dark.

The select will actually contain a few joins but I stripped it down to see if that was the issue. But both this simple and the more complex query executes within Access.

Answer 1

I got it to work using a bit of the principles described in VB tutorials and some other sources. Something like this should work.

WideString sqlQuery = WideString(
            "SELECT * FROM NODES "\
            "WHERE ID = " + IntToStr(NODE_ID));

Dao_2k::RecordsetPtr pRecord;
pRecord = m_dbDatabase->OpenRecordset(sqlQuery.c_bstr(), OleVariant(RecordsetTypeEnum::dbOpenDynaset));

Dao_2k::FieldsPtr fs;
fs = pEntryRecord->get_Fields();

Dao_2k::FieldPtr nodeIdField;
nodeIdField = fs->get_Item(OleVariant(0));

Not though that Access is horrible at handleing SQL syntax. JOINS require a strange set of parentheses and I haven't been able to make it do a UNION yet :)

Answer 2

After spending several days searching for a way to use DAO with C++, all the examples I could find where all using Microsoft C++ compiler. However, I wanted something that even MingW can compile. After several searches, I came across a wrapper for com DLLs used by VB6 for C++ called Disphelper. I spent hours trying to figure out how to access the DAO object using this Disphelper but finally, I managed to make it work, and here is the code to open and read an MS Access database (I named it Test.mdb and contains 4 simple columns). I think it will be easy for anyone to convert it to their own database. You can get the files Disphelper.h and Disphelper.c from Disphelper archives.

#include "disphelper.h"
#include <stdio.h>
#include <wchar.h>

int main(void)
{
    dhInitialize(TRUE);
    dhToggleExceptions(TRUE);

    DISPATCH_OBJ(objDBEngine);
    DISPATCH_OBJ(objWORKSpace);
    DISPATCH_OBJ(objDATABase);
    DISPATCH_OBJ(objRECORDSet);

    int bEOF;
    LPWSTR a,b,c;
    int d;

    dhCreateObject(L"DAO.DBEngine.120", NULL, &objDBEngine);
    dhGetValue(L"%o",&objWORKSpace,objDBEngine,L".Workspaces(%d)",0);
    dhGetValue(L"%o",&objDATABase,objWORKSpace,L".OpenDatabase(%s)","C:/Test.mdb");
    dhGetValue(L"%o",&objRECORDSet,objDATABase, L".OpenRecordset(%s,dbOpenDynaset)", "SELECT * FROM Details");

    while (SUCCEEDED(dhGetValue(L"%b", &bEOF, objRECORDSet, L".EOF")) && !bEOF)
    {
          dhGetValue(L"%d", &d,objRECORDSet, L".Fields(%s)", "ID");
          dhGetValue(L"%s", &a,objRECORDSet, L".Fields(%s)", "Name");
          dhGetValue(L"%s", &b,objRECORDSet, L".Fields(%s)", "Surname");
          dhGetValue(L"%s", &c,objRECORDSet, L".Fields(%s)", "DOB");

          printf("ID: %d\nName: %s\nSurname: %s\nDOB: %s\n\n",d,a,b,c);

          dhCallMethod(objRECORDSet, L".MoveNext");
    }

    SAFE_RELEASE(objRECORDSet);
    SAFE_RELEASE(objDATABase);
    SAFE_RELEASE(objWORKSpace);
    SAFE_RELEASE(objDBEngine);

    printf("Press ENTER to exit...\n");
    getchar();

    dhUninitialize(TRUE);
    return 0;
}