Updating records using ADO

[qjd2004]

Hiya,

I'm trying to loop through every row in a recordset and if the field rectype = 2 then execute some SQL on another SEPERATE ADO connection that deletes all rows from a table.

For the row in the recordset that has the rectype of 2, I want to get the value of field "DistID" and use that in the WHERE clause of the SQL Delete.

If recordset row(number) field("rectype") = 2 then
string variable DistID =
recordset row(number) field("DistID").value
End If

Can someone please help as I can't get it work. I'm very new to ADO stuff. Here's where I am at the moment:

Do While Not rsUpdate.EOF
  For i = 0 To rsUpdate.Fields.Count - 1
    If rsUpdate.Fields("RecType").Value = 2 Then
      DistID = rsUpdate.GetRows(rsUpdate("DistID")(i)).Value
      con2.Execute ("DELETE * FROM Mainframe " _
                  & "WHERE DistID = " & DistID)
    End If
  Next i
Loop

Can anyone help me out here?

 

[RoyVidar]

Hi!

I think I understand the "For the row in the recordset that has the rectype of 2, I want to get the value of field "DistID" and use that in the WHERE clause of the SQL Delete." part, and base this suggestion on that (replaced the for next loop with a movenext).

Using the value of the DistID field where rectype = 2 in the sql string:

[tt]Do While Not rsUpdate.EOF
If rsUpdate.Fields("RecType").Value = 2 Then
con2.Execute ("DELETE * FROM Mainframe " _
& "WHERE DistID = " & rsUpdate("DistID").Value)
End If
rsUpdate.MoveNext
Loop[/tt]

- DistID is a numeric field

Is this what you're after?

Roy-Vidar

 

[qjd2004]

That's exactly what I was after, thank you.

How can I Do the same but update 2 other recordsets (choosing which one to update depending on the value of the rectype field)?

I've adapted your code to delete in 1 of 2 tables. I now need to insert these records in 1 of 2 tables/recordsets, but I dont know how to copy from recordset to another?

'This bit works fine        
Do While Not rsUpdate.EOF
   If rsUpdate.Fields("RecType").Value = 2 Then
        con2.Execute ("DELETE * FROM Mainframe " _
           & "WHERE DistID = '" & rsUpdate("DistID").Value & "'")
   ElseIf rsUpdate.Fields("RecType").Value = 1 Then
        con2.Execute ("DELETE * FROM client " _
           & "WHERE DistID = '" & rsUpdate("DistID").Value & "'")
   End If
      rsUpdate.MoveNext
Loop


'Not Working!
'Get new client records and update to the client table/rs
        
rsclient.AddNew
	Do While Not rsUpdate.EOF
           For i = 0 To rsUpdate.Fields.Count - 
                 If rsUpdate.Fields("RecType").Value = 1 Then
                        rsclient.Fields(i) = rsUpdate.Fields(i)
                 End If
           Next i
        Loop
rsclient.Update
        
'Not Working !
'Get new MF records and update to the MF table/rs 
        
rsMF.AddNew
        Do While Not rsUpdate.EOF
           For i = 0 To rsUpdate.Fields.Count - 1
                 If rsUpdate.Fields("RecType").Value = 2 Then
                        rsMF.Fields(i) = rsUpdate.Fields(i)
                 End If
           Next i
        Loop
rsMF.Update

Many thanks!

 

[RoyVidar]

