March 9, 2021
Hot Topics:

Book Review: Beginning Visual Basic 6 Databases

  • By John Percival
  • Send Email »
  • More Articles »

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 (Data Source Name)-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 (Data Source Name)-less example:

'A DSN (Data Source Name)-less connection looks like this
myADOConnection.Open "Provider=Microsoft.Jet.OLEDB.3.51;" _
& "Data Source=C:BegdbBiblio.mdb"

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 (Data Source Name)-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.

Try It Out - Creating a New 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 BegDbBiblio.mdb database.

When you click OK, you will now see the database name and location defined on the Setup dialog box.

3 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

Page 11 of 23

This article was originally published on November 20, 2002

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date