Adding Multiple Data Sources to an Access Form

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.

In Microsoft Access, you can now make several discrete connections to different data sources on a single form. Although the following example uses only the Northwind database, each ADO Data Control can be configured to connect to a different database.

To add an ADO Data Control to an Access form

1. In Access, on the Insert menu, click Form to insert a new form, and open it in Design View.

2. Draw an ADO Data Control on the blank form. See Activating and Inserting Controls for details about inserting controls into Office 2000 applications.

3. Right-click the new control, and click Properties. Rename the control adCust, and close the dialog box.

4. Double-click the control to display the ADODC Properties dialog box.

5. On the General tab, click Build.

6. From the list of OLE DB providers, click Microsoft Jet 4.0 OLE DB Provider to select it.

7. Click Next.

8. In the Data Source, type the path of the Northwind.mdb database. If the database is installed in its default location, it is at:

   C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb
   

   ****Note   ****Office does not install Northwind.mdb by default. If Northwind.mdb is not on your machine, you need to run Office setup and choose Custom setup to install it.

9. Click Test Connection. If the connection fails, make sure the database exists at the stated location; otherwise check the path for errors.

10. Click the Advanced tab, and select ReadWrite. Then click OK to close the dialog box.

11. On the Authentication tab, set the User Name and Password, if needed.

12. Click the RecordSource tab, and set the Command Type to adCmdTable. From the list of available tables select Customers. Then click OK to close the dialog box.

13. Draw an unbound TextBox control on the form, and change its name to txtCompanyName.

14. Add a CommandButton control to the form, and change its name to cmdBind; change its Caption to "Bind."

15. Open the Visual Basic Editor (ALT+F11).

16. On the Tools menu, click References. Select Microsoft Data Binding Collection, and click OK to close the dialog box.

17. Add the following code. The code creates a BindingCollection object and adds the TextBox control to the collection:

    Option Explicit
    ' Declare an object variable for the Binding Collection.
    Private bcCust As BindingCollection
    
    Private Sub cmdBind_Click()
       ' Instantiate the object.
       Set bcCust = New BindingCollection
       
       ' Set the DataSource property to the ADO Data Control.
       Set bcCust.DataSource = adCust.Object
       ' Add a binding to the collection.
       bcCust.Add txtCompanyName, "Value", "CompanyName"
       ' You can add more bindings to the collection if you need; use
       ' the Add method to add a binding for each control on the form to
       ' the collection.
    End Sub
    

18. Run the form.

    You can run the form and click the button. The TextBox control should be now be bound to the CompanyName field of the table.

Adding Multiple ADO Recordset Objects

You can use multiple data sources by repeating the preceding steps to create a new ADO Data Control for every discrete data source. You must also create a new instance of the BindingCollection object to bind the control to other controls on the form.

As an alternative, the following code uses the ADO Recordset object to create two different data sources and binds each to a different instance of the BindingCollection object.

To use the following code

1. From the Tools menu, select References, and then set a reference to the ActiveX Data Objects 2.1 Library (or later).

2. From the Control Panel, select ODBC, then select the System DSN tab. Click Add, and select the Microsoft Access Driver, then click Finish.

3. Create AddressBook and PhoneBook for this example. Point each of them to Northwind.mdb Database, then click OK.

Option Explicit
' Declare object variables, one set of Connection, Recordset, and
' BindingCollection objects for each data source.
Private cnAddresses As ADODB.Connection
Private rsAddresses As ADODB.Recordset
Private bmAddresses As BindingCollection
Private cnPhones As ADODB.Connection
Private rsPhones As ADODB.Recordset
Private bmPhones As BindingCollection

Private Sub MakeConnnections()
   ' Instantiate objects.
   Set rsAddresses = New ADODB.Recordset
   Set cnAddresses = New ADODB.Connection
   Set rsPhones = New ADODB.Recordset
   Set cnPhones = New ADODB.Connection
   Set bmAddresses = New BindingCollection
   Set bmPhones = New BindingCollection

   ' Configure Connection objects. For brevity, the
   ' connection strings are simple DSNs. Note, however
   ' that the code won't work if a named DSN isn't on
   ' the computer.
   cnAddresses.ConnectionString = "DSN=AddressBook"
   cnAddresses.Open
   cnPhones.ConnectionString = "DSN=PhoneBook"
   cnPhones.Open

   ' Open Recordset objects
   rsAddresses.Open "SELECT * FROM employees", cnAddresses, _
   adOpenStatic, adLockOptimistic
   rsPhones.Open "SELECT * FROM customers", cnPhones, adOpenStatic, _
   adLockOptimistic
   
   ' Set the DataSource property to the Recordset object, then bind
   ' multiple text boxes using the BindingCollection.
   Set bmAddresses.DataSource = rsAddresses
   With bmAddresses
      .Add txtLastName, "Value", "LastName"
      .Add txtAddress1, "Value", "Address"
   End With
   
   Set bmPhones.DataSource = rsPhones
   With bmPhones
      .Add txtName, "Value", "ContactName"
      .Add txtPhone, "Value", "Phone"
   End With
End Sub