logo
vbRad Home
Source Code
Book Reviews
Forum
Links
About Us
Contribute

Compare Databases with SQL Effects Clarity
 
 How to create ADO connection string

Posted on
2/18/2001
Author:
Robert Gelb
Email:
Not Shown
Applies To OS:
NT, 9x, 2000
Product:
5, 6



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.

  1. 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.
  2. 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.
    Provider tab
  3. 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).

  4. 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.

Add Your Comment  

Name: Email Address: all fields optional
Notify me via email when someone responds to this message (valid email required).

Enter the word:
 



Comments
#1. By Robert. Posted on 9/3/2008 5:04:39 PM
This is the best VB-DB secret i have ever seen. Thank you Robert Gelb

#2. By sam. Posted on 8/5/2009 4:28:45 AM
This article is really very good amd written in simple way.I really Appretiate the wrk you have done here.Thnx for Sharing your knowledge

#3. By froma. Posted on 8/15/2009 10:20:18 PM
This is the best guideline for creating ado connection strings I've seen in years. And this was posted back in 2001? Why am I only seeing it now?
I've now been trying to convert a vb6 project to a native c++ project and just couldn't get connection to a sybase ianywhere 9 database server. I've translated the (working) vb code to c++ but just couln't establish the connection. Problem was the specific keys which were used from a dsn file. I've been searching and trying for hours but after finding this post (from a link from stackoverflow) it took me a few minutes to get my c++ code up and running! Thanks man! You've really made my day.

#4. By froma. Posted on 8/15/2009 10:57:56 PM
This is the best guideline for creating ado connection strings I've seen in years. And this was posted back in 2001? Why am I only seeing it now?
I've now been trying to convert a vb6 project to a native c++ project and just couldn't get connection to a sybase ianywhere 9 database server. I've translated the (working) vb code to c++ but just couln't establish the connection. Problem was the specific keys which were used from a dsn file. I've been searching and trying for hours but after finding this post (from a link from stackoverflow) it took me a few minutes to get my c++ code up and running! Thanks man! You've really made my day.

#5. By Topher. Posted on 11/14/2009 4:32:42 AM
Thank you.

Thank you, thank you, thank you.

#6. By scooby arundas. Posted on 12/1/2009 8:36:33 AM
not clear .. yet to improve .. anyhw thanks

cheers

#7. By nachos08. Posted on 9/17/2010 2:54:23 AM
WHAT ARE THE SIMILARITIES AND DIFFERENCES BETWEEN ADODC,ADODB,AND THAT OF ODBC?..

#8. By Himanshu Girdhar. Posted on 10/12/2010 6:58:43 PM
Second best thing ... I have ever discovered over the internet.... (First was .. how to crack QTP)
Still..... thanks a million..... for sharing this cricial info..... thanks again...!!!!

#9. By Robert W.. Posted on 12/27/2010 8:05:32 PM
This was great, I learned a lot. Thank you sir!

#10. By fgh. Posted on 2/22/2011 6:45:07 AM
cvzsdagasdf sdfasdf asdf asdf asdfsadf

#11. By Reena. Posted on 3/17/2011 9:39:04 AM
Thanks...

#12. By James Crowley. Posted on 3/22/2011 9:07:54 PM
You might also be interested in this free tool that lets you generate sql server connection strings on the fly. Only works for SQL Server but makes it nice and simple.

http://www.developerfusion.com/tools/sql-connection-string/

#13. By bikash. Posted on 7/1/2011 10:52:38 AM
how to create conection when two button are in there asp.net page, only one is conneted other is not connected

#14. By madel. Posted on 8/27/2011 7:35:54 AM
how to assayer to make a adodc

#15. By Nilson. Posted on 9/28/2011 5:09:16 PM
Thanks a lot Robert! a very usefull information aimed to connections. I hadn't never seen it on the web.

#16. By Eddie. Posted on 4/3/2012 12:57:30 AM
This page brings a tear to my eye. 2 WEEKS! 2 Weeks of searching for a solution finally over.

#17. By suwastica. Posted on 5/3/2012 3:31:43 AM
it is the same as

in vb6 create an adodc object >> right click the control >> properties >> general tab >> build >> provider tab >> microsoft ole db provider for odbc drivers >> connection tab >> source name, uname, pw, test connection >> click ok >> then copy the connection string