How To Create an ADO Connection from a Data Link File in Data Access Components

View products that this article applies to.

Summary

Microsoft Data Links provide an easy way to create or validate a Microsoft ActiveX Data Objects (ADO) connection string that uses an ODBC driver or an OLE DB Provider to connect to a given data source. This article describes how to set up and use this Universal Data Link (UDL) Wizard.

Requirements

The following items describe the recommended hardware, software, network infrastructure, skills and knowledge, and service packs you will need:

MDAC 2.5 or later as well as any one of the following operating systems:
Microsoft Windows 95, Windows 98, Windows NT 4.0, or Windows 2000
NOTE: MDAC Data Links drivers are available after you have installed Microsoft Visual Basic 6.0, Microsoft Data Access SDK 2.x, or Microsoft Data Access Components (MDAC) 2.x run-time components. MDAC 2.5 is installed by default with Windows 2000. You can access Data Links from the Visual Basic 6.0 Data Environment or from the Microsoft Windows 95, Windows 98, Windows NT 4.0, or Windows 2000 shell, as outlined in the following steps.
Familiarity with Data Access Technology and Syntax (ADO)

How to Create a Data Link File

Right-click anywhere on the Windows 2000 desktop, point to New, and then click text document. A new file is created by default (New Text Document.txt).
Right-click the new text document that you just created and select rename, and then change the name and extension of the file to DataLink.udl and press ENTER.

If you do not see the file extension, open Windows Explorer. From the Tools menu, click Folder Options. On the View tab, clear the hide file types for known file types checkbox, and then click OK.NOTES:
- A warning might appear, explaining that changing file extensions could cause files to become unusable. Disregard this warning and click OK.
- You can store the Datalink.udl file anywhere on your system or network.
Double-click the Datalink.udl file or right-click it and then click Properties. This opens the Data Link Properties dialog box. You are now ready to create your data connection.NOTE: The file's icon now looks like a computer with a datasheet in the background. If you still see the Microsoft Notepad text file icon, ensure that the file extension is showing correctly by following step 2. Then rename if necessary.

Steps to Build a Connection String

Double-click Datalink.udl and use the Wizard as follows:
1. Click the Provider tab.
2. From the Providers menu, select the appropriate OLE DB or ODBC provider (you may only have OLE DB providers on your computer):
  - select Microsoft Jet 4.0 OLE DB Provider for Microsoft Access 2000 or 2002 databases
  - select Microsoft Jet 3.51 OLE DB Provider for Access 97 databases
  - select OLEDB Provider for SQL for Microsoft SQL Server databases
  - select OLEDB Provider for Oracle for Oracle databases
3. Click Next to proceed to the Connections tab.
With the Properties dialog box displayed, use the Connection tab to create a connection to a datasource:
1. Type your database name or browse to the database you want to connect to.
2. If you have created an account to use for Internet connections to the database, enter those credentials. Otherwise, you can use the default settings. (To use the default settings, enter Admin as the user name, and then select the Blank Password checkbox). If you do not want to use the default credentials, follow the steps under "Additional Information," later in this article, to configure the System database.
NOTE: You can optionally use the Advanced tab to set access permissions and All tab to set specific data type behavior available to either an ODBC or OLE DB data source.
Click test your connection to ensure that the connection will be successful.
After you have successfully completed and tested your connection, click OK to save your new data link and close the Wizard.

Steps to Using Your UDL File

From the .udl file, the connection string can be copied and used in an application that supports MDAC technology.

Right-click the Datalink.udl file and rename it as Datalink.txt. Click Yes in the warning dialog box that appears.
Double-click Datalink.txt, and a statement similar to the following appears: [oledb] ; Everything after this line is an OLE DB initstring
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My.mdb;User ID=;Password=;

The following line is the connection string from My.txt:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My.mdb;User ID=;Password=;

The following sample code illustrates how this connection string can be incorporated into an ASP page:

Set objCXN = Server.CreateObject("ADODB.Connection")
   strCXN = "Microsoft.Jet.OLEDB.4.0;Data Source=C:\My.mdb;User ID=;Password=;"
   objCXN.Open strCXN
      .
      .
      .
   objCXN.Close
   Set objCXN = Nothing

Additional Information

If you are specifying an Access database username and password, use the following steps to specify the path and the password to access the System.mdw file.

In the UDL Wizard:

Click the All tab.
Select the field labeled Jet OLEDB.System database.
Click Edit Value.
In the Property Value box, type the path to the System.mdw file:
C:\Program Files\Common Files\System\System.mdw

NOTE: Alter as necessary if the operating system is not installed on C:\.
Click OK in the Edit Property Value window.
Select the field labeled Jet OLEDB:Database Password.
Click Edit Value.
In the Property Value box, type the password for the System database.
Click OK in the Edit Property Value box.
Click OK to close the Data Link Properties window.

The final connection string should look like this:

Provider=Microsoft.Jet.OLEDB.4.0;Password=joe;User ID=jim;Data Source=C:\My.mdb;Persist Security Info=True;
Jet OLEDB:System database=C:\Program Files\Common Files\System\SYSTEM.MDW;Jet OLEDB:Database Password=14323

NOTE: The preceding connection string has been broken into two lines for readability; however, it must be in a single line in your code.

Troubleshooting

Although Microsoft does not recommend that you use Internet Information Server (IIS) to connect to Access databases, there are several advantages to using OLE DB versus ODBC for Access database connections. One of the benefits of using OLE DB connections is the ability to connect to databases without the need to create a user or system Data Source Name (DSN) on the Web server. Other advantages include the ability to call a thread-safe version of Microsoft Visual Basic for Applications and increased stability.

↑ Back to the top

References

More information about MDAC is available from the Microsoft Universal Data Access Web site:

http://msdn.microsoft.com/en-us/data/aa937729.aspx

The "Downloads" section lists the versions of MDAC that are available. Read the details prior to installing a new version to make sure that the new version will work correctly with your Web applications and configurations.

↑ Back to the top