Progress ODBC Connection Pulls Wrong Index from Database

1. Create an ODBC connection on the FIMS server and then the workstation using the steps in the following article:
How do I setup an ODBC Connection on my FIMS workstation?

2. Open Microsoft Access
3. Go to External Data\New Data Source\From Database\Access
4. The Get External Data - Access Database
5. Select the radial button for link to the data source by creating a linked table.
6. The Select Data Source" window will appear. Click on the Machine Data Source tab and select the ODBC connection for your database. Click on the OK button.
7. Highlight the Progress table you wish to link to and click OK
8. Note that the first record repeats multiple times.
9. Note also that the index for the table in Access does not match the index for the table in Progress.

 

In OpenEdge when a table is created using the 4GL engine there is no way to explicitly indicate the primary key column. The way you could indicate a primary key is by creating an index defining it as primary and unique. On the other hand, the SQL engine in OpenEdge works differently and even if a column can be defined as primary key in the CREATE TABLE statement if the table was created using the 4GL engine, SQL will see it differently. The ODBC driver just executes the ODBC functions sent by the client application, in this case MS Access., then using these functions the driver accesses the database and retrieve the information then is sent back to the client.

When MS Access queries the table metadata, the primary index is not returned as a primary key hence it does not show up as a key column. When MS Access cannot identify a primary key, it uses unique fields as the key. Setting unique index also makes the columns unique so they are returned and then set as the key column in MS Access.

The issue is that the database table in this example, Profile, has different unique indexes defined for the table, one of them is for idcode column (IDCODE index) and the other for External-Source and External-ID columns (External-ID index). The IDCODE index is defined as primary key as confirmed using the Data Dictionary tool.

External-ID and External-Source are unique due to these being able to only relate to one record in FIMS. These fields are associated with data that comes in from our other FIMS supported products.

Note that NPact only supports data coming into FIMS from supported FIMS programs like DonorCentral, and IGAM. If a change is made in FIMS using an external third-party data source, that change will not stay because the master database is not FIMS and is also not supported.