ADO AddNew Method Fails in BeginTrans

[gray]

Hi All

Access 2002/2007
WinXP Pro SP2

I'm struggling to add a record into a recordset, or more specifically, copy a record within it.

I have a form with an unlinked subform. The subform is populated by the main form using an ADO recordset and within a begintrans/endtrans. I use an ADO so that a sort can be applied to it.

It's built like this:-

'Module declarations
Public Edit_rstADO As ADODB.Recordset
Public Edit_Cnn As ADODB.Connection
 
Private Sub Form_Open(Cancel As Integer)
Dim SQLLine As String
Dim Cntrlbx As Control
etc etc
 
Set Cntrlbx = Me.My_SubForm
 
'Build req'd SQL
SQLLINE = "SELECT TBL1.*, TBL2.* FROM ((User_Preferences AS TBL1) 
INNER JOIN Captions AS TBL2 
ON TBL1.Captions_Unique_No=TBL2.Unique_No) 
WHERE blah AND blah"
 
'Set up Transaction resources etc
Set Edit_Cnn = CurrentProject.AccessConnection
Edit_Cnn.BeginTrans
Edit_Transaction_Begun = True
 
'Create an instance of the ADO Recordset class and set its properties
Set Edit_rstADO = New ADODB.Recordset
 
With Edit_rstADO
        Set .ActiveConnection = Edit_Cnn
        .Source = SQLLine
        .LockType = adLockOptimistic
        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .Open
End With
'Set the form's Recordset property to the ADO recordset
Set Cntrlbx.Form.Recordset = Edit_rstADO
Cntrlbx.Form.OrderBy = "[Field_Selected] DESC,[TBL1.List_Order]"
Cntrlbx.Form.OrderByOn = True
 
End Sub

I then want to copy a record within it. There is a Primary Key, AutoNum, named 'Unique_No'. Here's my latest effort at a copy:-

Private Sub Copy_Record_Button_Click()
Dim Cntrlbx As Control
Dim Rec_To_Copy_Unqe_No As Long
Dim rst As New ADODB.Recordset
Dim Num_Fields As Integer 
 
'Extract selected record num
Set Cntrlbx = Me.My_SubForm
Rec_To_Copy_Unqe_No = Cntrlbx.Form.Recordset![TBL1.Unique_No]
Set rst = Cntrlbx.Form.RecordsetClone
 
'Navigate to chosen record
rst.Find "[TBL1.Unique_No]=" & Rec_To_Copy_Unqe_No
'Count it's fields
For Each fld In rst.Fields
       Num_Fields = Num_Fields + 1
Next fld
 
'Disconnect RS from Subform
Set Cntrlbx.Form.Recordset = nothing
 
'Add the new record
Edit_rstADO.AddNew
 
'Copy Fields from old record to new record, avoiding PK 
For i = 1 To Num_Fields
    If Edit_rstADO(i).Name <> "Unique_No" Then
          Edit_rstADO(i).Value = rst(i).Value
    End If
Next i
 
'Save the changes
Edit_rstADO.Update
 
'Re-connect Rs to SubForm
Set Cntrlbx.Form.Recordset = Edit_rst_ADO
 
End sub

My effort fails at the AddNew with:-

Error No: 3251 - Error Decription: Object or provider is not capable of performing requested operation.

But looking at the connection setup this should be OK?

Any Ideas anyone?

Thanks

 

[Banana]

First of all -

I want to point out that you can do a Sort in DAO recordset and Access' Form also provide a pair of properties, OrderBy and OrderByOn to provide the sort functionality. You're actually using those already, and that is not something specific to ADO.

You can use that with linked subform without the additional work required by ADO. ADO are great thing but I'm not seeing how it is the necessary solution.

With that in mind - few issues:

1) adOpenKeyset is not a supported cursor type for Jet OLEDB provider. I would expect you may be falling over to adOpenStatic (which is really what you want anyway) but if it's actually falling over to adOpenForwardOnly then that would be why you can't do an Addnew.

2) I don't think AccessConnection is the connection you want. Use CurrentProject.Connection

3) There should be no need to disconnect the recordset just to add new records?

4) RecordsetClone isn't good with ADO recordsets - use Form.Recordset.Clone instead.

Try them one at a time and see what works for you.

 

[gray]

Hi Banana

