Automating ODBC import using the Import Records script step

Because accessing ODBC data sources is a task you might repeat frequently, you can create a script with the Import Records script step that automates ODBC import. Some script step options behave differently than when used with other file formats.

The Specify data source option stores:

  • the data source name and location
  • the user ID and password (optional)
  • the SQL query to be executed against the data source

For more information, see Import Records script step.

When you choose an ODBC data source in the Import Records script step, the Specify ODBC SQL Query dialog box opens. You can do any of the following:

  • Specify a calculation to generate a query, including choosing a field that contains a query (see Storing an SQL query in a field)
  • Type a new query to be stored with the Import Record script step
  • Use the SQL Query builder to construct a query to be stored with the Import Record script step

Notes 

  • To save the user name and password, select Save user name and password in the ODBC Enter Password dialog box.
  • The With dialog:Off option hides all dialog boxes during an ODBC import.
  • The Specify import order option stores the field order in the Import Field Mapping dialog box, similar to other scripted imports.
  • The Set Error Capture script step suppresses ODBC alerts.
  • The ODBC import feature saves the data source name, user ID and password, and the SQL query from the previous ODBC import. Keep this in mind when selecting the Specify import order or Specify data source options.
  • In Windows, to automate the interaction across multiple applications, explore the ActiveX functionality. For more information, see Scripting with ActiveX Automation (Windows).
  • If you choose to execute a query from a non-global field, the query in the current record is executed. See Defining global fields (fields with global storage).
  • SQL statements are limited to a maximum length of 256 K characters (512 KB).
  • If you use the Import Records script step to import ODBC data that contains Unicode strings, your ODBC driver must support Unicode. Otherwise, the results may be inconsistent.