QODBC connection string failed

[decisionmaking]

Hi All,
I was using QODBC and set up a DSN to access a Microsoft Access (.mdb) database without any problem. My Qt version is 4.6.3. Now I would like to deploy the app. and avoid requiring users to set up a DSN. I found out that you can use a connection string instead. Please search for "connection string" in below page.
http://doc.qt.nokia.com/4.6/qsqldatabase.html#QSqlDatabase

Here is the DSN based code.

//----Initialize Database----
_dbDriver = "QODBC";
_dbDSN = "DecisionMakingStudy";
_dbHost = "localhost";
_dbUser = "";
_dbPasswd = "";

_db = QSqlDatabase::addDatabase(_dbDriver);
_db.setConnectOptions("SQL_ATTR_ODBC_VERSION=SQL_OV_ODBC3");//force ODBC3.x behavior
_db.setHostName(_dbHost);
_db.setUserName(_dbUser);
_db.setPassword(_dbPasswd);
_db.setDatabaseName(_dbDSN);

if(!_db.open()){
_db.setConnectOptions();// clear options
qFatal(_db.lastError().text());
}

...

Here is the connection string based code which is *failed* in _db.open() with error
"[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified QODBC3: Unable to connect"

_db = QSqlDatabase::addDatabase(_dbDriver);
_db.setConnectOptions("SQL_ATTR_ODBC_VERSION=SQL_OV_ODBC3");//force ODBC3.x behavior
QString databaseName = "DRIVER={Microsoft Access Driver (*.mdb)};FIL={MS Access};UID=;PWD=;DBQ=test.mdb";
_db.setDatabaseName(databaseName);

if(!_db.open()){
_db.setConnectOptions();// clear options
qFatal(_db.lastError().text());
}
...

The only difference between the two methods is highlighted in red. The .mdb file located at the project root directory. Using the absolute file path in connection string method did not help. Also connecting to a copy of the DSN linked .mdb (rather than the linked .mdb itself worrying DSN setup might prevent external access) failed as well.

Would you please help? Thanks so much.

Best,
dm

[jh]

this worked for me:


_db = new QSqlDatabase(QSqlDatabase::addDatabase("QODBC3","selections") );

QString connect_str = "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};FIL={MS Access};DBQ=";
// "DRIVER={Microsoft Access Driver (*.mdb)};FIL={MS Access};DBQ=";

QString str_db;

str_db = connect_str + fn;
// fn = filename

_db->setDatabaseName(str_db);

[decisionmaking]

Hi jh,

Thanks a lot. It works!
I guess the trick resides in the DRIVER value. Here is mine (failed)
DRIVER={Microsoft Access Driver (*.mdb)};
Here is yours (successful):
DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};
even though my database file is a .mdb. Here is the quote from
http://doc.qt.nokia.com/4.6/qsqldatabase.html#QSqlDatabase
"For example, Microsoft Access users can use the following connection string to open an .mdb file directly, instead of having to create a DSN entry in the ODBC manager:

...
db = QSqlDatabase::addDatabase("QODBC");
db.setDatabaseName("DRIVER={Microsoft Access Driver (*.mdb)};FIL={MS Access};DBQ=myaccessfile.mdb");
if (db.open()) {
// success!
}
...
There is no default value.
..."
I will report this as a bug.
Thanks again for the help.
Best,
dm