Microsoft Office 2000 Developer

Creating a Data Source Using the ActiveX Data Objects Library

See Also

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

1. Create a reference to the ADO Object Library.
   
   
2. Create a Connection object.
   
   
3. Create the Recordset objects.
   
   
4. 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

1. Open a new Office document, for example, an Excel worksheet.
   
   
2. Open the Visual Basic Editor (ALT+F11).
   
   
3. On the Tools menu, click References.
   
   
4. In the References dialog box, select Microsoft ActiveX Data Objects 2.0 or 2.1 Library.
   
   
5. 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

1. 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 
   

2. 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"
   

3. 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

1. 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
   

2. Set the ActiveConnection property to the Connection object:

   Set rs.ActiveConnection = cn
   

3. 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.

4. 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.