TechRepublic Tutorial: Use Windows to create a connection string

Learn how to create ADO connection strings to data sources using a Windows dialog box

As a database consultant, you're constantly facing new challenges. You must keep abreast of new technologies in order to meet those ever-changing expectations from clients and users. That means most consultants no longer have the luxury of claiming an expertise in just one or two systems. Now, the consultant that knows enough about several systems will keep clients returning. It isn't easy, and one way to keep ahead of things is to let software do as much of your work as possible.

Something as simple as connecting to a database can become a time-consuming glitch if you don't know just the right arguments and values to make the connection. For instance, ActiveXDataObject (ADO) has become one of the most popular libraries for manipulating and transferring data. With all that flexibility comes a lot of options—too many to memorize even if you're using them all on a regular basis. In this article, I'll show you a quick and simple technique that uses a Windows dialog box to supply all the necessary components of an ADO connection string. In 30 seconds, you can have just the right connection string for the situation at hand.

A connection overview
There's more than one way to connect to a data source. Open Database Connectivity (ODBC) specifies the database and connection information in what's known as a System Data Source Name (DSN), which refers to a program-specific driver to connect and translate instructions. OLE DB is newer and faster, and the main difference as far as coding is concerned is that OLE DB connections are specified right in the code. It also tends to be a bit more difficult to put to use than ODBC (for most of us).

Although both methods require most of the same connection information, ODBC does a lot of the work for you via dialog boxes and options—Windows holds your hand through the process of creating an ODBC connection (a DSN). On the other hand, the OLE DB connection requires special code, and you just have to memorize and retain all the necessary arguments or at least have a good reference.

The most common object you'll work with is the ADO Connection object—it represents the connection to the data source. Listings A and B contain generic code for both situations, respectively.

Listing A: Explicit
Dim cnn As ADODB.Connection
Dim rst As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open (connectionstring)
Set rst = New ADODB.Recordset
Set rst.ActiveConnection = cnn
rst.Open datasource

Listing B: Implicit
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Opendatasource, connectionstring

connectionstring is a text string that includes all the relevant connection information. To open an explicit Connection, use the Connection object's Open method in the form:
connection.Open [connectionstring][, userID][, password][, options]

Or, open an implicit Connection object using the Recordset object's Open method in the form:
recordset.Open datasource, connectionstring

As you can see, either way you have to deal with a rather complex connection string, which can contain several optional arguments in the form:
Provider=value;File Name=value;Remote Provider=value;Remote Server=value;URL=value;

While all of the arguments are optional, you must specify the Provider, the File Name or the URL. Table A defines all five arguments.
Table A
Argument Explanation
Provider Specifies the data source's Provider.
File Name Specifies the actual data source.
Remove Provider Specifies the client-side provider.
Remote Server Specifies the client-side's server's path name.
URL Specifies an absolute URL, which usually identifies a file or directory.
connectionstring arguments

Sometimes, you can omit many of the arguments, and establish a connection by identifying just the provider and the data source. If the data source is secured, include the user ID and password. When this is the case, composing connectionstring isn't so difficult. On the other hand, most OLE DB providers support a large number of arguments, some of which I've listed in Table B. The reality is that often this argument is much more complex because it is so very flexible.
Table B
Provider Argument Explanation
SQL Server Integrated Security Use the value "SSPI" to use Windows NT login security
Data Source SQL Server instance to which you're connecting
Initial Catalog SQL Server instance connection to use
Database SQL Server instance connection to use
User ID SQL Server authentication username
Uid SQL Server authentication username
Password SQL Server authentication password for the specific User ID
Pwd SQL Server authentication password for the specific User ID
Trusted_Connection Yes setting uses Windows NT authentication; No setting uses SQL Server authentication
Use Procedure For Prepare Yes setting creates a temporary stored procedure when a command is prepared
Jet Data Source Jet database to which you're connecting
Jet OLEDB:System Database Jet system database to use to verify username and password
Jet OLEDB:Database Password Specifies password protected database password
Jet OLEDB:Lock Retry A value that determines how many times to attempt to read a locked page
Jet OLEDB:Lock Delay A value that determines the number of milliseconds to wait betweenattempts
Jet OLEDB:Max Locks Per File Maximum number of locks in a single database
Jet OLEDB:Registry Path Path to the registry key that contains Jet tuning and security values
Oracle Data Source Oracle instance to which you're connecting
User ID Authentication username
Password Authentication password
OLE DB arguments

When in doubt, turn to Windows
Memorizing every possible connection string argument and value isn't necessary if you use Windows to create a connection string. Specifically, the Windows Data Link Properties dialog box allows you to select connection options to provide a complete and accurate ADO connection string for just the provider and source you specify. You can probably fill in the Data Link properties quicker than you can look up the appropriate values in any reference.

To use this method to create a connection string:
  1. Open the Windows Explorer and right-click any window.
  2. Select New from the resulting shortcut menu, and then choose Text Document, as shown in Figure A.

Figure A
Specify a text file.

  1. Save this new (and as yet empty) document using the file extension .udl. When Windows warns you about changing the extension, click Yes to continue.
  2. Open the Data Link Properties dialog box by double-clicking the new .udl file.
  3. Click the Provider tab in the Data Link Properties dialog box.
  4. Select an OLE DB provider, as shown in Figure B. You'll choose the provider that's most appropriate for your data source. I've selected SQL Server.

Figure B
Select an OLE DB Provider.

  1. Click the Connection tab and identify the server instance and/or the data source as shown in Figure C. (The options will be appropriate for the Provider you specify.) Click the Test Connection button to make sure your options specify an available data source on an available server.

Figure C
Specify the connection's data source.

  1. Under the Advanced tab, specify the specific permissions the user(s) identified in the Connection tab should have.
  2. The All tab gives you an immense amount of flexibility over the specific connection because you can alter the connection's property settings. To do so, simply highlight the property, click Edit Value, and enter the new value to change the defaulting setting. Figure D shows how you might change the encryption default (but don't actually change the value right now).

Figure D
Change default values for specific Provider settings.

  1. Click OK when you're done.
  2. Using a text editor, open the .udl file. As you can see in Figure E, the text file contains a connection string that represents all of the options you just selected. Paste this string into your code.

Figure E
Open the .udl file to find a completed connection string that's just right for your particular connection specifications.

A wise consultant
A wise consultant doesn't know everything about every system; rather, he or she knows where to go for help. Don't try to become a human reference for every system you use. Instead, take advantage of the many utilities and tools at your disposal to do as much of your work as possible.