Although we have provided a number of built-in ODBC driver identifiers, as well as the automatic inference of driver from file extension, it is possible that you may need to create additional driver identifiers of your own. This might be because:
- you have a client-server database other than Microsoft SQL Server or Oracle
- you want to hard-code a user-id and/or password
- you want to avoid inference by file extension, perhaps because two ODBC drivers handle the same file extension (e.g., the Microsoft drivers for dBase and FoxPro both handle files with extension
.dbf)
- you want to streamline references to a particular database in many models.
In order to create a new ODBC driver identifier:
- Select ODBC Drivers from the Options menu. The ODBC Drivers dialog is displayed.
- Click the New… button. The Configure ODBC Driver dialog is displayed.
- Enter a short name, which will be used to identify this connection string within formulae. (The name must have at least two characters, and cannot include a ‘:’, to avoid confusion with disk drive identifiers, e.g., C:.)
- Enter the formatted connection string, using the built-in drivers as a reference for the general syntax.
Most connection strings will begin with the text DRIVER={DriverName}; where DriverName
is exactly as it appears in the ODBC Drivers tab of the ODBC manager in Windows Control Panel. You may need to consult your database documentation to establish the format for the rest of the string. The only exception is for a connection to a local data source, for which the connection string will be of the form
DSN=MyDataSource;. In this case, MyDataSource
is exactly as it is defined in the DSN tabs of the ODBC manager in Windows Control Panel, which is where the actual ODBC driver and other parameters are specified.
- You can click the Parameter button or press <Alt+P> to insert a
%p in the connection string as a placeholder for a parameter, and click the File Name button or press <Alt+F> to insert a
%f as a placeholder for a filename.
- Press <Enter> or click OK to complete.
Configuring an ODBC driver
Note: You can also create a new driver directly from the External Links dialog.
Once you have created an identifier, it can be used immediately as the basis for external links. When you first enter a formula using such a link, you may receive an error message if the connection string for the new identifier is incorrect, or the ODBC manager or driver may prompt you for additional information if the connection string is incomplete.
Note: It is not possible for STEM to copy any information which you enter in the ODBC manager or driver dialogs to the original connection string, and so you will need to amend the connection string by hand.
In order to revise the connection string:
- Select ODBC Drivers from the Options menu. The ODBC Drivers dialog is displayed.
- Select the relevant identifier from the ODBC Driver list.
- Click the Configure… button. The Configure ODBC Driver dialog is displayed.
Note: It is not possible to re-configure the built-in driver identifiers; but you may find it helpful to copy the text from a built-in connection string if you want to create a new driver similar to an existing one.