Tracking down a WORKING connection string HAS to be one of the most painful things to get right. Think about it - there are
entire web sites dedicated to listing the many possible combinations. (ref
Carl Prothman's Connection String Site, for instance) And, don't get me wrong - they
are useful.
But, I wanted to pass on a couple of tricks that I use when setting up a VB program, so that I rarely need to visit those web sites any more.
One approach is to temporarily add an ADO Data Control to the app, and use IT in the design mode to build a connection string. Then, just select the connection string, copy it, and paste it into a constant in your code. Thereafter,
immediately drop the data control and it's reference. (This is about the ONLY legitimate use, as far as I'm concerned, for a data control...
)
This takes care of ODBC, but, What about OleDB? It's generally faster than ODBC, and it sure would be nice to be able to have some (easy) means for building an OleDB connection string, hmmm?
Well, you can create an empty text file with a .udl extension. Then, if you double-click this test file in explorer, the OleDB connection editor opens; you can then use the editor to update the udl file. After you save the updated udl file, open it with notepad - You'll see the OleDB connect string. This is a little kludgy though, especially if you want to include the capability in your app - isn't there a "cleaner" way?
Of COURSE there is! (Otherwise, I wouldn't have brought it up, right?
) Here you are - I've attached the source code for a handy little utility which uses the OleDB Datalink Editor to build a connection string for your apps, and it places the connection string into the clipboard, ready to paste directly into your app. Furthermore, since the heart of the utility is contained in a single function called SetConnection, it's easy for you to include this functionality within your app as well -Just copy the function into your app, and call it whenever you want to support letting your users "roll their own" OleDB connection to a database.
Enjoy!