none
DirectDAO sample not useable - fail of standard SQL commands in MS Access 2000-2010 databases

    Question

  • Hello,

    Referring to this DirectDAO sample http://msdn.microsoft.com/en-us/library/office/ff965871.aspx#DataProgrammingWithAccess2010_DirectDAOExample I am facing an issue with standard SQL statements in DirectDAO that is beyond my understanding.

    E.g. the compiler prompts this error message when I try to "cout" the RecordTable. Even if at the same time the database could be initialized successfully, the SQL commands worked well and the correct RecordCount of the RecordTable was available. Even trying to iterate through the Record fields prompts the following error:

    First-chance exception at 0x752cb9bc in DirectDAO.exe: Microsoft C++ exception: _com_error at memory location 0x0044f310..
    Unhandled exception at 0x752cb9bc in DirectDAO.exe: Microsoft C++ exception: _com_error at memory location 0x0044f310..
    The program '[3672] DirectDAO.exe: Native' has exited with code -529697949 (0xe06d7363).

    <comutil.h>
    
    //////////////// Error checking routines//////////////////
    
    namespace _com_util {
        inline void CheckError(HRESULT hr) throw(...)
        {
            if (FAILED(hr)) {
                _com_issue_error(hr);
            }
        }

    In case of different database versions (2003 vs. 2010) the compiler also stated for Access2010 that the "DatabasePtr(_result, false)" was invalid, while it works for Access 2003.

    inline DatabasePtr _DBEngine::OpenDatabase ( _bstr_t Name, const _variant_t & Options, const _variant_t & ReadOnly, const _variant_t & Connect ) {
        struct Database * _result = 0;
        HRESULT _hr = raw_OpenDatabase(Name, Options, ReadOnly, Connect, &_result);
        if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));
        return DatabasePtr(_result, false);
    }

    Another error is caused by the SQL statement "_bstr_t query = "SELECT * FROM Customers";"

    <strlen.asm>
    
    main_loop:
      mov     eax,dword ptr [ecx]     ; read 4 bytes

    Indeed there seems to be a different behaviour (error handling) depending on what kind of database type (Access2000-2003 or Access2007-2010) is used. But that's just my guessing game.

    The last two days I was trying to find out why some "standard" SQL statements work for one database but not for another of the same version, or why some staments work for databases of Access2007-2010 but not for Access 2000-2003 and vice versa.

    But finally to no avail, and I really don't have any other ideas of what I need to do to make this sample work off SQL commands as commonly expected, unaffected what version of MS Access database is provided.

    Hope you can help me out.

    Thank you

    Konrad

    Tuesday, September 18, 2012 1:51 PM

Answers

  • Hello Kirk,

    thank you for your feedback. I also think that this was not the right place for my problem.

    Probably because of the string "SQL" in my headline the moderator from the "Visual Studio VC++" forum saw a link  to the "MS Access Developer" forum and therefore has moved my request here.

    1. I did not try out the VBA code yet as I am programming VC++. Maybe I will give it a try when the CPP coding is done.

    2. As Albert advised I already used the Access SQL Query Designer without problems. Only my CPP code came up with an error.

    3. Indeed the problem is caused by empty fields in the DAO record set.  The SQL statements have always worked well, only my interpretation of the appearing problems was incorrect.

    Readout an empty field in a DAO record set needs to be handled by an exception. I will pass to post VC++ code here in the "MS Access Developer" forum.

    Thanks again for your help.

    Best regards

    Konrad

    • Marked as answer by MailKonrad Saturday, October 27, 2012 9:50 AM
    Saturday, October 27, 2012 9:49 AM

