As you already know, the Open method of the Connection object is used to establish a connection. With the OLE DB - ODBC Provider, an ADO application can use the ODBC connection mechanism to connect to a database server. ODBC allows applications to establish a connection through various ODBC data sources, or by explicitly specifying the data source information. This is commonly referred to as DSN-Less connection. DSN stands for Data Source Name. To see the difference, take a look at these examples. First, the standard, DSN connection:
Dim myADOConnection As New ADODB.Connection
'A DSN Connection looks like this
myADOConnection.Open "myDSN", "sa"
Next, here's the DSN-Less example:
'A DSN-Less connection looks like this
myADOConnection.Open "Provider=Microsoft.Jet.OLEDB.3.51;" _
& "Data Source=C:\Begdb\Biblio.mdb"
myADOConnection.Close
Set myADOConnection = Nothing
The DSN example opens a connection using the myDSN ODBC data source that points to a .mdb database. In other words, the DSN connection has all of the required information stored in it. With a DSN-Less connection, we provide all of the information required to open the connection.
We can now find a wide variety of ODBC drivers that can be used with ADO to establish a connection to data. Soon, there will be OLE DB providers available to connect to most data sources. You can use a different provider by setting the Provider property of the Connection. But if you want to connect with an ODBC-compliant data source, you could use the following Try It Out as an example and create your own DSN. Let's take a look at how this works.
First, we will build the New Data Link by creating a new ODBC Data Source. Then we will use the SQL OLE DB data provider to talk to it. We will go through these steps so you can see how to connect to virtually any data source.
First then, we want to build a Data Source Name (DSN). This can be referenced, and it will contain all of the information required to access a data source.
1. From your Windows 95/98 Settings | Control panel, select the 32bit ODBC icon:
This will bring up the ODBC Data source Administrator dialog box. Any data sources already defined will be listed:
2. Click on Add to create a new user data source. When you click Add, the Create New Data Source dialog box appears with a list of drivers:
Choose the driver for which you are adding a user data source. Since we are using .mdb files, select the Microsoft Access Driver. Any drivers that are installed on your machine will show up. Notice that the Access driver is version 3.51 - new with Visual Basic 6.0.
3. Double click on the Microsoft Access Driver (*.mdb) to display the Setup dialog box:
Provide the name of the data source as Our ADO Example DSN and the description as Beginning Database Programming in VB6.0. Now we need to set up the database. Within the Database frame, click the Select… button. Here you can navigate to our old friend, the \BegDb\Biblio.mdb database.
When you click OK, you will now see the database name and location defined on the Setup dialog box.
4. Next, click on the Advanced… button to display the Default Authorization.
5. Remember when we were discussing Access security (long ago, in Chapter 2)? Be sure to add Admin as the Login name. Then click OK. Now choose the User DSN tab and notice that our new DSN description is listed as a valid choice:
6. Make sure that Our ADO Example DSN has the Access driver file selected. Again, when you install new drivers on your machine, they will be listed as options here.
Now we'll test new ODBC Data Source
1. To ensure everything is working, let's go back to our form, frmADOPublishers and right click the ADODC control to bring up the properties page.
2. Choose Build… to bring up the Data Link Properties.
3. Since we just defined a new ODBC DSN, on the Provider tab select the OLE DB Provider for ODBC Drivers entry. Then click Next >>.
Now select our newly minted data source - Our ADO Example DSN - from the drop down box.
Be sure to add Admin for the User Name. However, it is already built into our DSN connection. If you needed to set this up for a specific user, it would be done here. Then click Test Connection to insure everything is fine.
Click the Advanced tab and select the Share Deny None choice as shown. Again, you would do this if you wanted to change the defaults defined when the DSN setup was built. But we do it here just to show you how to accomplish this:
Click OK and run your program. It works as advertised:
We have just created an ODBC data source that can be used with an Access database. If you needed another data source, you would follow the same steps with that driver. Now the OLE DB provider can take our commands from ADO and translate them to the new ADO ODBC data source. So the OLE DB acts as a universal translator from ADO to whichever ODBC driver we happen to be using.
In case you were wondering what the connection string looks like, here it is:
Provider=MSDASQL.1;Persist Security Info=False;User ID=Admin;Data Source=Our ADO Example DSN;Mode=Share Deny None
As you can see, all of the required information is now built into the string. Notice that the provider is now MSDASQL.1. We are now accessing the Biblio.mdb database via the ADO data control using ODBC.
Let's take a look at using some VB code to access our data via our new DSN.
1. Add a form to your project, and name it frmDSN. Add a single command button to the form and give it the name cmdTestDSN. Give it a caption as shown below:
2. In the Click event of the command button, add the following code:
Private Sub cmdTestDSN_Click()
Dim myConnection As ADODB.Connection
Set myConnection = New ADODB.Connection
'If we wanted, we could set the provider property to the OLE
'DB Provider for ODBC. However we will set it in the connect 'string.
' Open a connection using an ODBC DSN. The MS OLE DB for
' SQL is MSDASQL. We gave our new data source the name "Our ADO Example DSN"
' so let's use it.
myConnection.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;User ID=Admin;Data Source=Our ADO Example DSN;Mode=Share Deny None"
myConnection.Open
' Determine if we conected.
If myConnection.State = adStateOpen Then
MsgBox "Welcome to the Biblio Database!"
Else
MsgBox "The connection could not be made."
End If
' Close the connection.
myConnection.Close
End Sub3. Let's give it a try. Run the program and press the command button. Success!
Since we only want to test an ODBC connection, we only need to dim a new object variable as type ADODB.Connection. We then immediately initialize the object variable using the Set key word:
Dim myConnection As ADODB.Connection
Set myConnection = New ADODB.ConnectionNext, we just lifted the connection string that was built from our DSN. If you wish to copy it, simply bring up the ADO data control and copy the connection string. Our DSN provider placed the full connection string there. Since this is a string, be sure that the entire string is on a single line:
myConnection.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;User ID=Admin;Data Source=Our ADO Example DSN;Mode=Share Deny None"
Once we set the .ConnectionString property of the connection object, we simply invoke the .Open method to establish a connection to the data source:
myConnection.Open
We can then interrogate the .State property to see if the connection is open:
If myConnection.State = adStateOpen Then
MsgBox "Welcome to the Biblio Database!"
Else
MsgBox "The connection could not be made."
End If
If you need to find out the state of the connection, you can easily check the .State property against these constants:
| Constant | Description |
| AdStateClosed | Default. Indicates that the object is closed |
| AdStateOpen | Indicates that the object is open. |
| AdStateConnecting | Indicates that the Recordset object is connecting. |
| AdStateExecuting | Indicates that the Recordset object is executing a command. |
| AdStateFetching | Indicates that the rows of the Recordset object are being fetched. |
And since in our program the state is equal to adStateOpen, we display our message box indicating success! We then close the connection.
Now that we've demonstrated how to open up our connection to the data source, let's consider how to run some SQL against the data in the data source. We send any processing commands via the Execute method of the connection object.