Thanks, I changed to CursorType = adOpenStatic
which on it's own did not cure the problem but with Set Edit_Cnn = CurrentProject.Connection ... it now works!!

Just to answer a couple of your queries... I may have tricked myself here ... it's a while since I switched this form to ADO... but I seem to recall I couldn't get the native DAO form to sort using OrderBy/OrderByOn whilst its Rs was part of a BeginTrans/EndTrans. The only way I could sort it was by adding an Order By to the SQL Select statement... fine until a different sort order is required... It does seem to sort OK with ADO though.

Also, I think I learned to disconnect / reconnect the Rs when changes were made because neither Refesh nor Requery got the changes to be seen in the form?

RecordsetClone vs Recordset.Clone... This is one of those things I've been meaning to look at for ages but never get around to... is there a fundamental difference in these then?

Thanks again

 

[Banana]

1) Ah, yes. Using transaction with a bound recordset is kind of problematic since you're now fighting with Access over who controls the transaction. Generally speaking, if you need transaction control, you're probably better off using a temporary table and committing the edit as a bulk operation within a transaction. .

The only case I know of where we can successfully bind a recordset and still have transaction control is when we're using MySQL backend because of the relatively unusual feature of MySQL ODBC driver allowing you to disable transaction support at ODBC level and as long all your queries are done via same connection, you still can submit transaction requests via a passthrough query (which is at SQL level, rather than ODBC level).

2) Whenever you do change filter or sort of a recordset and it's meant to be bound then this is usually sufficient:

Me.Recorset.Filter = ...
Set Me.Recordset = Me.Recordset

No need to disconnect/re-connect which as far as I've seen is not relevant unless I missed more details here.

RecordsetClone will always return a DAO recordset and therefore will fail when the form is bound to an ADO recordset, hence the need to change the syntax from RecordsetClone to Recordset.Clone.

 

[Banana]

Almost forgot -

There's an wiki hosted by another forums, UtterAccess and I wrote up an article which is still sparse but tries to cover some common gotcha with ADOs. Feel free to make use of it or better yet, add your own experience.

Using ADO wiki article

 

[gray]

Hi Banana

Thanks for such a comprehensive answer... it's great that you experts give up so much of your time!

Very much appreciated!

 

[MarkK]

Banana, on this topic,

The only case I know of where we can successfully bind a recordset and still have transaction control is when we're using MySQL backend

this works ...

Private m_dbs As DAO.Database

Private Sub Form_Load()
[COLOR="Green"]   'begin trans on default workspace[/COLOR]
   DBEngine(0).BeginTrans
[COLOR="Green"]   'retain an instance of CurrentDb, known to be drawn from the default workspace[/COLOR]
   Set m_dbs = CurrentDb
[COLOR="Green"]   'assign directly to the recordset property of the form...[/COLOR]
[COLOR="Green"]   '...a recordset created from a database from a trasaction enabled workspace[/COLOR]
   Set Me.Recordset = m_dbs.OpenRecordset("SELECT * FROM tTestData")
End Sub

Private Sub Form_Unload(Cancel As Integer)
[COLOR="Green"]   'close the form and notice that all changes you made were rolled back[/COLOR]
   DBEngine(0).Rollback
End Sub

Do you see a problem with this approach or have any thoughts or observations?
Thanks,
Mark

 

[Banana]

Hi Mark -

Glad to get challenged - it's been long time since I last tested. Leigh Purvis has a demo of bound form transaction (Transactions in Forms) and I remember there are issues with how you can commit/rollback. My memory was that you were limited in how you could start/rollback/commit a transaction and if you did at wrong time, you would get error or worse, find that it had no effect (because Access is also trying to control transaction).

I'll take in your code and do a quick test and come back with what I find out. Much obliged!

 

[MarkK]

I can see how there might be concurrency issues or other unexpected results, since some other process might consume CurrentDb. Or a user might leave his 'in-process' transaction A, go do some other task T, return and rollback A and unwittingly rollback T. But it seems these things might be managed using a modal form or something.

The link is great, thanks.

Leigh's example, however, starts the transaction on DBEngine(0) AFTER opening the recordsets on CurrentDb which seems counterintuitive. And she Dims independent object variables to contain those recordsets, which mean there's the Me.Recordset object and the Dim rst as DAO.Recordset object that both contain the same recordset. Not sure if that creates a problem or not, but I'd avoid it.

I have never implemented this in a production system, but it would be useful to know if I could do it effectively.
Thanks for an interesting discussion,
Mark

 