Not intending to be rude, but you are again mixing up different ways of working with the recordset/chronology (or what it's called;-)) of which action to do when. You'll need to examine the first sample a bit more, and perhaps consider a more simplistic approach (se the third * below), and then expand.

Also, stating "not working" isn't very helpfull when trying to assist, errormsg etc makes it a bit easier...

Just typing/altering your code to something more similar to my approaches:

[tt]Do While Not rsUpdate.EOF
If rsUpdate.Fields("RecType").Value = 1 Then
rsclient.AddNew
For i = 0 To rsUpdate.Fields.Count - 1
rsclient.Fields(i) = rsUpdate.Fields(i)
Next i
rsclient.Update
End If
rsUpdate.MoveNext
Loop[/tt]

* When looping thru a recordset, you'll need to loop - and move to the next record (as in the working example)
* The addnew must be when you wish to add a new record -> inside the loop, and on the condition that a new record is supposed to be added
* The loop on the fields does not need to be performed until the criteria is met (and remember the -1) - but for starters, why not the more simplistic:
[tt]rsclient.Fields("PK") = rsUpdate.Fields("PK")
rsclient.Fields("Field1") = rsUpdate.Fields("Field1")
...[/tt]
Which would give a bit more control in the starting phase of using recordsets
* The update would be issued when you want the current to be saved (actually I don't think it's needed in ADO, it will be performed by the next .addnew etc, but I use it, "old habits" etc)

If you're performing all of this in the same operation, then you'd probably need to put all within the same loop of rstUpdate (or at least issue a .movefirst prior to doing the other loops).

Copying and moving the same records between identical tables, gives me an indication that the table structure might not be fully normalized, and might become be the source of massive headaches later on. Often I use only a status or category field when the data/records are "identical", to determine what kind of records they are, not place them in different tables.

Should be something to get you a step further, I hope...

Roy-Vidar

 

[qjd2004]

Thanks Roy,

That's a massive help actually. I've implemented some of your suggestions and things are working , and quicker!

I appreciate the background info as well, it's a big help to me as I've no formal training at all. Much of the theory is new to me as I'm learning bit by bit, mainly from the help files and looking at other people's code.

Many thanks for a helpful and informative post!

My code now looks like this (I don't know if it's the best way of doing what I wanted, but it works for now):

Private Sub UpdateAccessTbls(targetDB As String, srcDB As String)
'Work in prog
Dim con1 As ADODB.Connection
Dim con2 As ADODB.Connection
Dim rsUpdate As ADODB.Recordset
Dim rsKing As ADODB.Recordset
Dim rsMF As ADODB.Recordset
Dim i As Integer
Set con1 = New ADODB.Connection
Set con2 = New ADODB.Connection
'Set connection to the Update access db
con1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & srcDB
con1.Open

'Set connection to the Master accesdb
con2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & targetDB
con2.Open

Set rsUpdate = con1.Execute("SELECT DISTINCT DistID, RecType FROM Sent")
       
        'Clear out any existing records for the distIDs found in the 1st update rs!
        Do While Not rsUpdate.EOF
                If rsUpdate.Fields("RecType").Value = 2 Then
                    con2.Execute ("DELETE * FROM Mainframe WHERE DistID = '" _
                                 & rsUpdate("DistID").Value & "'")
                End If
            rsUpdate.MoveNext
        Loop
        
'Kill rsUpdate and reopen the rs with all the records from Sent
Set rsUpdate = Nothing
Set rsUpdate = con1.Execute("SELECT * FROM Sent")
'Set up rsets for Client and MF tables!
Set rsClient = New ADODB.Recordset
Set rsMF = New ADODB.Recordset
rsClient.Open ("SELECT * FROM Client"), con2, adOpenDynamic, adLockOptimistic
rsMF.Open ("SELECT * FROM Mainframe"), con2, adOpenDynamic, adLockOptimistic
            
            rsUpdate.MoveFirst
        'Get new Client records and update the table
            Do While Not rsUpdate.EOF
                    If rsUpdate.Fields("RecType").Value = 1 Then
                        rsClient.AddNew
                            rsClient.Fields("Client_ID") = rsUpdate.Fields("Client_ID")
                            rsClient.Fields("JD_ID") = rsUpdate.Fields("JD_ID")
                            rsClient.Fields("PDDescription") = rsUpdate.Fields("PDDescription")
                            rsClient.Fields("Pack_Size") = rsUpdate.Fields("Pack_Size")
                            rsClient.Fields("Conv_Factor") = rsUpdate.Fields("Conv_Factor")
                            rsClient.Fields("Client_Cost") = rsUpdate.Fields("Client_Cost")
                            rsClient.Fields("EUP") = rsUpdate.Fields("EUP")
                            rsClient.Fields("POR") = rsUpdate.Fields("POR")
                            rsClient.Fields("Handling_Credit") = rsUpdate.Fields("Handling_Credit")
                            rsClient.Fields("ContractID") = rsUpdate.Fields("ContractID")
                            rsClient.Fields("DistID") = rsUpdate.Fields("DistID")
                            rsClient.Fields("RecType") = rsUpdate.Fields("RecType")
                        rsClient.Update
                    End If
                rsUpdate.MoveNext
            Loop

        'Get new MF records and update the table
            Do While Not rsUpdate.EOF
                    If rsUpdate.Fields("RecType").Value = 2 Then
                        rsClient.AddNew
                            rsClient.Fields("Client_ID") = rsUpdate.Fields("Client_ID")
                            rsClient.Fields("JD_ID") = rsUpdate.Fields("JD_ID")
                            rsClient.Fields("PDDescription") = rsUpdate.Fields("PDDescription")
                            rsClient.Fields("Case_Size") = rsUpdate.Fields("Pack_Size")
                            rsClient.Fields("Conv_Factor") = rsUpdate.Fields("Conv_Factor")
                            rsClient.Fields("Dist_Price") = rsUpdate.Fields("Client_Cost")
                            rsClient.Fields("EUP") = rsUpdate.Fields("EUP")
                            rsClient.Fields("POR") = rsUpdate.Fields("POR")
                            rsClient.Fields("Handling_Credit") = rsUpdate.Fields("Handling_Credit")
                            rsClient.Fields("ContractID") = rsUpdate.Fields("ContractID")
                            rsClient.Fields("DistID") = rsUpdate.Fields("DistID")
                            rsClient.Fields("RecType") = rsUpdate.Fields("RecType")
                        rsClient.Update
                    End If
                rsUpdate.MoveNext
            Loop
    'Destroy the variables, kill the connections
    Set rsUpdate = Nothing
    Set rsClient = Nothing
    Set rsMF = Nothing
    con1.Close
    con2.Close
    Set con1 = Nothing
    Set con2 = Nothing
End Sub