VB and VBA Users Source Code: Disconnected updates using ADO
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Disconnected updates using ADO
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Sunday, October 20, 2002
Hits:
909
Category:
Database/SQL/ADO
Article:
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 AndrewTest 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 AndrewTest", oConn, adOpenStatic, adLockBatchOptimistic, adCmdText Debug.Print "The table AndrewTest 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
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
