Easy OLE DB Connection Strings

Have you ever had a problem constructing a connection string?  That's not even a gamble - it's a sure thing!  Everyone has had a problem either figuring out the correct syntax, or wondering if the reason they're not connecting to their source is due to bad syntax or a connectivity issue.

Instead of poking through your other code to find out syntax you've used before, searching the 'net, and/or wondering if it's the tool you're using that has the connection issue, there's a simple way to construct and test that connection string.  Much in the same way you can use DTEXECUI for SSIS to construct a command line to use with DTEXEC without having to read up on the command line options, Microsoft has a tool to help you construct connection strings.  Unlike DTEXECUI, however, this tool is included in the OLE DB subsystem in every Microsoft operating system (MDAC), so you're guaranteed to have it - no downloads required.
Create a Microsoft Data Link File
Microsoft supports a file format called a "Microsoft Data Link" - when you have MDAC installed, this file format supports a default GUI editor.  It's not hard to create one of these files:
1. Right-click and "Create New... Text Document". 
2. Rename the file to "Test.UDL" - but make sure you are actually renaming the file extension as well, and not just renaming the file to "Test.UDL.txt". 
3. If you get the "changing the file extension could make the file unusable" message - answer "Yes", and you're done this step.  If not, continue to #4.
4. You need to change the folder settings in Windows to allow you to see extensions of known file types.  Go to the "Tools", "Folder Options" menu in Windows explorer.  Switch to the View tab, and uncheck "Hide extensions for known file types".
Edit the Data Link Using the GUI
Simply double-click on your "Test.UDL" file, and you'll get a familiar GUI.  You've probably seen something almost identical to this when using many of Microsoft's own applications' facilities for connecting to data sources.
The GUI starts up focused on the second tab - but we need to change back to the first tab in order to select the driver we want to use.  On the first tab, select the OLE DB connection type that you want to use.  Every installed OLE DB driver is shown in this list - if you don't see what you're looking for, then you don't have the OLE DB driver installed.  This is one of the advantages of using this tool - a simple connection string, or even an error message from the system you're trying to use that string in probably won't tell you this clearly that you don't have the driver.
Press "Next" or select the second tab, and specify your driver-specific connection information.  Things such as the server name and login (for most RDBMS drivers).  Or the file name and location (for file-based drivers, like Excel or Access).  You may want to check the "Advanced" tab for more options - but typically that isn't necessary.  Press the "Test Connection" button to see if your settings work.  If they don't, you'll get an error message as to why - which I don't guarantee will help you out any!
Save your Connection Settings
Now that you can connect to your data source - because you've verified it by pressing the "Test Connection" button - you need to get the text connection string that represents all that connection information.  But before we do that, we have to make sure it gets saved to disk properly.  If you've specified a user name and password (non-integrated security), then you need to inform the system that you intend to save the password to the UDL file.  Check the "allow saving password" box - which will cause the system to warn you that it will save the password IN PLAIN TEXT.  You've been warned...
Press "OK" to save your settings.  (The system will warn you again about saving passwords to plain text if you've chosen to do so.)
Extracting the Connection String from the GUI
Fantastic.  You now have a verified connection saved in some cryptic format... how does this help you?  It helps because the format isn't cryptic at all - it's a plain-text connection string!  OK then, how do you read it?
1. Right-click on your UDL file.
2. Select "Open With...", then pick "Choose Default Program"
3. Find NotePad in the "Other Programs" list
4. Uncheck "Always use the selected program to open this kind of file" and press OK.
5. Right-click on the UDL file again.
6. Select "Open With...", and pick NotePad.
ZOMG - Cut and Paste - Are You Serious?
You bet I am.  Select the third line you see in NotePad.  Yes, the line after "Everything after this line is an OLE DB initstring".  Paste wherever you need an OLE DB connection string for a guaranteed functioning connection.