Importing from OLE DB data sources

To import from an external data source, such as Oracle, Microsoft™ SQL Server, or Microsoft Access, iBase must connect to the data source, which it does through facilities in Microsoft Windows™.

About this task

The most efficient way to import from a data source is to use one of these providers:

  • Microsoft Office 16.0 Access Database Engine OLE DB Provider (for Microsoft Access databases)

  • Microsoft OLE DB Provider for Oracle

  • Microsoft OLE DB Driver for SQL Server, or SQL Server Native Client

You can use Microsoft OLE DB Provider for ODBC Drivers, but this method is slower and less efficient, and is not described here.

You might need to ask your system administrator for information on the source database to set up a connection to it. For example, for SQL Server databases, you need to know:

  • The server name.

  • Whether Windows Integrated security is used or whether you must enter a username and password.

  • Which method to use to select the database.

Note: When you import from a Microsoft Access database that contains linked tables, check that the database that contains the physical tables that are being linked is also available.

Importing from an Access database

Before you can import from a Microsoft Access database, you need to obtain the password from iBase Designer. From the iBase Designer Tools menu, click Feature Availability > Options and open the Advanced tab. The password is displayed in the Database Password field. Copy the password to the clipboard.

  1. In iBase, click File > Data > Import. The import wizard opens.

  2. In Step 1, select OLE DB Compliant Data Source. Click Next.

  3. In the Connection area, click OLE DB Data Source....

  4. On the Provider page, select Microsoft Office 16.0 Access Database Engine OLE DB.

  5. On the All tab of the Data Link Properties page, select Jet OLEDB:Database Password, click Edit Value, and enter the password that you copied from iBase Designer.

  6. On the Connection tab, enter the location of the Microsoft Access database in the Data Source field. This is in the format of a file path ending with a .mdb file.

  7. If required, enter the username and password for logging on to the Access database.

  8. Click Test Connection to check that the connection is successful.

  9. Click OK. The OLE DB Data Source field in Step 2, changes to bold to indicate a connection is added (although not that the connection is correct).

  10. From the Source drop-down list, select the Microsoft Access table.

  11. Click Next to continue in the usual way.

Changing the import timeout value

Imports from OLE DB data sources have a 30 second timeout by default. It is possible for your system administrator to change the timeout value by editing the registry, for example:

HKEY_CURRENT_USER\SOFTWARE\i2\iBase\UserSettings\Importer:CommandTimeout
HKEY_LOCAL_MACHINE\SOFTWARE\i2\iBase\SystemSettings\Importer:CommandTimeout

If registry key overrides are not present, your system administrator can update the configuration tables in the iBase database.

To add the setting to the main configuration table, run the following SQL command:

INSERT INTO _Configuration_Def ([Item], [Encrypted]) VALUES ('Importer:CommandTimeout', 0)
INSERT INTO _Configuration_Text ([Item], [Data]) VALUES ('Importer:CommandTimeout', '123')

Then, in replicated environments, the following SQL command is run in the _GlobalConfiguration table:

INSERT INTO _GlobalConfiguration ([Item], [Data]) VALUES ('Importer:CommandTimeout', '123')

If you need further assistance, contact i2 Support.