Visual Basic > Database SQL Stuff

Disconnected updates using ADO

Disconnected updates using ADO

The following demonstration routine shows how to performed disconnected updates to databases using ADO.

'Demonstration routine
'NOTE. First create a table in the Nothwind.mdb called redTest with columns "Type" and "Name"
Sub Test()
    Dim oConn As ADODB.Connection, oRs As ADODB.Recordset
    Dim sSQL As String, lThisInsert As Long
    
    On Error GoTo ErrFailed
    'Open a new connection to the northwind database
    Set oConn = New ADODB.Connection
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\northwind.mdb;"
    
    'Insert 50 dummy records into the test table
    For lThisInsert = 1 To 50
        sSQL = "INSERT INTO AndrewTest (Type, Name) VALUES ('Type" & Int(lThisInsert) & "','Name" & lThisInsert & ")"
        oConn.Execute sSQL, , adCmdText + adExecuteNoRecords
    Next
    
    
    Set oRs = New ADODB.Recordset
    With oRs
        'Open the recordset
        .CursorLocation = adUseClient
        .Open "Select * From redTest", oConn, adOpenStatic, adLockBatchOptimistic, adCmdText
        Debug.Print "The table redTest has " & .RecordCount & " rows..."
        
        'Filter the recordset
        .Filter = "Type Like 'Type1%'"
        Debug.Print "The recorset has " & .RecordCount & " matching rows..."
        
        'Delete each matching row
        Do While Not .EOF
            .Delete adAffectCurrent
            .MoveNext
        Loop
    
        'Filter the recordset to contain only changed rows
        .Filter = adFilterPendingRecords
        Debug.Print "The recordset has " & .RecordCount & " pending rows..."

        'Submit only the changed rows
        .MarshalOptions = adMarshalModifiedOnly
        .UpdateBatch
    End With
    
    Set oRs = Nothing
    oConn.Close
    Set oConn = Nothing
    Exit Sub
    
ErrFailed:
    Debug.Assert False
    Debug.Print Err.Description
    MsgBox "Database failure"
    'Resume
End Sub