DAO and SQL

I am setting up a DAO program that uses multiple tables without data controls. One of the tables I did set up a control for so I could do querries and "easily" display the data in a bound dbgrid. This table needs to be requeried many times with many transaction processes. I do not seem to be able make this work as planned . Any help would be highly appreciated.

Thank You.
Don B
Farm & Home Supply Center
www.farm-home.com



Here's the more pertanent code.

Private rs_grid As Recordset

Private Sub Form_Load()
Set db = OpenDatabase(gDataBaseName, dynamic)
Set rs_grid = db.OpenRecordset("arcusttr", dbOpenTable)
Data1.DatabaseName = gDataBaseName
....

BeginTrans

now do other stuff

displayGrid("openSet")
...
do other stuff including moving back and forth through the records in displayGrid sub.
displayGrid("closeSet")


******
Now if I rollback the transaction the grid clears the the data1 recordset is closed (I think implicitly) and I can go and process new transactions. If on the otherhand I "Committ" a transaction the next time I try to process a new transaction I get a error 3420 object invalid or no longer set
******



Private Sub displayGrid(choice As String)
Select Case choice
Case "openSet"

Set rs_grid = db.OpenRecordset("SELECT ct_invoic, t_tr_type, ct_item_id, ct_itemqty,ct_coment,ct_rowno " & _
"FROM arcusttr WHERE ct_invoic = " & Quote(gInvNumber))
Set Data1.Recordset = rs_grid
Data1.Recordset.Movelast
...
Other stuff

Case "movefirst"
If Not gGridDetlEmpty Then
Data1.Recordset.MoveFirst

Case "closeSet"
Data1.Recordset.Close

[pardede]

I think I see your problem:

Case "closeSet"
Data1.Recordset.Close

The .Close method within a transaction implies a rollback of the transaction, that's why you get the problem. I had the same problem when using .refresh of a data control (this triggers also an implicit rollback). Examine your code flow to avoid this implicit rollbacks, or use the workspace object to establish 'isolated' transactions (if you have books online you can read it there)