connecting MS Access in Qt 5.15

dan1973

hi,
i am trying to connect to MS Access Database using MS ODBC Driver and DSN. i created DSN in ODBC Data sources for both *.mdb and *.accdb. i am using Qt 5.15.2. but unable to connect. it is not opening. it is giving the following error:
"[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified QODBC3: Unable to connect"

my source code:


    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    db.setDatabaseName("Driver={Microsoft Access Driver (*.mdb)};DSN='dsn3';DBQ=C:\\LCA_MC.mdb");
//    db.setDatabaseName("Driver={Microsoft Access Driver (*.mdb, *.accdb)};FIL={MS Access};DSN='dsn1';DBQ=C:\\LCA_MC.mdb");

    if(db.open())
      qDebug() << "oK";
    else
      qDebug() << db.lastError().text();

Please let me know any solution to this.
Thanking you in advance....
-Dan

JonB

@dan1973
This is an ODBC connection issue, not Qt. Start by checking exactly what exact connection string is needed and works, e.g.

How do I fix data source name not found and no default driver specified?

Image result for [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified QODBC3: Unable to connect

You can check the drivers which are installed on your system by going to the ODBC Data Source Administrator. To open it, press ⊞ Win + R , and type in: odbcad32.exe . Then check the tab Drivers for installed drivers. The Name column indicates the exact name you should use in your connection string or DSN.26 Oct 2019

or maybe https://stackoverflow.com/questions/2702739/simplest-way-to-test-odbc-on-windows helps, I don't know.

dan1973

@JonB i tried reinstalling MS Office - MS Access 2007 also. after restarting the pc, still same problem. whether, you also face this problem?

JonB

@dan1973
Reinstalling Office is not the issue. I said you need to verify what ODBC connection string is needed/works external to your Qt program.

No I don't face such a problem as I don't use Windows for programming. When I have in the past you have to get the ODBC connection string correct.

dan1973

@JonB
Following is the output i get in excel when i run the prepared dqy file(see dqy file also below)
Excel output:

dqy file:

XLODBC
1
Driver={Microsoft Access Driver (*.mdb)};DSN='ms32';DBQ=C:\\LCA_MC.mdb;
SELECT BusName FROM BusInfo

I then used the Driver string in my code still the same problem.

    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    db.setDatabaseName("Driver={Microsoft Access Driver (*.mdb)};DSN='ms32';DBQ=C:\\LCA_MC.mdb;");

    if(db.open())
      qDebug() << "oK";
    else
      qDebug() << db.lastError().text();

JonB

@dan1973
I know nothing about this, but the examples at https://www.connectionstrings.com/access-2007/ do not pass any DSN=... argument. Do you need that, can it be removed?

dan1973

i removed the "DSN='ms32'" string from Driver string. still the same error. But it executes the dqy file into excel output.

dan1973

@JonB We normally connect the Database "LCA_MC.mdb" using DSN name (Data Source Name)

JonB

@dan1973
There are loads of Google hits for "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified .

I think you are supposed to go into ODBC Data Source Administrator (32-bit? 64-bit? I think it's important you configure whatever the right one is being used by your Qt program) and have a look through the tabs to see what is configured/available to you.

So far as I can see the Qt ODBC driver is installed correctly and the error comes from the ODBC driver on the connection string.

ChrisW67

@dan1973 Further to what @JonB noted, the examples on the Connection Strings site for an ODBC connection to Access are of the form:

Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\mydatabase.accdb;...

I expect if you open the ODBC admin tool this is exactly the name you will see on the system drivers tab.

Notice that the driver name here includes two different file extensions and differs from yours:

Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.accdb;...

dan1973

@ChrisW67

I used the following codes:
1.

    QString strDatabasePath = "C:/LCA_MC.mdb";
    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    QString strDbName = QString("DRIVER={Microsoft Access Driver (*.mdb)};FIL={MS Access};DBQ=%1").arg(strDatabasePath);
    //db.setDatabaseName("DRIVER={Microsoft Access Driver (*.mdb)};FIL={MS Access};DBQ=c:/ada.mdb");
    db.setDatabaseName(strDbName);
    bool ok = db.open();

    if(ok) qDebug() << "Success";
    else qDebug() << db.lastError().text();

    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    QString strDbName = QString("Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\\LCA_MC.mdb;Uid=;Pwd=;");
    db.setDatabaseName(strDbName);
    bool ok = db.open();

    if(ok) qDebug() << "Success";
    else qDebug() << db.lastError().text();

Still i get the same error:
"[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified QODBC3: Unable to connect"

I have a doubt: why QODBC3? since i am using QODBC.

mchinand

Also, be aware that the Access ODBC connector is likely 32-bit so your application has to be 32-bit. It's been quite a while since I used the Access ODBC with Qt, but I remember trying to install a 64-bit version of the connector but that created other MS Office issues.

JonB

@mchinand
I tried pointing this out to the OP earlier with

I think you are supposed to go into ODBC Data Source Administrator (32-bit? 64-bit? I think it's important you configure whatever the right one is being used by your Qt program) and have a look through the tabs to see what is configured/available to you.

but don't hear much back that s/he has tried.