Initially, when ADO first came out there was a lot of confusion as to how
create the Connection string needed for an ADO Connection object.
There is still quite a bit of confusion even though a simple method exists to
create this connnection string. Follow the steps below and you'll never
have problems generating connection strings again.
- On the desktop, create a file called x.udl. You can do this by
right-clicking on the desktop and selecting New/Text Document, then renaming the
resulting file to x.udl.
- Double click on the file, click on the Provider tab and you'll see the screen
below. If you don't see this screen, the check out troubleshooting tips below.
If you are connecting an Access 2000 database, select OLE DB
Provider for Jet 4.0. If you are connecting to Access 97 or earlier
database, select OLE DB Provider for Jet 3.51. If you are
connecting to a data source that has no native OLEDB provider (like Paradox, or
some versions of Sybase, Informix, etc...), select the Microsoft OLE DB Provider
for ODBC drivers. If you are connecting to Microsoft SQL Server, use the
native OLE DB Provider for SQL Server.
- Click on the Connection tab. Depending on what provider you've
selected, you'll see different things here. In general, you'll be asked to
enter UserID, Password, ODBC connection (if you've selected Microsoft OLE DB
Provider for ODBC drivers) and maybe the initial catalog to use (in other words,
if you have several databases on a server, which one do you want to use by
default).
- Click Test Connection button. If the connection succeeds, you are
done. Click OK. Then open x.udl in a text editor like notepad.
You will see the connection string that you need. Copy it out of the file
and set it to the ConnectionString property of the Connection object.
If the connection fails, you can try several things.
- Make sure you have all your spellings right.
- If you are using the ODBC driver for Sybase 11, leave the Initial Catalog
empty.
- Make sure that you can get a connection in Access. If it doesn't work
in Access, it won't work. Period.
- If trying to connect to an ISAM data source through JET, set the Extended
Properties on the All tab.
Troubleshooting Microsoft Data Link
So you created x.udl, but double-clicking on it doesn't bring up Microsoft Data Link applet.
It brings up Notepad or something like that.
Try the following troubleshooting tips.
- Make sure Hide file extentions for known file types setting is turned off.
When this setting is on changing x.txt to x.udl in reality creates x.udl.txt.
Change this setting by doing the following: open Windows Explorer, go to the View menu and
select Folder Options. Click on the View tab, then find the
Hide file extentions for known file types setting.
- UDL extention may not be associated with Microsoft Data Link. This happens very rarely.
To fix it, simply open Windows Explorer, go to the View menu and
select Folder Options. Click on the File Types tab and scroll down to
Microsoft Data Link. Click Edit. Make sure the open command corresponds to:
Rundll32.exe C:\PROGRA~1\COMMON~1\System\OLEDB~1\oledb32.dll,OpenDSLFile
If you can't find Microsoft Data Link, then create a New Type and associate
with the command above.