This retrieves data from an access database budget.mdb, this DB has one table: "budget" and seven fields. This code retrieves data in the Item field containing the text "lease" and the division field which contains the text " N. America". The qualifying data is stored in a Recordset object, the data is then transferred to the worksheet.
Sub ADO_Demo()
' This demo requires a reference to
' the Microsoft ActiveX Data Objects 2.x Library
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer
Cells.Clear
MsgBox "This demo retrieves the data for the records in which ITEM = LEASE and DIVISION = N. AMERICA."
' Database information
DBFullName = ThisWorkbook.Path & "budget.mdb"
' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct
' Create RecordSet
Set Recordset = New ADODB.Recordset
With Recordset
' Filter
Src = "SELECT * FROM Budget WHERE Item = 'Lease' "
Src = Src & "and Division = 'N. America'"
.Open Source:=Src, ActiveConnection:=Connection
' Write the field names
For Col = 0 To Recordset.Fields.Count - 1
Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name
Next
' Write the recordset
Range("A1").Offset(1, 0).CopyFromRecordset Recordset
End With
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub