ADO Help 1

[bdavis96]

My problem:

rst.open "Select * From Table, conn, adOpenOptimistic, adLockOptimistic

If I do:

rst.AddNew
rst("Field1") = Whatever
rst.AddNew
rst("Field1") = Whatever

The first AddNew gets added to the database (Access 2000), but the second is subject to rst.CancelUpdate and rst.Update.

I have tried several options using adLockBatchOptimistic, etc with either the same thing happening or some error at the second AddNew about releasing the fields.

What I am trying to do is allow unlimited AddNew, but then to have the decision to post all or post none to the database.

Can this be done with an ADO control? Any help would be much appreciated.

 

[Sheco]

Are you saying that you want to add multiple rows as part of a single transaction? Such that either all of them will be added or none of them?

 

[pkailas]

rst.AddNew
rst("Field1") = Whatever
rst.update
rst.AddNew
rst("Field1") = Whatever
rst.update

_______
I love small animals, especially with a good brown gravy....

 

[bdavis96]

Yes.

 

[bdavis96]

Basically I want to add however many records (rows), but at the end, have the decision at the end to update or cancel. If I do .AddNew, .Update, .AddNew, .Update, ... , I don't have the option to cancel all the .AddNew at the end.

 

[pkailas]

ahh.. yes, I didn't read your post close enough.. my bad!

_______
I love small animals, especially with a good brown gravy....

 

[bdavis96]

Take a check register for instance, you add 3 entries, you can see those 3 entries, they are in the memory of the ADO control, so then you can click Save or Cancel. Save will append all 3 entries to the database, Cancel will append none. Does an ADO control have "storage memory" that isn't the underlying database.

 

[pkailas]

I don't know. but one methoc to do something like that would be to actually update a temporary table and then on "Commit" or "Save" copy those values into the permanent table.

I do this on a "Quote" so it can be stored for later conversion into an "Invoice" without having to lookup all the parts again or to delete an invoice if the customer didn't decide to buy.

_______
I love small animals, especially with a good brown gravy....

 

[gmmastros]

Your missing a quote symbol here...


rst.open "Select * From Table, conn, adOpenOptimistic, adLockOptimistic

should be...

rst.open "Select * From Table", conn, adOpenOptimistic, adLockOptimistic

 

[DrJavaJoe]

Do a keyword search for Transaction ADO Commit Rollback.



Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[bdavis96]

I guess I don't know the difference between CursorType and LockType, I did a little more testing and it seems like adOpenStatic, adLockBatchOptimistic seems to do what I wanted.

 

[bdavis96]

DrJavaJoe,

You have pointed me towards an option I haven't tried before and from everything I read, looks like a better option then anything I tried. A couple clarifications and I will be on my way. First, Does Access/VB support nested Transactions? I can see myself needing one when I first open the program, and then a second when I get further into the program. Second, Does it matter what I do with the recordsets (ie. CursorType, LockType, .Update)? Will the updates still be subject to the commit/rollback command? I have read a couple posts on problems with commit/rollback, but they didn't seem to answer my question. If all of those answers are true, I have found a new approach that should work the way I want. I really appreciate your post.