Transactional processing with subforms possible?

[Banana]

I have a feeling this isn't possible, but wanted to get a definite answer (or at least a potential workaround).

I would like to know if it is possible to use subform as a part of transaction, and rollback all the many-side records *and* the one-side record although should it fail to meet the criteria. Access's default behavior is to save the record before shifting focus to subform, which makes sense as we do need a primary key for the foreign key field, but this means we may have a potential childless record that may not meet the requirement. Making it a transaction would be far more simpler than mucking with temporary tables and/or writing several more lines which means more chances of errors or weird bugs.

Any thoughts?

 

[CEH]

set your relationship to "Cascade delete related records" ???

 

[Banana]

On surface, it would work but it doesn't get past the fact that this violates ACID.

There's a point just between after the main form is saved and before subform is dirty and that is precisely the PITA. Ghudson's mousetrap is no good here because I still have to save the form, and lose the dirty state, and the user is free to move off the record without an error, or maybe Access could lose network connection, and thus data integrity is lost.

I really wished Access made it an option to make edits in subforms transactional or not. :\

 

[KeithG]

So are you saying each parent rec must have a child rec?

 

[Banana]

Yes. No parent should be childless.

Edit: I should clarify- There are rules to which child a parent should have, and sometime there must be X number of childs. Therefore if it's incomplete, everything must be thrown out, the bathwater and baby all. The rule, I can enforce via VBA, but I really want this to be a single transaction.

 

[Banana]

Any suggestions to accomplish this?

I'm thinking I may need to use unbound forms, but can't shake the feeling that would defeat the point of using Access in first place.

 

[Banana]

After doing some research and a bit of experimenting-

You may be interested to know that transactions can be indeed done for bound forms. There's an article at MSDN detailing how to do so. However, this won't just work with a linked subform.

Looking through other googles, it seems that one developer using a MySQL backend came up with an idea of using bound forms for navigating, and a custom procedure to "unbind" the form to allow for the editing or inserting new records. This is quite a brilliant solution that does not take lot of work a true unbound form would entail.

So I got the idea of creating a class to make transactions across linked subforms possible, but there is one more barrier I need to figure out. More often than not, a subform are usually viewed in continuous or datasheet view and for a good reason. It goes without saying that you can't have a unbound control on either view. At that point, I'm juggling between several ideas and am hoping others can give me advice on what would be probably the best solution.

Idea #1

Use a dummy table with set of dummy fields to act as a placeholder and bind the the subform to the dummy table. If I can figure out how to track what changes are made to data so I can send it off to the server for transactional processing, it would work. But how do I keep track of the changes? I'm afraid that using either an array or a custom collection will get too unwieldly especially if the user shuttles all over the fields.

Idea #2

Use a subform that shows only one active record from a read-only continuous/datasheet subform and log the changes into a transaction. This is probably most easiest on the developer (e.g. me), but I don't think my users will like being constrained to single record, especially if they want to update same type of values in different records quickly without having to clicking a button to select a record to be updated. Also, how would I update the read-only subform, when the transaction isn't complete?

Idea #3

Somehow similar to #1, except instead of using a dummy table that has no meaning at all, pull over the needed recordsets for each form, save it to a local cache (if possible. I'm not positive if this can be done and in which case, use local tables instead), and simply destroy the link between form and subform (mind, the user will not be allowed to move off the main form until the transaction is complete). I'm not sure, however, of the effects this will have on the performance and Access has a tendency to treat disconnected recordset as nonupdateable.


Questions:

1) I understand that Jet's transaction is not a true ACID-compliant transaction. I intend to use ODBCDirect Workspace to execute transactions. Will that mean it uses the server's transaction processing, not Jet, correct? I ask this because the help files is a bit fuzzy on that one point: Workspace is still a DAO object, and I am not clear whether Transaction methods are derived from Jet or from DAO and whether DBEngine object represents Jet or simply is a pointer to Jet.

2) Do you have any additional information on how caching are done? I would like to ensure local cache is used, and DAO seems to have some methods providing for such, but the documentation is sketchy.

 

[Banana]

Does anyone have any suggestions?

 

[Banana]

Solution identified!

Today is the day I have my cake and ate it! (Or at least the day I've totally gone insane and deluded myself into imagining a dead end being a solution.)


After banging my head about how to achieve *both* bound form and precise transaction, I found a solution.

No coding required, beyond manually identifying where I want to start and end the transaction with a nice little prompt to my users if they want to commit/rollback the transaction.

It was so freaking simple, I could just cry.


With MyODBC, the driver for MySQL ODBC, I set an option to disable transaction.

This fools Jet into thinking that transactions isn't supported, so Jet doesn't bother with autocommits, sending commit commands and whatnots. I then send transaction commands using passthrough queries. Jet is none the wiser that the passthrough queries executes successfully despite being told by MyODBC that transactions are not supported.

And it works.

*goes back to check make sure it is definitely working*

Yes, it works.


Freaking beautiful.

 

[LPurvis]

So the two acts are independent?
You execute a PT which begins a transaction on the server, your users carry on about their business as they see fit, and then you (/they) execute another PT which commits (or rolls back) the transaction?
You'll, presumably, be requiring that Access maintains the same connection open to the server throughout it's instance? (Do you perhaps hold open a Read Only recordset or bound form to help ensure this?)

From where are you able to configure your MyODBC driver in this way? (I've not got it installed personally - just curious).

 

[Banana]

Apparently.

Whether this is a feature, bug, or just a brainfart, I'm not sure. (Does MS SQL driver have similar option, and does it work)? But yes, this seems to be independent; the driver doesn't reject the PT even though it says no transactions are allowed, presumably because PT is treated as a command/query which the server executes without driver's knowledge.

Yes, a persistent connection is assumed; all forms and subforms are bound.

Two ways to configure the driver:

Start -> All Programs -> Administrative Tools -> ODBC Data Source

Select the driver, then click configure.

But in my case, I use DNS less connection:

strConnection = "DRIVER={MySQL ODBC 5.1 Driver};" & _
                 "Server=myServerName;" & _
                 "Port=3306;" & _
                 "[COLOR="Red"]Option=16384;"[/COLOR] & _
                 "Stmt=;" & _
                 "Database=mydatabaseName;" & _
                 "Uid=myUsername;" & _
                 "Pwd=myPassword"

Option is where I configure stuff like disabling transactions, allowing big results, allowing multiple statement, whether to use ODBC API 1.0 or 2.0. that kind of stuff. They're a bitwise flags, so it's a sum of powers of 2. The dialog in ODBC Source's Configure shows the same options.