Hi



Apologies first and foremost - I'm a VB amateur (at best).



When I run the code below I get a run-time error -2147217887. Is my 

logic/syntax valid for the use of ADO Trans Processing? In essence my 

Recordset comprises of three records (one with a matching value) and I'm 

looking to amend a value and then test the Rollback/Commit function.



The code only fails when a string MATCH occurs (if the string is not found 

the loop completes without error)



The field itself is NOT an Index field and it is one of three in a 

relational table.



Any ideas/suggestions would be extremely useful



Thanks



Tony



Private Sub Command1_Click()

Dim Cn As New ADODB.Connection

Cn.Open "books.udd"



Dim Rs As New ADODB.Recordset

Rs.Open "select * from customer where custcode <='C003'", Cn, 

adOpenDynamic, adLockPessimistic





Rs.MoveFirst



Cn.BeginTrans



Do Until Rs.EOF

   If Rs.Fields.Item("cust_region") = "EAST" Then

      Rs.Fields.Item("cust_region") = "NORD"

   End If

   Rs.Update

   Rs.MoveNext

Loop



If MsgBox("Save all changes?", vbYesNo) = vbYes Then

   Cn.CommitTrans

Else

   Cn.RollbackTrans

End If



        

        

Cn.Close

Rs.Close



Set Cn = Nothing

Set Rs = Nothing







End Sub



Private Sub Command2_Click()

Unload Me

End Sub

Dear Tony



Apologies, this isn't a direct answer to your question but here goes anyway:



Why not try doing a straight Update rather than using a Select and looping

through the records?



How about



UPDATE customer SET cust_region = 'NORD' WHERE cust_region = 'EAST' AND

custcode <='C003



Much easier (and faster)!



HTH



David





----- Original Message -----

From: "Anthony" <aleahy@t...>

To: "ActiveX_Data_Objects" <activex_data_objects@p...>

Sent: Tuesday, February 26, 2002 12:18 PM

Subject: [activex_data_objects] ADO Transaction Processing problem





> Hi

>

> Apologies first and foremost - I'm a VB amateur (at best).

>

> When I run the code below I get a run-time error -2147217887. Is my

> logic/syntax valid for the use of ADO Trans Processing? In essence my

> Recordset comprises of three records (one with a matching value) and I'm

> looking to amend a value and then test the Rollback/Commit function.

>

> The code only fails when a string MATCH occurs (if the string is not found

> the loop completes without error)

>

> The field itself is NOT an Index field and it is one of three in a

> relational table.

>

> Any ideas/suggestions would be extremely useful

>

> Thanks

>

> Tony

>

> Private Sub Command1_Click()

> Dim Cn As New ADODB.Connection

> Cn.Open "books.udd"

>

> Dim Rs As New ADODB.Recordset

> Rs.Open "select * from customer where custcode <='C003'", Cn,

> adOpenDynamic, adLockPessimistic

>

>

> Rs.MoveFirst

>

> Cn.BeginTrans

>

> Do Until Rs.EOF

>    If Rs.Fields.Item("cust_region") = "EAST" Then

>       Rs.Fields.Item("cust_region") = "NORD"

>    End If

>    Rs.Update

>    Rs.MoveNext

> Loop

>

> If MsgBox("Save all changes?", vbYesNo) = vbYes Then

>    Cn.CommitTrans

> Else

>    Cn.RollbackTrans

> End If

>

>

>

> Cn.Close

> Rs.Close

>

> Set Cn = Nothing

> Set Rs = Nothing

>

>

>

> End Sub

>

> Private Sub Command2_Click()

> Unload Me

> End Sub




$subst('Email.Unsub').

>





This e-mail is subject to copyright and the information in it is confidential.  Use of this e-mail or of any information in it other
than by the addressee is unauthorised and unlawful. Please delete this e-mail if you have received it in error.

Hi David



The reason why I'm not using a SQL Update is that I'm attempting to test 

