none
Query works on MS Access 2016 but not when submitted by Excel VBA RRS feed

  • Question

  • Hello ... Trust everybody is doing well.

    I have a question that has been bugging me for a while and getting me stuck.

    I have a query that runs perfectly well on MS Access 2016, but when I get exactly the same query on VBA and then submit it to an access DB (.accdb), it does not work. Let me better explain it.

    The first test was using "SELECT A, B FROM TABLABLABLA ORDER BY A" (column and table names here are fictitious). This definitely works while being submitted by my Excel VBA program and I can see the results on the excel listview. This means that the recordset returns with full of contents after the open command.

    The second test was using the "SELECT A, B FROM TABLABLABLA WHERE A LIKE '*BIRD*' ORDER BY A". There are indeed rows on this table matching this criteria. I can see them on MS ACCESS when I paste this query on ACCESS SQL mode and run. It works fine there. However, when I replace the former query string with this new one on Excel VBA, using the same program, the same instructions to open connection, recordset as before, the recordset returns empty so that it cannot get into the loop to parse the rows as the recordset.EOF condition is satisfied right away and then the program flow ends.

    By removing the WHERE clause and thus getting back to the former query string, it all works fine.

    Do you have any hints to me on what's going on?

    I shall thank you very much indeed.

    Regards

    Luiz

    Tuesday, February 4, 2020 2:00 AM