Does cftransactions work with MS Access databases?

[MarHer]

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.

[Carl Von Stetten · Accepted solution]

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.

[BKBK]

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]

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

-Carl V.

[BKBK]

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]

OK, I have to take a step back from earlier statements.  After doing a bit more research, it appears that the Jet database engine (which Access uses) does support transactions; it just doesn't keep a transaction log like RDBMSs do.  Transactions are definitely supported via JDBC (Java Database Connectivity), which is the way ColdFusion talks to databases generally.  The only code examples I could find online for using transactions with ODBC connections (which is generally how ColdFusion connects to Access, via a JDBC-ODBC bridge) involve .Net or VBA code, so I can't say for certain if it is doable via JDBC.

What is unclear is whether the ColdFusion transaction functionality (<cftransaction>) specifically supports transactions on Access databases.

[BKBK]

Carl Von Stetten wrote:

What is unclear is whether the ColdFusion transaction functionality (<cftransaction>) specifically supports transactions on Access databases.

The crux of the matter, with only one way to verify. To mix idioms, the proof of the pudding is in the eating. A toi maintenant, Marher!