Using ADO to Connect to a Microsoft Access Database

The newest and most recommended method of establishing a connection with an Access database is using the ActiveX Data Objects (ADO). You must begin by setting up a reference to the Microsoft ActiveX Data Objects 2.5 Library or higher version. The example procedure ADOOpenJet-Database connects to the Northwind database using the Connection object. This object is opened via the Open method. Notice that the Open method requires a connection string argument that contains the name of the data provider (in this example, it's MicrosoftJet.OLEDB.4.0) and the data source name (in this example, it's the full name of the database file you want to open):

con.Open _

"Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=C:\Program Files\Microsoft Office\" _ & "Office\Samples\NorthWind.mdb;"

After establishing a connection to the Northwind database, you can use the Recordset object to access its data. Recordset objects are used to manipulate data at the record level. The Recordset object is made up of records (rows) and fields (columns). To obtain a set of records, you need to open a Recordset by using the Open method. This method requires that you specify information such as the source of records for the Recordset:

rst.Open "SELECT * FROM Customers " & _ "WHERE City = 'London'", con, _ adOpenForwardOnly, adLockReadOnly

The source of records can be a name of a database table or query or the SQL statement that returns records. After specifying the source of records, you also need to indicate the connection with the database (con) and two constants, one of which defines the type of cursor (adOpenForwardOnly) and the other type of lock (adLockReadOnly). The adOpenForwardOnly constant tells VBA to create the forward-only recordset, which scrolls forward in the returned set of records. The second constant, adLockReadOnly, specifies the type of the lock placed on records during editing; the records are read-only which means that you cannot alter the data. The next part of the procedure uses the For...Each... Next loop to iterate through the Recordset and print the contents of the first record to the Immediate window:

For Each fld In rst.Fields

Debug.Print fld.Name & "=" & fld.Value & vbCr

Next

After obtaining the data from the first record, the Close method closes the Recordset and another Close method is used to close the connection with the Access database:

rst.Close con.Close

The ADOOpenJetDatabase procedure is given below:

Sub ADOOpenJetDatabase() Dim con As New ADODB.Connection Dim rst As New ADODB.Recordset Dim fld As ADODB.Field

' Connect with the database con.Open _

"Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=C:\Program Files\Microsoft Office\" _ & "Office\Samples\NorthWind.mdb;"

' Open Recordset based on the SQL statement rst.Open "SELECT * FROM Customers " & _ "WHERE City = 'London'", con, _ 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.Name & "=" & fld.Value & vbCr Next

' Close the Recordset and connection with Access rst.Close con.Close

' Destroy object variables to reclaim the resources Set rst = Nothing Set con = Nothing End Sub

0 0

Post a comment