Information on optimizing ADO transactions?

About that subforms and transactions-

It is so decadent.

I'm impressed. It is the perfect solution. I have had the ability to set options when I used DB2 (although I don't remember if this was one of the options) but I have never seen a place to make such settings for SQL Server. Does anyone know how to do this?

Haven't used MS SQL- hopefully someone can help out.

A bit of background information, in case anyone else finds it useful-

I suspect that the only reason MySQL provided this as option is only because their default storage engine, MyISAM, is non-transactional for a faster performance, so there would be no point in enabling transaction on ODBC layer which MySQL silently processes whenever the MyISAM is used without any actual effect and just wastes the cycles. This helps improves the performance in this specific case. My usage could be constructed as "abuse" in the sense that they didn't intend this feature to enable me to bypass the transactions performed by Jet.

Now here's the thing, I couldn't find any references to transactions in ODBC Programmer's Guide, except for the SQL_ATTR_AUTOCOMMIT, which doesn't actually disable, but specify whether transactions can be implicit or must be explicit. This is normally a read/write, connection-specific, non-global setting, AFAIK. ODBC API doesn't use two-phase transaction; it's either implicit transactions, or explicit (by turning off the AUTOCOMMIT) and sending SQLEndTrans( [COMMIT | ROLLBACK ] ) call.

If I'm reading the source code for MyODBC right, all it does is check if transaction is enabled in the options, then set AutoCommit off, and a connection error is sent to the client (e.g. Jet in this case). I'm afraid I got lost following the convoluted calls and and translating the error codes, but I know this much- whatever the error was sent to Jet, Jet chose to silently ignore the error.

Maybe this will help someone else. I don't know.


EDIT:

Just had a flash of insight and figured out the code. The code sent to Jet is 'SQLSTATE: S1C00', or "Driver Not Capable." Oddly, this isn't in MSDN's programmer reference to ODBC, though it can be find in other links... Anyway, this is apparently a good thing as Jet chose to silently ignore that error.

Hi Banana,
I came across this website that I think belongs to Leigh Purvis or his company
http://www.databasedevelopment.co.uk/examples.htm
In any event, it has a database that creates a transaction across a form/subform. I took a very brief look at it and although the app warns you that there are problems with the solution, it doesn't really go into detail about what the problems might be.

Pat,

Thanks for the pointer- will definitely take a look and comment on that.

The example mentioned is an implementation of binding forms to recordsets in order to wrap the processes in a transaction.
While originally putting it together some time ago - there were one or two pitfalls which caused the process to be less than robust (you have to assign the recordset at just the right moment etc).
And it has the potential to become self design locking (i.e. as if another user were also editing the design) if you edit the code (particularly in the case of using ADO - due to the provider used) after use. (But then - so does much ADO work wherever you use the default Access provider connection).

And the example itself is left deliberately unpolished. For example the transactions are started and ended by manual button clicks - to leave each action obvious.
And the Rollbacks also don't necessarily render the effects well onscreen - some tidying for consistency is required.

Hence my stating next to the download link that it's not "comprehensive or clean" .
But hopefully makes some begin to think about it.
For those willing to scroll down past the pretend obfuscation there's a discussion on the methodology at this Experts Exchange thread (as indicated there I'd long since made my example by then).

A quick look, and I can see that it had same problems as my earlier experiments did, especially not always refreshing the recordset correctly after rollback or locked record (bizarrely enough, this locking only happened on *some* records, even thought those weren't edited at all. I never figured that one out and long ago gave up on that)

Here is what I use for my transaction. I'm assuming this will require MySQL or anything else that allows you to disable transaction as an option in connection, but at least you can look at the implementation. The linked tables are still there, but I also imported the tables used in their sample World database. If you want to try this with MySQL, just download a MySQL server, MyODBC, and the world database (You will have to scroll down a bit to get the link and the guide instructions as well. Also, you will need to ALTER TABLE xxx ENGINE=InnoDB; each table to support transaction.

Click to download the sample mdb (The file was too big to be attached, even with zipping; the actual link to download it is tiny, on bottom right corner under the ads and says "Save file to your PC")