Q: I am developing software that uses an ODBC data source name (DSN) to locate a database that's out on the network. The problem is that on every client, I have to create the DSN manually. I'd like to know how to set up a DSN with all the necessary parameters programmatically, using Visual Basic.
A: Using named ODBC data sources is a good way of keeping your applications' databases anonymous: The less your app has to know about its database, the better. Unfortunately, as you point out, the main problem with using a DSN lies in configuring it correctly, which can require visiting each client machine. That's a time-consuming task if you have more than a handful of client PCs to configure.
Fortunately, there are a couple of ways to configure a DSN programmatically and invisibly so that your application's user doesn't even know it happened. In this article, I'll introduce two methods that will work in most cases. Which one you should use depends on your data access library and how much control you need over the process.
DAO's RegisterDatabase method
If you happen to be using DAO, you have a built-in solution in the form of the DBEngine.RegisterDatabase method. RegisterDatabase accepts four arguments:
- · The name of the data source, dbname.
- · The name of the appropriate driver, driver.
- · A Boolean flag, silent, that controls whether the driver dialog boxes are shown.
- · A set of attributes defining other information about the DSN, such as its description, the name of a default catalog, etc. Multiple attributes should be delimited by vbcr characters embedded in the string.
If the call to RegisterDatabase fails for any reason, an error is raised to the calling application. You can see an example showing the use of RegisterDatabase in Listing A.
RegisterDatabase is a quick and dirty way to add a new DSN, but it doesn't provide any facility for removing or reconfiguring an existing one. For that, we need something a bit more powerful.
A little more control
The ODBC API exposes a function called SQLConfigDataSource(), which is handy if you want more control over the process or you aren't using DAO for your database access. Happily, you can easily use the function from Visual Basic. Here's the Declare statement for it:
Declare Function SQLConfigDataSource _
Lib "ODBCCP32.DLL" _
(ByVal hwndParent As Long, _
ByVal fRequest As Long, _
ByVal lpszDriver As String, _
ByVal lpszAttributes As String) _
SQLConfigDataSource is distinguished from RegisterDatabase by the fact that you can use it to modify or remove a data source as well as add one. It accepts the following arguments:
- · The handle of the window that will "own" any dialogs that are displayed, hwndParent. This can be the hwnd property of a form in your application or zero if you don't want any configuration dialog to be shown. If you pass any other value here, you will get a dialog regardless of whether SQLConfigDataSource has enough information to set up the DSN.
- · The fRequest parameter controls whether the data source will be added (1), modified (2), or deleted (3). You can also add (4), modify (5), or delete (6) system-wide data sources that are available to all users of the machine, including system services.
- · The name of the ODBC driver, lpszDriver. Remember that ODBC is picky about driver names: The one you supply must exactly match the driver name that would be displayed in the ODBC Data Source Administrator.
- · A group of attributes, lpszAttributes, that sets various options for the data source, such as its description or default catalog. This argument is similar to RegisterDatabase's attributes parameter, but in this case, multiple attributes are to be null-delimited. Most attributes are specific to the particular driver you are using (refer to that driver's documentation), but some, like "Description" and "Server" are universal.
For an example illustrating the use of SQLConfigDataSource to add a new DSN, see the code in Listing B.
Figuring out what went wrong
If SQLConfigDataSource fails to configure your DSN for any reason, it will return False (0). A call will also fail if you don't specify all the correct attributes required to successfully configure the DSN and you didn’t specify a valid hwndParent parameter (meaning you don't want the ODBC Data Source Administrator dialog to display). Either way, you may have a hard time determining exactly why the call failed, since the VB Err object's LastDLLError property won't be set to anything meaningful.
To determine why a call to SQLConfigDataSource failed, you must call another ODBC API function, SQLInstallerError. Listing C rewrites the sample AddDSNConnection function to include a diagnostic call to SQLInstallerError in the event that SQLConfigDataSource fails.