Thursday, March 8, 2007

VB: Use a UDL file to create an unusual connection string

I write VBScripts and VBA stuff all the time to connect to SQL Server and other databases. You need a connection string to do this, and sometimes you don't know how to make a connection string for the database you are connecting to. I have in the past always Googled for connection strings when I didn't know what to do, but I recently learned a great trick to create my own. You use a UDL file to find it:

  1. Create a new text file. Change its name to "sql_server.udl" or something appropriate with UDL as the extension.
  2. Double-click this new file. The Data Link Properties application will launch.
  3. Configure the connection as you want, including selecting the provider, server, user name, password, etc.
  4. Test the connection if you like with the "Test Connection" button
  5. Close the Data Link Properties editor, then open the new file with notepad, gvim, emacs, or some other text editor
  6. The last line of the file will be your connection string! Here is an example for SQL Server:

; oledb
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Password=REALSECRET;Persist Security Info=True;User ID=tim;
Initial Catalog=master;Data Source=MyServer;Application Name=

Some things to remember when configuring the connection:

  • The Provider will default to ODBC, so be sure to set the provider first, as all other properties will go blank when you select a new provider.
  • Don't forget that there is an "ALL" tab where you can set things like the "Application Name", which is nice if you will be trying to trace your app with SQL Profiler later.
  • If you click on "Allow Saving Password", the password will be saved in plain text in your UDL file, so be careful with the file when you are done with it.

No comments:

Post a Comment

I moderate comments blog posts over 14 days old. This keeps a lot of spam away. I generally am all right about moderating. Thanks for understanding.