Microsoft Office 2000 Developer |
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
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.
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
You can run the form and click the button. The TextBox control should be now be bound to the CompanyName field of the table.
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
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