What's the best method to 'batch process' records from a data entry session?

[stonward]

Hi Guys,

I have a system that includes a Purchase form and a Purchase Details subform, one to many relationship etc.

What is the best way to 'hold' the records entered into the subform, before committing them to disc. The customer needs to be able to check the entered data before it has been saved in the table.

I've thought of make table queries and have been experimenting with recordset clones, but am certainly not an expert with DAO/ADO programming. However, if i knew the best/preferred method then I would narrow down how much new I have to learn.

The fields I need to save are called, PurchaseDetailsID, PurchaseID, ProductID, UnitCost and Quantity - the fields of the PurchaseDetails table. Usually there are not more than 6 rows of such data entered each time.


Many thanks for your time,

RPE.

[TheSmileyCoder]

You cannot use transactions covering both the main form and the subform as far as I know. Thus the only viable solution is to use a table with temporary values, and then tie the saving/updating of subform records into the main forms navigation (I.e. update child records in live table when main form navigates to another record, or closes)

[NeoPa]

Check out BeginTrans in the Context-Sensitive Help. It would have to be understood well before using, but it may allow you to achieve your aim.

FYI: DBEngine.Workspaces(0).Name == "#Default Workspace#"

[stonward]

Thanks for your help SmileyCoder.

I figured that temporary tables would be a likely method, but have found it 'clunky' at best so far.

I can't believe this problem has become so difficult to solve...but generally when I have come across such an impasse in Access before, it simply means I have started with faulty fundamentals!

Thanks again,

Stonward.

[NeoPa]

Had to reset the Best Answer I'm afraid as it's factually incorrect. Transactions work at the WorkSpace level and, as such, can cover multiple disparate tables and even separate databases, as long as they are within the same WorkSpace.

[TheSmileyCoder]

I still stand by my answer. If it were as simple as you imply, then the following should work fine. Create a main form with a subform. In the main form, add 2 buttons btn_Cancel, and btn_SaveAndExit, and the following code:

Option Compare Database
Option Explicit
Private ws As DAO.Workspace
 
Private Sub Btn_Cancel_Click()
   ws.Rollback
   DoCmd.Close
End Sub
 
Private Sub btn_SaveAndExit_Click()
   ws.CommitTrans
   DoCmd.Close
End Sub
 
Private Sub Form_Close()
   Set ws = Nothing
End Sub
 
Private Sub Form_Open(Cancel As Integer)
   Set ws = DAO.DBEngine.Workspaces(0)
   Debug.Print "Workspaces:" & DBEngine.Workspaces.Count
   ws.BeginTrans
End Sub

However, when testing it seems all changes are commited regardless of which button is pushed.

I believe that Access handles the data changes in a seperate hidden workspace, and that is why the above does not work. If you (NeoPa) or anyone else has suggestions I am all eyes to look at it.

[TheSmileyCoder]

You can expand on the above by getting the db object from the workspace, and base the forms recordsets off said db like so:

Private Sub Form_Close()
   Set Me.Recordset = Nothing
   Set Me.frm_Sub.Form.Recordset = Nothing
   Set ws = Nothing
End Sub
 
Private Sub Form_Open(Cancel As Integer)
   Set ws = DAO.DBEngine.Workspaces(0)
   Set curDb = ws.Databases(0)
   Set Me.Recordset = curDb.OpenRecordset(Me.RecordSource, dbOpenDynaset)
   Set Me.frm_Sub.Form.Recordset = curDb.OpenRecordset(Me.frm_Sub.Form.RecordSource, dbOpenDynaset)
   ws.BeginTrans
End Sub
 

However you loose alot of the built in access funtionality. For instance the parent/child relation is no longer automatically enforced (All child records for all parent records are shown in subform, new child records do not automatically get the correct foreign key assigned.)

Im sure its possible, but I am also sure it will involve alot of extra coding (But PLEASE prove me wrong, and provide an elegant solution, I know of several places I could use it)

[NeoPa]

I'm afraid I can't do that now Smiley as that would take more time than I have.

I will say that I expect you may be right as I was only going on the information available in the Help System, and I know that has led me astray in the past. I will add it to my list of things to try, but why not send me a sample db to play with and test out. I will see if I can find a way to implement the logic as explained by the Help System. You have my Skype details. NB. I still only use 2003.

[NeoPa]

I did a test and found very similar results to you Smiley. IE. Nothing in a form seems to be managed within a transaction. I too, assumed this might be due to another Workspace object managing all Forms / Reports, but I ran the following code (between updating a control value and moving from the record to trigger changes to be saved) in the Immediate Pane and got the results shown (only) :

for each ws in DBEngine.Workspaces:?ws.Name:Next ws
#Default Workspace#

The code I used was very basic and inside a simple Form that allows updating of records in a local table (The Help System indicates it won't work with ODBC connections anyway).

Option Compare Database
Option Explicit
 
Private blnTran As Boolean
 
Private Sub Form_Open(Cancel As Integer)
    Call DBEngine.BeginTrans
    blnTran = True
End Sub
 
Private Sub cmdCommit_Click()
    Call CommitTrans(dbForceOSFlush)
    blnTran = False
    Call DoCmd.Close
End Sub
 
Private Sub Form_Close()
    If blnTran Then Call Rollback
End Sub

Line #18 consistently called Rollback, but never with any effect.

I'd better go back and set post #2 back to Best Answer.

[TheSmileyCoder]

From the access 2010 context sensitive help on workspaces:

Access 2010 Help
Access Developer Reference
DBEngine.Workspaces Property
Returns a Workspaces collection that contains all of the active, unhidden Workspace objects. Read-only.
Syntax

expression.Workspaces

expression A variable that represents a DBEngine object.

This would seem to indicate that there can be hidden workspaces, and I am guessing thats why you are not seing the workspace used, when looping through the collection.

[NeoPa]

Ah. Well spotted young man :-) That would tend to indicate that it may well be, as you say, contained within a hidden workspace.

All we need now is to identify the hidden workspace used, and to determine if all forms are opened within that single workspace, or whether each object is opened within its own, separate, workspace.

I tried to determine the workspace associated with a form but was unsuccessful at finding anything remotely helpful.