Creating a Data Source Using the ActiveX Data Objects Library
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
The ADO Library allows you to create multiple connections and Recordset objects. In Microsoft Office 2000 Developer, you can use the Recordset object as a data source in many situations. This topic explains how you can create a Recordset object to be used as a data source.
To create a data source using the ADO Library
Create a reference to the ADO Object Library.
Create a Connection object.
Create the Recordset objects.
Set the DataSource property to the Recordset object.
Referencing the ADO Object Library
Before you can use the ADO Object Library in a project, you must reference it.
To reference the ADO Library in Office 2000 Developer
Open a new Office document, for example, an Excel worksheet.
Open the Visual Basic Editor (ALT+F11).
On the Tools menu, click References.
In the References dialog box, select Microsoft ActiveX Data Objects 2.0 or 2.1 Library.
Click OK to close the dialog box.
Creating a Connection Object
The first step in creating a data source is to create a Connection object.
To create a Connection object
Declare an object variable of the object type before using it.
Option Explicit ' Declare object variable as type Connection Private cn As New ADODB.Connection
In the worksheet's Activate event, configure the Connection object by assigning a string to the ConnectionString property.
A connection string is text that supplies the necessary information to create a connection to a data provider. The information written depends on the data provider. For example, if you have created an (ODBC) DSN to connect to the Northwind database on a specific machine, the connection string may look like this:
cn.ConnectionString = "DSN=Northwind"
The success of setting such a connection string depends on the DSN being present on the target machine. To avoid having to ensure that such a DSN exists, the connection string can be written to function on any machine (provided the database file exists in the same location). This is sometimes called a "DSN-less connection":
cn.ConnectionString = _ "Driver={Microsoft Access Driver (*.mdb)};" & _ "Dbq=C:\Program Files\Microsoft Office\Office\Samples\" & _ "Northwind.mdb;Uid=MyUserID;Pwd=MyPassword"
You can also connect to a database server using an appropriate connection string. The following one connects to a server machine running SQL Server:
Adodc1.ConnectionString = _ "Provider=SQLOLEDB.1;Password=MyPassword;" & _ "Persist Security Info=True;User ID=MyUserID;" & _ "Initial Catalog=Northwind;Data Source=MyDataServer"
Open the connection.
cn.Open
****Tip ****If you require a connection string but are unfamiliar with the syntax required by the OLE DB provider, use the either the Data Environment designer or the ADO Data Control to create one, and copy it for use with the ADO Connection object. In the Immediate window, type:
?myDataEnvironment.ConnectionString
Creating Recordset Objects
Once an instance of the Connection object has been created, configured, and opened, it can be used to create a Recordset object.
To create a Recordset object
Declare and create an instance of a Recordset object. The same methods for creating a Connection object apply:
Option Explicit Private rs As Recordset ' Or Private rs As New Recordset ' Or Private WithEvents rs As ADODB.Recordset Private Sub CreateRS() Set rs = New ADODB.Recordset ' If you didn't use the New keyword. End Sub
Set the ActiveConnection property to the Connection object:
Set rs.ActiveConnection = cn
Set the Source property of the object:
rs.Source = "SELECT * FROM Products"
The Source property is usually an SQL (or TransactSQL) statement that instructs the DBMS what recordset to retrieve.
Open the object, setting the CusorLocation and LockType at the same time:
rs.Open , , adOpenStatic, adLockOptimistic
Setting the DataSource Property to the Recordset Object
Once an instance of the Recordset object has been created, configured, and opened, it can be set to the DataSource property of a control (or other component).
Set DataGrid1.DataSource = rs
Example Code
Option Explicit
Private cn As ADODB.Connection
Private rs As ADODB.Recordset
Private Sub Worksheet_Activate()
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
'set cn.ConnectionString to Northwind DSN
cn.ConnectionString = "DSN=Northwind"
'open connection
cn.Open
Set rs.ActiveConnection = cn
rs.Source = "Select * FROM Products"
rs.Open , , adOpenStatic, adLockOptimistic
Set DataGrid1.DataSource = rs
End Sub
For a complete example, see Displaying Data from a Recordset in an Excel Worksheet.