Table of contents for ADO recordset code samples:
Open recordset
Movenext recordset
Get current position of recordset
Find a recordset with one selection criteria
Seek a recordset
Filter selection criteria for recordset find
Sort a recordset
Add a recordset
Add a recordset using an array
Update a recordset
Read a memo field
Update a blob field
Execute an Ado query
Execute an Ado query example #2
Execute an Ado query example #3
Execute an Ado query example #4
Sample ADO coding
Top
ADO Sub ADOOpenRecordset() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim fld As ADODB.Field ' Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=.\NorthWind.mdb;" ' Open the forward-only, ' read-only recordset rst.Open _ "SELECT * FROM Customers WHERE Region = 'WA'", _ cnn, adOpenForwardOnly, adLockReadOnly ' Print the values for the fields in ' the first record in the debug window For Each fld In rst.Fields Debug.Print fld.Value & ";"; Next Debug.Print ' Close the recordset rst.Close End Sub Top
Sub ADOMoveNext() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim fld As ADODB.Field ' Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=.\NorthWind.mdb;" ' Open the forward-only, ' read-only recordset rst.Open _ "SELECT * FROM Customers WHERE Region = 'WA'", _ cnn, adOpenForwardOnly, adLockReadOnly ' Print the values for the fields in ' the first record in the debug window Do Until rst.EOF For Each fld In rst.Fields Debug.Print fld.Value & ";"; Next Debug.Print rst.MoveNext Loop ' Close the recordset rst.Close End Sub Top
Sub ADOGetCurrentPosition() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset ' Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=.\NorthWind.mdb;" ' Open the recordset rst.CursorLocation = adUseClient rst.Open "SELECT * FROM Customers", cnn, adOpenKeyset, _ adLockOptimistic, adCmdText ' Print the absolute position Debug.Print rst.AbsolutePosition ' Move to the last record rst.MoveLast ' Print the absolute position Debug.Print rst.AbsolutePosition ' Close the recordset rst.Close End Sub Top
Sub ADOFindRecord() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset ' Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=.\NorthWind.mdb;" ' Open the recordset rst.Open "Customers", cnn, adOpenKeyset, adLockOptimistic ' Find the first customer whose country is USA rst.Find "Country='USA'" ' IMPORTANT - TO USE MORE THAN ONE SELECTION FIELD YOU MUST USE FILTER! ' Print the customer id's of all customers in the USA Do Until rst.EOF Debug.Print rst.Fields("CustomerId").Value rst.Find "Country='USA'", 1 Loop ' Close the recordset rst.Close End Sub Top
Sub ADOSeekRecord() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset ' Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=.\NorthWind.mdb;" ' Open the recordset rst.Open "Order Details", cnn, adOpenKeyset, adLockReadOnly, _ adCmdTableDirect ' Select the index used to order the data in the recordset rst.Index = "PrimaryKey" ' Find the order where OrderId = 10255 and ProductId = 16 rst.Seek Array(10255, 16), adSeekFirstEQ ' If a match is found print the quantity of the order If Not rst.EOF Then Debug.Print rst.Fields("Quantity").Value End If ' Close the recordset rst.Close End Sub Top
Sub ADOFilterRecordset() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset ' Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=.\NorthWind.mdb;" ' Open the recordset rst.Open "Customers", cnn, adOpenKeyset, adLockOptimistic ' Filter the recordset to include only those customers in ' the USA that have a fax number rst.Filter = "Country='USA' And Fax <> Null" Debug.Print rst.Fields("CustomerId").Value ' Close the recordset rst.Close End Sub Top
Sub ADOSortRecordset() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset ' Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=.\NorthWind.mdb;" ' Open the recordset rst.CursorLocation = adUseClient rst.Open "Customers", cnn, adOpenKeyset, adLockOptimistic ' Sort the recordset based on Country and Region both in ' ascending order rst.Sort = "Country, Region" Debug.Print rst.Fields("CustomerId").Value ' Close the recordset rst.Close End Sub Top
Sub ADOAddRecord() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset ' Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=.\NorthWind.mdb;" ' Open the recordset rst.Open "SELECT * FROM Customers", _ cnn, adOpenKeyset, adLockOptimistic ' Add a new record rst.AddNew ' Specify the values for the fields rst!CustomerId = "HENRY" rst!CompanyName = "Henry's Chop House" rst!ContactName = "Mark Henry" rst!ContactTitle = "Sales Representative" rst!Address = "40178 NE 8th Street" rst!City = "Bellevue" rst!Region = "WA" rst!PostalCode = "98107" rst!Country = "USA" rst!Phone = "(425) 555-9876" rst!Fax = "(425) 555-8908" ' Save the changes you made to the ' current record in the Recordset rst.Update ' For this example, just print out ' CustomerId for the new record Debug.Print rst!CustomerId ' Close the recordset rst.Close End Sub Top
Sub ADOAddRecord2() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset ' Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=.\NorthWind.mdb;" ' Open the recordset rst.Open "SELECT * FROM Shippers", _ cnn, adOpenKeyset, adLockOptimistic ' Add a new record rst.AddNew Array("CompanyName", "Phone"), _ Array("World Express", "(425) 555-7863") ' Save the changes you made to the ' current record in the Recordset rst.Update ' For this example, just print out the ' ShipperId for the new row. Debug.Print rst!ShipperId ' Close the recordset rst.Close End Sub Top
Sub ADOUpdateRecord() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset ' Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=.\NorthWind.mdb;" ' Open the recordset rst.Open _ "SELECT * FROM Customers WHERE CustomerId = 'LAZYK'", _ cnn, adOpenKeyset, adLockOptimistic ' Update the Contact name of the ' first record rst.Fields("ContactName").Value = "New Name" ' Save the changes you made to the ' current record in the Recordset rst.Update ' Close the recordset rst.Close End Sub Top
Sub ADOReadMemo() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim sNotes As String Dim sChunk As String Dim cchChunkReceived As Long Dim cchChunkRequested As Long ' Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=.\NorthWind.mdb;" ' Open the recordset rst.Open "SELECT Notes FROM Employees ", _ cnn, adOpenKeyset, adLockOptimistic ' cchChunkRequested artifically set low at 16 ' to demonstrate looping cchChunkRequested = 16 ' Loop through as many chunks as it takes ' to read the entire BLOB into memory Do ' Temporarily store the next chunk sChunk = rst.Fields("Notes").GetChunk(cchChunkRequested) ' Check how much we got cchChunkReceived = Len(sChunk) ' If we got anything, ' concatenate it to the main BLOB If cchChunkReceived > 0 Then sNotes = sNotes & sChunk End If Loop While cchChunkReceived = cchChunkRequested ' For this example, print the value of ' the Notes field for just the first record Debug.Print sNotes ' Close the recordset rst.Close End Sub Top
Sub ADOUpdateBLOB() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim rgPhoto() As Byte ' Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=.\NorthWind.mdb;" ' Open the recordset rst.Open "SELECT Photo FROM Employees ", _ cnn, adOpenKeyset, adLockOptimistic ' Get the first photo rgPhoto = rst.Fields("Photo").Value ' Move to the next record rst.MoveNext ' Copy the photo into the next record rst.Fields("Photo").Value = rgPhoto ' Save the changes you made to the ' current record in the Recordset rst.Update ' Close the recordset rst.Close End Sub Top
Sub ADOExecuteQuery() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset Dim fld As ADODB.Field ' Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=.\NorthWind.mdb;" ' Open the recordset rst.Open "[Products Above Average Price]", _ cnn, adOpenForwardOnly, adLockReadOnly, adCmdStoredProc ' Display the records in the ' debug window Do Until rst.EOF For Each fld In rst.Fields Debug.Print fld.Value & ";"; Next Debug.Print rst.MoveNext Loop ' Close the recordset rst.Close End Sub Top
Sub ADOExecuteParamQuery() Dim cnn As New ADODB.Connection Dim cat As New ADOX.Catalog Dim cmd As ADODB.Command Dim rst As New ADODB.Recordset Dim fld As ADODB.Field ' Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=.\NorthWind.mdb;" ' Open the catalog cat.ActiveConnection = cnn ' Get the Command object from the ' Procedure Set cmd = cat.Procedures("Sales by Year").Command ' Specify the parameter values cmd.Parameters _ ("Forms![Sales by Year Dialog]!BeginningDate") = #8/1/1997# cmd.Parameters _ ("Forms![Sales by Year Dialog]!EndingDate") = #8/31/1997# ' Open the recordset rst.Open cmd, , adOpenForwardOnly, _ adLockReadOnly, adCmdStoredProc ' Display the records in the ' debug window Do Until rst.EOF For Each fld In rst.Fields Debug.Print fld.Value & ";"; Next Debug.Print rst.MoveNext Loop ' Close the recordset rst.Close End Sub Top
Sub ADOExecuteParamQuery2() Dim cnn As New ADODB.Connection Dim cmd As New ADODB.Command Dim rst As New ADODB.Recordset Dim fld As ADODB.Field ' Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=.\NorthWind.mdb;" ' Create the command Set cmd.ActiveConnection = cnn cmd.CommandText = "[Sales by Year]" ' Execute the Command, passing in the ' values for the parameters Set rst = cmd.Execute(, Array(#8/1/1997#, #8/31/1997#), _ adCmdStoredProc) ' Display the records in the ' debug window Do Until rst.EOF For Each fld In rst.Fields Debug.Print fld.Value & ";"; Next Debug.Print rst.MoveNext Loop ' Close the recordset rst.Close End Sub Top
Sub ADOExecuteBulkOpQuery() Dim cnn As New ADODB.Connection Dim iAffected As Integer ' Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=.\NorthWind.mdb;" ' Execute the query cnn.Execute "UPDATE Customers SET Country = 'United States' " & _ "WHERE Country = 'USA'", iAffected, adExecuteNoRecords Debug.Print "Records Affected = " & iAffected ' Close the connection cnn.Close End Sub
To contact me (Author of this website and software), please E-mail me at:
This page last updated Aug 29 2004
PolandServices at yahoo.com