BeginTrans, CommitTrans, Rollback in Datasheet (MS Access)

[keirnus]

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:

    Public WS As DAO.Workspace

Then, instantiate it in the very first Form of the system:

    Set WS = DBEngine.Workspaces(0)

In the loading of the Form w/c I want to control the DB update,
I started the db transaction setting:

    WS.BeginTrans

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:

    WS.Rollback

I did not commit the transaction.
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 =-

[FishVal]

Could it be so that you update db via ADO connection?

[keirnus]

Could it be so that you update db via ADO connection?

hmmm...it could be...

DAO and ADO are different, right?

Have to check it when I get back in the office tomorrow.

But, if DAO or ADO works, would it still be working for a Datasheet?
Could it be because I'm using Datasheet?

Coz I checked the net and there aren't any samples of Datasheet.

[ADezii]

Could be a simple Syntax Error (say that quickly 3 times!). Check the following Links:

http://bytes.com/topic/access/insights/677490-ado-transaction-processing

http://bytes.com/topic/access/insights/671094-dao-transaction-processing-what

[keirnus]

I was able to make a test form.
I got a subform in Datasheet view in my form.
I set the Recordset of the form and executed the tests for Begintrans, CommitTrans and Rollback in DAO. All went well.

I implemented it in my system. But I can't edit the fields.

My guess is the query in my Recordset is using more than 1 table and the data displayed came from more than 1 table as well. Unlike in my test which I only used 1 table.

I tried 2 things:
[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?

[ADezii]

I was able to make a test form.
I got a subform in Datasheet view in my form.
I set the Recordset of the form and executed the tests for Begintrans, CommitTrans and Rollback in DAO. All went well.

I implemented it in my system. But I can't edit the fields.

My guess is the query in my Recordset is using more than 1 table and the data displayed came from more than 1 table as well. Unlike in my test which I only used 1 table.

I tried 2 things:
[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?

Could be as simple as the underlying Recordset is not Updateable. Programmaticall y set a Form's Record Source equal to this Recordset, add a couple of Bound Fields to the Form, then see if you can Update these Fields. Create an ADO Recordset as opposed to a DAO Recordset, then check the Supports Method of the Recordset with the adUpdate Argument to see if it is Updateable.