Q: How can I automate the creation of ODBC datasources that I use to access Microsoft SQL Server?
A: The HKEY_LOCAL_MACHINESOFTWARE\ODBC\ODBC.INI registry key contains a subkey for each configured ODBC Data Source Name (DSN). The subkeys' entries store the DSN configuration settings. To create a datasource, you can simply create the required registry subkey. I use a script called CreateDSN.vbs to create ODBC DSNs for SQL Server. You can download this script from the Windows Scripting Solutions Web site. Go to http://www.winnetmag.com/windows scripting, enter 43314 in the InstantDoc ID box, then click the 43314.zip hotlink.
CreateDSN.vbs begins by defining a set of variables that will contain the essential datasource elements, such as its name and the path to the appropriate ODBC driver. As the CreateDSN .vbs excerpt in Listing 2 shows, the script then uses an instance of the FileSystemObject object to retrieve the path to the system folder, which is where ODBC drivers typically reside. Next, the script assigns values to the variables that supply the DSN (which the sDB variable contains), the default database's name (sDB), the DSN's description (sDescription), the path to the ODBC driver DLL (sDriverDLL), the SQL Server system's name (sSQLServer), and the driver's name (sDriverName). The sDriverDLL and sDriverName variables must use the values that you see in Listing 2. The values for the other variables can be whatever you choose. Finally, the script creates an instance of the WshShell object and uses that object's RegWrite method to write the registry subkey.