Skip to main content
MarHer
MarHer
Participant
June 3, 2014
Answered

Does cftransactions work with MS Access databases?

  • June 3, 2014
  • 2 replies
  • 484 views

The project I'm working on is accessing it's data from a MS Access db. When creating a new person in the database there are several tables where data has to be inserted and updated.

I had the problem, that when the second, third… insertion fails, the first one is already done. So I wanted to wrap the insertions in a cftransaction, so when a call fails the other ones get rolled back.

But now no insertion takes place, even when there's no error. I haven't found reliable information whether it is possible to use cftransactions together with MS Access dbs. Is there any definite statement if it works with MS Access at all or with which version it will work?

Hope you can help me out.

    This topic has been closed for replies.
    Correct answer Carl Von Stetten

    I doubt cftransaction will do you any good with MS Access.  The main purpose of the cftransaction tag is to utilize the underlying database's transaction functionality.  Typically, transactions link several query statements together as an atomic unit such that if any of the statements fail, the entire transaction rolls back and the database is left unchanged.  Access doesn't keep a transaction log, so there is no way for it to handle transactions (and thus perform rollbacks).  So you are left with partial updates as you are seeing.

    This is yet another reason that you will see recommendations from other ColdFusion developers to NOT use MS Access in ColdFusion applications.

    -Carl V.

    2 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    June 4, 2014

    MS Access's support for transactions apparently depends on version. MS Access 2007 and MS Access 2013 support transactions.

    Nevertheless, I would second what Carl says. You should consider transferring your data to a Relational Database Management System. I would recommend MySQL. Its Community Edition is free, and can outperform MS Access by many orders of magnitude, in every respect.

    Carl Von Stetten
    Carl Von Stetten
    Legend
    June 4, 2014

    Is transaction support part of the new Access .accdb format?  Thanks for that little nugget of knowledge!

    -Carl V.

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    June 5, 2014

    I know little of the developments at the Access and Jet camps. I would nevertheless imagine that, where transactions are possible, they would apply to MDB as well as ACCDB.

    Carl Von Stetten
    Carl Von StettenCorrect answer
    Legend
    June 3, 2014

    I doubt cftransaction will do you any good with MS Access.  The main purpose of the cftransaction tag is to utilize the underlying database's transaction functionality.  Typically, transactions link several query statements together as an atomic unit such that if any of the statements fail, the entire transaction rolls back and the database is left unchanged.  Access doesn't keep a transaction log, so there is no way for it to handle transactions (and thus perform rollbacks).  So you are left with partial updates as you are seeing.

    This is yet another reason that you will see recommendations from other ColdFusion developers to NOT use MS Access in ColdFusion applications.

    -Carl V.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices