Third Party Applications

MS Access

Microsoft Access provides some very easy to use tools that can be used to manipulate data stored in another database, including any Omnidex enhanced database.

The following discussion linking an Omnidex enhanced database to Access using the Omnidex ODBC Gateway.

Before beginning, please read the Prerequisites to ensure that you have everything you need to complete this exercise.

This discussion is not intended to teach MS Access.

Link Tables

Start Microsoft Access and either open an existing Access database or create a new one.

• The next step is to link the tables to Access through the Omnidex data source.
• In the Table section, click New.
• Select Link Table from the list of new table options and click OK.
• A File System dialog is displayed. At the bottom of the File System dialog is a drop down box labeled Files of type:. Open the drop down list and select the last item in the list ODBC Databases().
• A Select Data Source dialog is displayed. The dialog may vary depending on the version of Microsoft Office installed. Find the Omnidex data source that you want to connect to in the list and click OK.
• A Link Tables dialog appears. The Link Tables dialog displays a list of tables that you can link to in the Access database. The tables listed are the tables defined in the Omnidex Environment Catalog. Select one or more tables and click OK.
• A new table is created in Access for each table selected in the Link Tables dialog.

That's all there is to it! You can now manipulate the data, create forms and reports, directly in Access.

Any changes made to the data in Access are immediately reflected in the underlying database.

FYI

When using subforms in Microsoft Access, specifically parent table forms with one or more embedded child table subforms, Access will send an unusual sql statement to Omnidex when you attempt to add a parent record.

Access automatically attempts to retrieve all child table records for the current parent table record. When adding a new parent table record, the link field or parent id is null. Therefore, no child table records exist. However, Access still attempts to retrieve child table records by sending the following sql statement:

select * from childtable where linkfield=" "

When the link field is an integer field, which is fairly common, this statement is invalid. An integer field will never be equal to a space.

Therefore, depending on the Omnidex index installation, this statement will either produce an error or cause a serial read of all the child table records.

DISC is in the process of deciding how to handle this issue. The sql statement is invalid but all versions of Access tested, (97,2000,2003) send the same statement and produce the same results.

In the mean time, to avoid getting the error, install the link field in the child table with the ;kw option. This will cause a serial read of the child table but will prevent the error.

Top