When using the linked table option to create an external data source via ODBC, Microsoft Access changes the opening of a linked table or SELECT query syntax into three or more queries that it sends to MCPSQL. For example, the following query entered from Microsoft Access for a linked table:

SELECT * FROM customer;

results in the following queries sent to MCPSQL:

SELECT id FROM customer;
SELECT id, firstname, lastname, phone, email from customer where id=:H1
SELECT id, firstname, lastname, phone, email FROM customr WHERE id = :H1 OR id = :H2 OR
id = :H3 OR id = :H4 OR id = :H5 OR id = :H6 OR id = :H7 OR id = :H8 OR id = :H9 OR id = :H10;

For the first query, Microsoft Access determines the primary key of the table and issues a SELECT query for that field only. For the second query, Microsoft Access selects all of the fields in the table for one record, then uses this call to obtain information about all of the columns. The third query, with 10 bound values, is submitted in a loop until all of the records in the data set are retrieved. For example, if a SELECT all is performed on a table with 995 records, the third statement is issued 100 times, each time with 10 more values of the primary key (id) returned from the first SELECT statement.

These queries are only passed for linked tables that allow users to INSERT, UPDATE, and DELETE data directly into the DMSII database tables in addition to SELECT queries. When using the import into a new local table option to create an external data source via ODBC, only one query is passed to ODBC to retrieve all the records of a chosen data set.

Known Issues

Microsoft Access displays “Record is too large” error when accessing DMSII database

You might receive this error when importing a table from DMSII or selecting records from a linked DMSII table in Microsoft Access. The error occurs as a result of Microsoft Access constraints, which limit the number of fields in a table to 255 and the number of characters returned for a record to 4000 characters. For a complete description of these constraints, see the appropriate Microsoft documentation on Access specifications.

To resolve this issue without modifying the DMSII table declaration, use the SELECT statement in Microsoft Access with specified field names instead of * for all fields.

Microsoft Access displays “Column names in the order by clause shall identify derived columns of the query expression” error when selecting records from a DMSII database

You might receive this error when using ODBC to select records from a linked DMSII table in Microsoft Access. The error can occur as a result of performing a SELECT query that includes the ORDER BY clause with a field that is not the primary key of the data set; in this case, Microsoft Access changes a SELECT query into multiple queries. For example, the following SELECT queries for a table named PERSON with the primary key ID and another field NAME return an error:

SELECT ID, NAME FROM PERSON ORDER BY NAME
SELECT * FROM PERSON ORDER BY NAME

The first query that Microsoft Access then passes is as follows:

SELECT ID FROM PERSON ORDER BY NAME

MCPSQL requires that the column name in the ORDER BY clause must be included in the SELECT list, so it returns the error. There is no simple syntactical workaround for this problem.