All replies

  • Hello,

    Thanks for your post.

    According to your description, Access for Developers is a more appropriate forum for this issue. This thread is moved to that forum to get better support. Your understanding will be appreciated.

    Regards,


    Damon Zheng [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, September 19, 2012 7:00 AM
  • Indeed, Access 2007 and later may use a different format and lightly different database engine (ACE instead of JET) which has been introduced since Office 2007.   Try MFC ODBC, as example, with C++,  for Access 2003 and before.



    Note that JET has not been designed for near real-time processing (such as storing a record each 1/1000 of a second). I suggest to try MS SQL Server if this is your scenario.

    Wednesday, September 19, 2012 12:41 PM
  • There several things here.

    I think first up, is are you able to open the database before any errors?

    There is most certainly a different provider required for the newer format (2007 and later) datbases. So, for "mdb" file formats, then:

    DAO.DBEngine.36

    And for accDB, then:

    DAO.DBEngine.120

    So are you saying that you are able to create an instance of the DAO object, and THEN open the database in both cases?

    And THEN some sql statements don't work?

    I mean, if the open database command don't work, the of course it not really the SQL command(s) that are failing here.

    Also, note that using the new "ACE" provider, you should be able to open EITHER mdb for accDB.

    With the DAO 3.6 provider, then ONLY mdb can be opened.

    If you are able to open the accDB, and the SQL fails (again, I STREES you  confirm that open commands are successful), then I see little if any reason why the SQL command(s) should not work.

    If the open of the file using ACE (DBEngine.120) works in both cases, then we now down to the database.

    If the open is successfully, then I would do two things:

    First, consider opening the database with MS Access and then do a compact + repair. It possible some index or some damage has occurred, and the C+R will re-build the indexes.

    Next up, since the database is opened with MS Access, can you cut + paste in your SQL directly into the query builder and see if it runs.

    So, open up the query builder, switch to SQL view, and then paste in your SQL that was not working. Does it work?

    Remember, it likely of little use to attempt code here if you cannot run such SQL in the query builder.  And as noted, if your open command not working, then again what follows is of little use.

    Last but not least:

    Are you running 32 bit inprocess or x64 inprocess here? If you are running x64 (and I don't think you are), then you MUST use the x64 bit edition of the database engine, and this means you have to install the x64 bit ACE provider.

    If you are 100% using only mdb format, then the DAO36 should suffice, but for accDB, then DAO120 is required. If you are going to use "either" type of files, then I would stick to using the one DAO120 as it should work for both types of formats.

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Wednesday, September 19, 2012 10:42 PM
  • Hello all,

    Thanks for your replies and support.

    I am using the file Northwind 2007.accdb with DirectDAO from the ACESampleCode.zip of 2007:

    http://archive.msdn.microsoft.com/ac2007DevSolutions/Release/ProjectReleases.aspx?ReleaseId=1434

    The updated sample codes from 2010 always show an “#import”-error because the file “C:/…/ACESampleCode/Native/Debug/ACEDAO.tlh” is missing. This is folder and file are only created automatically by the sample codes of the version from 2007 above. I do not know how to fix this for the newer versions:

    http://code.msdn.microsoft.com/odcac2010ta/Release/ProjectReleases.aspx?ReleaseId=4908

    I think first up is, are you able to open the database before any errors?

    Yes.

    So are you saying that you are able to create an instance of the DAO object, and THEN open the database in both cases?

    Yes, the problem seems to be the iteration through the record set.

    If the open of the file using ACE (DBEngine.120) works in both cases, then we now down to the database.

    I have rebuilt a new test database now with Access 2010. It only contains text and integer values. But problems are still not completely solved or understood.

    So, open up the query builder, switch to SQL view, and then paste in your SQL that was not working. Does it work?

    All SQL commands that caused a problem within the code do their work as expected running within the Access SQL Query Designer.

    Are you running 32 bit inprocess or x64 inprocess here?

    The machine is running with "Windows 7 (x64)" and "Office 2010 (x86)".

    If you are 100% using only mdb format, then the DAO36 should suffice, but for accDB, then DAO120 is required.

    Later on the program shall work with local databases of file *.accdb only.

    In the meantime it seems I could identify a reason why the code comes up with errors in the sample database and sometimes also in my own test database.

    1. issue: Empty fields (in sample database and my own test database)

    Some fields are left blank like "Customers.[E-mail Address]" in the sample database and also some fields in my own test database are empty. So it may need a special handling of empty fields?

    Regards

    Konrad

    Thursday, September 20, 2012 12:42 PM
  • Hey Konrad,

    Looking at this, I'm not sure that the Access forum is the correct home for your question, but I can at least take a stab at answering what I can from an Access or basic troubleshooting standpoint.  Just had a couple of questions based on what you've posted so far:

    1.  You mentioned this link in your original post:

    http://msdn.microsoft.com/en-us/library/office/ff965871.aspx#DataProgrammingWithAccess2010_DirectDAOExample

    Do you see the same issue when running the VBA code example outlined later in the same article?

    2.  I see that the sample uses the Northwind database.  Have you tried with another database file?  Can you create a very simple sample to connect to, maybe with just a single table with simple information?  I'm thinking a table with contact first and last names for example. 

    3.  I noticed the comment at the end of your most recent post where you say that some of the fields contain nulls.  Do you see any difference in behavior if you add values in place of those nulls?  If things work after overwriting the nulls, do you think there's any way you could adjust your SQL statement to exclude the records that include null values? 

    Hopefully something there can give us a clue to what's going on in your case. 

    Thanks, and good luck!

    Kirk
    Microsoft Online Community Support


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Thursday, October 04, 2012 6:49 PM
  • Hello Kirk,

    thank you for your feedback. I also think that this was not the right place for my problem.

    Probably because of the string "SQL" in my headline the moderator from the "Visual Studio VC++" forum saw a link  to the "MS Access Developer" forum and therefore has moved my request here.

    1. I did not try out the VBA code yet as I am programming VC++. Maybe I will give it a try when the CPP coding is done.

    2. As Albert advised I already used the Access SQL Query Designer without problems. Only my CPP code came up with an error.

    3. Indeed the problem is caused by empty fields in the DAO record set.  The SQL statements have always worked well, only my interpretation of the appearing problems was incorrect.

    Readout an empty field in a DAO record set needs to be handled by an exception. I will pass to post VC++ code here in the "MS Access Developer" forum.

    Thanks again for your help.

    Best regards

    Konrad

    • Marked as answer by MailKonrad Saturday, October 27, 2012 9:50 AM
    Saturday, October 27, 2012 9:49 AM