Automate ODBC DSN Creation

When you have an application that requires ODBC DSN connections created on multiple PC’s, it can be time consuming to create them all. This is especially true if you have multiple Spire companies.

A simple one line batch file is all that is required to create an ODBC DSN for Spire on a computer. You will need to customize this to suit your environment.

You also have to decide the following:

  • 32-bit or 64-bit datasource?
  • User DSN or System DSN?

The choice of 32-bit vs 64-bit is not based on your Windows bitness. It is based on the application you are creating the database for. For Excel, Word or MS Access, you would check the bitness of your MS Office product. (Note step #3)

User DSN’s are available only to the user who is logged on when the DSN is created. System DSN’s are available to all users of that computer. User DSN’s are therefore more secure than System DSN’s

  • MyInspireDSN : The name you want for the datasource.
  • INSPIRE33: The database name of the Spire company.
  • localhost: Your server IP address or name.
  • password: The password for the PostgreSQL user “spireapp”

Here is the single-line batch file in which to substitute your values:

%WINDIR%\SysWOW64\odbcconf.exe CONFIGDSN "PostgreSQL Unicode" "DSN=MyInspireDSN|DATABASE=INSPIRE33|SERVER=localhost|UID=spireapp|PWD=password"

That example is for a 32-bit User DSN. To specify the bitness and/or User/System DSN designation, make these additional substitutions:

  • SysWOW64: leave as is for 32-bit, for 64-bit use “System32”
  • PostgreSQL Unicode: leave as is for 32-bit, for 64-bit use “PostgreSQL Unicode(x64)”
  • CONFIGDSN: leave as is for User DSN, for System DSN use “CONFIGSYSDSN”

Using these substitutions you can create a 64-bit System DSN:

%WINDIR%\System32\odbcconf.exe CONFIGSYSDSN "PostgreSQL Unicode(x64)" "DSN=MyInspireDSN|DATABASE=INSPIRE33|SERVER=localhost|UID=spireapp|PWD=password"

Important Notes:

  • Spire does not install the 64-bit PostgreSQL ODBC drivers by default. You can obtain them here.
  • The batch file contains the password for the spireapp user. This batch file should generally not be made available to users.
  • The batch file will over-write an existing DSN with no warning. But this is handy if you want to replace an existing DSN.