As I said in my original post, in order for a transaction to
span multiple databases, you need to do what's called "2-phase
commit" or "distributed transactions". This is not a trivial thing
to do, nor is it something that is often needed (especially in
Coldfusion, which is 99% of the time talking to a single database).
Why do you need to do 2-phase commit? and why isn't it
trivial?
"2PC is an acronym for 2 Phase Commit. This is a protocol by
which data being committed to a database is committed in two
phases. In the first phase, the transaction processor checks that
all parts of the transaction can be committed. In the second phase,
all parts of the transaction are committed. If any part of the
transaction indicates in the first phase that it cannot be
committed, the second phase does not occur. ODBC does not support
two-phase commits."
http://docs.openlinksw.com/virtuoso/twopcimplementation.html
I don't think Access supports 2-phase commit. Access isn't
really intended for the kinds of things that need 2-phase commit,
which Microsoft readily admits (hence MS SQL Server).
> My thought is that it is not allowed because CF only
remembers one data source per cftransaction therefore can only send
one commit or rollback instruction. If that's the case, it sounds
like a simple enhancement.
No, this isn't allowed because distributed transactions is a
non-trivial thing. This isn't all handled on the database side, as
you reference. If you're dealing with one datasource, yes, it does.
Once you pull in a 2nd datasource, you need something that can talk
to all databases involved (a transaction coordinator) and 1) check
to make sure everything can be committed safely then 2) commit
everything (hence 2-phase commit).
Basically, you're trying to do something that's non-trivial,
you don't understand why it's non-trivial, so you're getting
frustrated. I realize you're not a Java shop. I was simply trying
to explain to you why it was non-trivial, and a method to work
around that if you so desire.