ADO - Update

Private Sub cmdUPDATE_Click()

   

    On Error GoTo ERR_MAIN

    Dim objRS As ADODB.Recordset

   

    Set objConn = New ADODB.Connection

    Set objRS = New ADODB.Recordset

   

    ''1 CONNECT TO THE DATA SOURCE

    If Me.chkOracle Then

        objConn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=yti;" & _

                             "User ID=scott;Password=tiger;"

    Else

        objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

                     "Data Source=C:\JPI\11th Batch Training\dbMSAccess.mdb"

    End If

                

    objConn.Open

   

    objConn.BeginTrans

   

    ''2 OPEN RECORDSET

    With objRS

       

        .Open "SELECT * FROM EMP WHERE DEPTNO=40", objConn, adOpenKeyset, adLockOptimistic

       

        ''The Recordcount property is used to return the number of records in the recordset.

'        If .RecordCount > 0 Then

       

            ''MoveFirst - Moves the cursor to the first record in the recordset

            .MoveFirst

           

            Do Until .EOF

                ''Set the new salary - To accept the changes

                .Update Array("SAL"), _

                        Array(.Fields("SAL").Value + 500)

               

                ''MoveNext - Moves the cursor to the next record in the recordset

                .MoveNext

            Loop

           

'        End If

           

        ''Close the recordset

        .Close

   

    End With

   

'    objConn.Close

    Exit Sub

   

ERR_MAIN:

    MsgBox Err.Number & " : " & Err.Description

End Sub