Transactions in Microsoft Access

[PinTxO]

Hi guys.

I have a database (.mdb) and I connect to it from my QT application. I do not have problems to read the data but when I save it, I want to do it through a transaction and I can not because I'm having an error. I paste how I open the connection and how I launch the transaction:

The connection:

m_db = QSqlDatabase::addDatabase("QODBC");
QString strPathDDBB="DRIVER={Microsoft Access Driver (*.mdb)};FIL={MS Access};DBQ=" + QCoreApplication::applicationDirPath() + "/galibo.mdb";
m_db.setDatabaseName(strPathDDBB);
if (!m_db.open()) {
    QMessageBox::critical(0, QObject::tr("Database Error"), m_db.lastError().text());
  }

The transaction:

bool feature = m_db.driver()->hasFeature(QSqlDriver::Transactions);
if (!m_db.transaction())
{
    error = "An error occurred while creating the transaction in the database\n";
    error += m_db.lastError().text();
    return false;
}

The feature variable returns true and the text error is "[Microsoft] [Microsoft Access ODBC Driver] Unable to define attribute now QODBC3: Unable to disable autocommit"

Any ideas?

[Pablo J. Rogina]

@PinTxO Maybe the issue is outside scope of Qt, and related to the ODBC driver you're using. Have you checked using that same driver with other application?
Your error "Unable to disable autocommit" maybe is related to some settings in the connection/database? As this post may clarify about transactions.
And also from your error "Unable to define attribute now QODBC3" this fragment from Qt's QODBC documentation may apply:

By default, Qt instructs the ODBC driver to behave as an ODBC 2.x driver. However, for some driver-manager/ODBC 3.x-driver combinations (e.g., unixODBC/MaxDB ODBC), telling the ODBC driver to behave as a 2.x driver can cause the driver plugin to have unexpected behavior. To avoid this problem, instruct the ODBC driver to behave as a 3.x driver by setting the connect option "SQL_ATTR_ODBC_VERSION=SQL_OV_ODBC3" before you open your database connection. Note that this will affect multiple aspects of ODBC driver behavior, e.g., the SQLSTATEs. Before setting this connect option, consult your ODBC documentation about behavior differences you can expect.

[PinTxO]

Thanks Pablo for answering.

I have already found out the reason for the error. The transaction is launched in the process of saving the application. Before this process, a series of consultations are made in the process of reading the information. Well, if in each query, the resources are not released through finish() method of the QSqlQuery class, then, when the transaction is launched, the commented exception is skipped.

Thank you

[Pablo J. Rogina]

@PinTxO ok, so it seems your issue is solved. If so, please don't forget to mark your post as such. Thanks.