transaction processing. Transaction processing usually involves looping 

through a recordset and updating a new master table based on a transaction 

file.



This involves scanning a recordset and updating.



Thanks for your suggestion.



Tony

Hi Tony



You can still wrap the Update statement in a transaction - if you Rollback

no changes will have been made.



If you can do the same thing with an Update that you would do by looping

through a recordset (read cursor) I would always use the update.



Regards



David



----- Original Message -----

From: "Anthony" <aleahy@t...>

To: "ActiveX_Data_Objects" <activex_data_objects@p...>

Sent: Tuesday, February 26, 2002 1:27 PM

Subject: [activex_data_objects] Re: ADO Transaction Processing problem





> Hi David

>

> The reason why I'm not using a SQL Update is that I'm attempting to test

> transaction processing. Transaction processing usually involves looping

> through a recordset and updating a new master table based on a transaction

> file.

>

> This involves scanning a recordset and updating.

>

> Thanks for your suggestion.

>

> Tony




$subst('Email.Unsub').

>





This e-mail is subject to copyright and the information in it is confidential.  Use of this e-mail or of any information in it other
than by the addressee is unauthorised and unlawful. Please delete this e-mail if you have received it in error.

Hi David



Based on how I've coded the example how would you amend it to cater for 

the UPDATE approach? How would I ROLLBACK once the RS.Open "UPDATE blah" 

has been committed?



Thanks once again



Tony

hello matey, how are you?



> -----Original Message-----

> From: Anthony [mailto:aleahy@t...]

> Sent: 26 February 2002 18:04

> To: ActiveX_Data_Objects

> Subject: [activex_data_objects] Re: ADO Transaction Processing problem

>

>

> Hi David

>

> Based on how I've coded the example how would you amend it to

> cater for

> the UPDATE approach? How would I ROLLBACK once the RS.Open

> "UPDATE blah"

> has been committed?

>

> Thanks once again

>

> Tony




> $subst('Email.Unsub').

>

Hi Tony



I would do the following (not tested...):



HTH



David





Dim lRecordsAffected As Long



Dim Cn As ADODB.Connection

Dim Cmd As ADODB.Command



    Set Cn = New ADODB.Connection

    Cn.Open "books.udd"



    Set Cmd = New ADODB.Command



With Cmd



    .ActiveConnection = Cn



    Cn.BeginTrans



    .CommandType = adCmdText

    .CommandText = "UPDATE customer SET cust_region = 'NORD' WHERE

cust_region = 'EAST' AND custcode <='C003"



    .Execute lRecordsAffected



    If MsgBox(CStr(lRecordsAffected) & " records have been updated. Do you

want to save all changes?", vbYesNo) = vbYes Then

       Cn.CommitTrans

    Else

       Cn.RollbackTrans

    End If



    Set .ActiveConnection = Nothing

    Set Cmd = Nothing

    Cn.Close

    Set Cn = Nothing



End With









----- Original Message -----

From: "Anthony" <aleahy@t...>

To: "ActiveX_Data_Objects" <activex_data_objects@p...>

Sent: Tuesday, February 26, 2002 5:04 PM

Subject: [activex_data_objects] Re: ADO Transaction Processing problem





> Hi David

>

> Based on how I've coded the example how would you amend it to cater for

> the UPDATE approach? How would I ROLLBACK once the RS.Open "UPDATE blah"

> has been committed?

>

> Thanks once again

>

> Tony




$subst('Email.Unsub').

>





This e-mail is subject to copyright and the information in it is confidential.  Use of this e-mail or of any information in it other
than by the addressee is unauthorised and unlawful. Please delete this e-mail if you have received it in error.

Hi David



Many thanks for that example code - it worked (in fact both extracts 

worked with MS Access but my main problem was that I didn't have a 

Transaction process active on my Unix machine so my bespoke client/server 

software could not complete the transaction processing request).



Thanks for your time, patience and efforts.



Tony