Progress ODBC Connection Pulls Wrong Index from Database

When using the Progress ODBC connection to pull data into another software like Microsoft Access, the wrong index is being sent from the database.
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.

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. 

It appears MS Access defines the correct primary keys, but when all the indexes are added the wrong primary keys are defined. In the table definition there are 2 unique indexes and both are sent back to Access, but for some reason the wrong primary key is always taken. It is not known yet where the problem resides since the unique indexes are sent back to Access and seems that only one is read or used to define the primary keys in Access.

The issue is that the database table in this case 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 we confirm with the customer using the Data Dictionary tool.

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.

For some reason MS Access queries the information of the table, which includes column name, datatype, etc. and then using an ODBC function SQLStatistics (<https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlstatistics-function?view=sql-server-2017>) queries the index information, which doesn’t return if any of the indexes is defined as primary key. When MS Access gets the information picks the "External-ID" index as primary key, which cannot be explained since it is an internal process. It appears that MS Access expects only one unique index defined for the table.

To verify this, you can use a third party tool for example, razorSQL, which is based on ODBC too. This tool identifies the primary key correctly and looking at the ODBC log file it appears that this tool uses the ODBC function that gets the primary key information from the table, SQLPrimaryKeys (<https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlprimarykeys-function?view=sql-server-2017>) while MS Access doesn’t use it.

The solution would be to have one Unique index for the Profile table since MS Access will always pick up the wrong index to define the primary key on its side. The unique index would be "idcode".

 

 

 

 

 
 

Steps to Duplicate

1. Create an ODBC connection on the FIMS server and then the workstation using the steps in the following articles:
https://kb.blackbaud.com/articles/Article/110801
https://kb.blackbaud.com/articles/Article/25528

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.

 

Environment

 FIMS

Was this article helpful?