BeginTrans, CommitTrans, Rollback in Datasheet (MS Access)

Hello,

I am using Datasheet view when displaying my data.
The data are displayed in a "x,y" matrix wherein data can be directly edited in the Datasheet.

Anything inputted in a certain cell is directly updated to DB.
I want to open the Form, input data but WILL NOT COMMIT then close the Form.
If I open the same Form again, the data shouldn't be changed because it wasn't committed.

What I did was declare a global Workspace:
Then, instantiate it in the very first Form of the system:
In the loading of the Form w/c I want to control the DB update,
I started the db transaction setting:
In the said Form, I modified some data in the Datasheet.
For testing purposes, in my Close button click event, I made a rollback setting:
I did not commit the transaction. So, the data shouldn't been saved.
But when I opened that Form again, the data was not rolled back to its old value.

What's wrong with this?
Aren't these BeginTrans, CommitTrans and Rollback work well with Datasheet?

Calling all Access Gurus...I badly need help.


-= keirnus =-

The basic issue is that there are two different scopes; your own scope in the VBA code and the scope managed by Access when we use a bound form. Thus, when you issued a transaction, it does not cover the recordset that Access uses behind the scene when the form is bound.

For an example, take a look at Leigh Purvis's Transaction with bound form example. Now, This is a rough-working meant to show how to get the basics set up but you have to cover several more cases.

IMHO, a better & easier implementation may be to take a look at ADO disconnected recordset, which he also has a sample as well an example of it.

Hope that gets you somewhere.

Banana said:

The basic issue is that there are two different scopes; your own scope in the VBA code and the scope managed by Access when we use a bound form. Thus, when you issued a transaction, it does not cover the recordset that Access uses behind the scene when the form is bound.

For an example, take a look at Leigh Purvis's Transaction with bound form example. Now, This is a rough-working meant to show how to get the basics set up but you have to cover several more cases.

IMHO, a better & easier implementation may be to take a look at ADO disconnected recordset, which he also has a sample as well an example of it.

Hope that gets you somewhere.

Click to expand...

Thanks Banana.

I made a test form using Leigh Purvi's "Transaction with bound Form" and it worked perfectly. But got 1 flaw when I implemented it to my system. I just can't seem to edit any of the fields.

My guess is maybe because the query for my recordset is using more than 1 tables and the displayed data in Datasheet came from more than 1 table. (The subform is in Datasheet view which displays data from more than 1 table)

[1]
I set the Recordset Type of my subforms into "Dynaset (Inconsistent Updates)" but still wasn't able to edit the fields.
[2]
Instead of Recordset, I set the RecordSource of my subform but then the Rollback doesn't work.

Am I missing something?

Just to be clear, though... if you open the query itself, can you edit/insert into that query? Regardless whether it's a recordsource, a recordset or whatever, it must conform to the same rules for updatability when it's ultimately to be bound to the form.

Leigh's sample works fine for single-form but when we have a form-subform setup, AFAIK it's basically impossible to do so with bound form/subforms in most set-up.

In this case, few more options:

1) You need two recordset objects, or at least as many recordset as you have form & subforms. Open them all in a single transaction then attach to each subform where it belongs. It still should be a single transaction.

2) See if Access let you get away with linking in the subform. If it doesn't work, you may have to remove the link in the subform control and manage the content yourself.