[Banana]

Lagbolt,

Haven't yet tested but few quick comments:

1) Leigh's a he, not she.

2) One scenario that I definitely know will not work is if you want to have a transaction span multiple recordsets - think of the form with a linked subform - years ago, I had a need to be able to control the transaction of creating both parent & associated child and avoid the situation where we have a parent record but no child record (which would not be valid by the business rule for this database). Because when you move in/out subform, an implicit save is made against the form's recordset. I'm very sure Access also commits the transaction behind the scene so that interferes with the scope. (I say very sure because I can confirm with absolute certainty from reading MySQL's log, Access was sending in several "COMMIT" and maybe "ROLLBACK" to MySQL whenever we did something like moving into/out of the subform. I assume the behavior is same with an Access tables though providing this is not as simple as it is with MySQL backend).

Like Leigh's demo, if you have a single record form, then transactions MAY work but it's something that I felt was too dicey to be worth the hassle based on the inferences I gained from above experience (if you search on my username, I started a thread several years ago asking about this specific problem), it was probably preferable to use a temporary table and run the bulk edits in a transaction via code to guarantee that Access won't muck up your transaction scope based on user action with the form.

I can see how using a modal form may reduce the overall uncertainty and will try to play with it and see how reliable we can get it. We also need to see how well it handles deletion which is a wholly interesting story on its own.

I can't comment on why Leigh is opening recordset then starting transaction but promise will post back after I've looked closely at his example again.

 

[Banana]

Okay, played with both LagBolt & Leigh's code to refresh my memory. Glad for that.

I can confirm that Lagbolt's starting transaction before setting recordset to the form works but in Leigh's case if I move the BeginTrans before I set the form's recorsource, I get error 2074, not supported in the transaction.

Investigating a bit, it seems to me that it is a matter of timing. Lagbolt's code happens on the Open event while Leigh's code is in a button's click event. Since Leigh's code is long after the form has loaded, it may be too "late" to wrap the form's recordset, hence the workaround of starting transaction after setting a new recordset to the form.

I want to point out that in Leigh's sample, the form is initially unbound, so there's no issue of duplicate recordset, but in light of the above, I would wonder if doing a BeginTrans is somehow more safer executed in the form's open event. We could simulate this by using a subform and re-setting the SourceObject, I believe, if we wanted to have the transaction to be started by clicking a button rather than implicitly via a form's open.

I also noticed that Leigh cautions about concurrency issues. I thought I remembered being put off by what I saw if I were to split the sample, create two copies of FEs and run them concurrently. However, in my recent testing, it seems to work OK and gives the expected behavior in case of write conflict. Maybe my memory was faulty or I was just a bungling noob back then (it's been years, after all).

Will need to think more and play more. So glad to have the chance to revisit this.

 

[MarkK]

Leigh's not a she?

Thanks for poking around at this.

I'll do some more too because I think it would be useful to be able to implement this in some cases. Maybe it would make sense to create a workspace when an app opens and put a database in it, like a custom TransDb, a companion to CurrentDb, but a non-default workspace against which transactions can be supported without curtailing the use of CurrentDb.
But more later,
Mark

 

[Banana]

I totally do love the idea of being to wrap the form editing in a transaction, but the whole idea was to be able to save oneself from having to code in the checks & bulk operations & whatnots and using Rollback/Commit & associated errors to manage data entry. Long ago, when I tinkered with it, I came away with the impression that the work required to set all this up would be at least same as the work required to set up temp tables & bulk operations.

I'm definitely in for discovering if we can make a better foundation to build something that enables us to have more transactional operations even with bound forms.

The idea of TransDb sounds interesting. It would be indeed more interesting to find out if a separate workspace also means better transaction control since Access won't be using that. OTOH, I still do believe that Access is indeed managing transaction controls so separating workspace may only create deadlock between our code & Access. Would be fun to find out.

FWIW, when doing bulk operation, I fell into this pattern which I think is fairly useful:

  On Error GoTo ErrHandler

  ...

  ws.BeginTrans 
  On Error GoTo TransErrHandler
  ...
  ws.CommitTrans
  On Error GoTo ErrHandler

  ...

ExitProc:

  ...
  Exit <procedure>
TransErrHandler:
  ws.Rollback
ErrHandler: 
  Select Case Err.Number
    Case ...
    ...
  End Select
  Resume ExitProc
  Resume

This way, if an error occurred within the transaction, I can roll back the transaction then use the default error handling. But if error is outside the transaction, we just go directly to the default error handler. Ergo, no "you tried to rollback without doing a BeginTrans first" error.

 

[gray]

Hi All

This is of great comfort to me.... I thought it was just my lack of Access skills. Here's my experience for anyone looking to implement similar.

Three of four years ago when I started all this, I was simply trying to implement a system that gave the user the opportunity to cancel his/her last set of edits... a quite commonplace event.. or so I thought.

Take a complex quotation (of the plumber type, not Shakespeare ). The quotation (stored in a Quotations table) might have dozens of tasks (stored in a Tasks table) and hundreds of parts (stored in a Parts table) plus it's own costs fields... all altering daily as a job is built. As we all know, at some time the user will want to cancel his/her last set of edits/additions/deletions to the quotation... and start again from the last saved "checkpoint". All very normal.

I began by trying to use Cancel=True in the BeforeUpdate event. Quite apart from the fact it did nothing (neither cancelled nor failed!... until I discovered Cancel=VBTrue... errr..) ... it was hopeless in the ubiquitous form/subform setup where data is held in the subform and custom action buttons are sited on the main Form. Selecting an item and taking an action on it was impossible.. the cancellation of the beforeupdate in the subform prevented use of the mainform ... derr... is it me or isn't this the precise use of a GUI?... select something... take an action?

I then read up on Disconnected Recordsets... let Access sync back new/deleted/edited records... but in my tests I seemed be losing changes during certain sequences of events... say an Addition of a record, then an Edit and a Delete of another. It seemed to get confused and the results were not as expected when re-sync'd.

I decided to use temporary tables to store [let's call it] a "work-space" ... then commit or throw away using batch in VB. After weeks of work I began to realise that this was a management/housekeeping nightmere.

As far as I could work out, temp tables are not available in Access so permanent ones are req'd but badged for "temp" use. This presents a whole host of challenges. The m/c crashes before the batch takes place? It is a multi-user system.. Are there are multiple joined tables? The overhead and, more importantly, reliability of loading all the data into the temp tables and copying it back (esp with PK autonumber fields).... co-ordinating data loads with associated "temp" tables. Do you kill the temp tables and build new ones each time... etc etc.. You also have to switch the forms between temp and permanent tables...what a joy that is! I eventually abandonned it.

My next version used "temporary" fields in the permanent tables... still some work to do (switching controls to temp_fields) but far, far less of an overhead... and much easier to manually lock when in multi-user systems too.

After I got this working I then found that I could use SQL Transactions in Access... all that coding/housekeeping/ overhead gone in an instant..... or, again, so I thought...

I've now become snowblind with the correct sequence of events needed to bind to a form, connection types, connection providors, cursor types, Access Workspaces and the pros/cons of DAO vs ADO let alone the subtle differences in syntax. I've now spent months implementing this on one form alone and today's issue is sorting the RS in a transaction... believe it or not it seems to be skipping records. Yee Gods!

Access reminds me of a Vauxhall van (panel van in the States?) my father used to use in his business For every hour he spent carrying goods and equipment it needed 50 hours maintenance.................... he eventually bought German...

Looking back, I think my preference is to use temporary fields.... so, so much easier... sadly, I binned all that in favour of Transactions and it would not be easy going back.


Moan over .... and thanks again for all your assistance...

 

[MarkK]

Well, I poked around with this some more and have not had much success with it. In one case my recordset object, assigned to the recordset property of the form, was destroyed by a rollback, so I'd rollback the workspace and the form's recordset property would become 'nothing.' I'd also intermittently receive a 'object invalid or no longer set' error, which I could not trap. If I stepped through the code this error did not occur!!! I could get something working if I handled the 'load' event of the form, and if I closed the form when a commit or a rollback was issued, but that's a pretty dire lack of functionality to be constrained in that way.
So I've abandoned this exploration. I'd use a disconnected recordset or temp table.
Cheers,
Mark

 

[gray]

Hi Lagbolt

Your time is much appreciated... and my immediate issue of adding a new record is resolved.

Since I seem to have got Transactions in a bound form working to a degree, once they are bedded in properly, I'd be happy to re-visit this thread and jot the calls and sequence of those calls down. Who knows between us we might be able to save others years of life!

Thanks