Microsoft Access can create table in its database that is linked to a table in an external database using ODBC.

With the release of the new ODBC Setup Wizard of AIS 5.1, some issues were reported about errors with linking MS Access tables to AIS data sources. This article explains why do these problems occur and how they can be resolved.

One issue that comes up is the error -7778 upon connection:

This error is due to an internal MS Access bug causing the connection to fail if the ODBC connect string does not contain "server=something". The AIS ODBC does not have such property and therefore it is not added to the DSN.

A simple workaround is to explicitly add this server=server to the custom environment setting of the DSN in the wizard as shown below:

The resulting definition may look like this:

The other issue that is specific to MS Access is that when linking a table from an ODBC DSN, MS Access copies the contents of the DSN (the set of connection string properties) into its database but in the process, it arbitrarily truncates the connection string if its length reaches about 256 characters. What part of the connection string gets truncated seems rather random and so are the observed side effects.

An easy way to verify this issue is by turning on "debug/apiTrace" and looking for the connect string that is passed to SQLDriverConnect in the AIS log.

Starting with AIS 5.1, the ODBC Setup Wizard offers many new options to add to the ODBC connection string. For this reason, the chances of getting longer connection strings are higher. If this MS Access bug impacts you, the only workaround is to try to reduce the connection string avoiding any setting that might be configured elsewhere (e.g., for non-thin AIS ODBC, the log file setting can be specified in the AIS environment definition rather than in the ODBC connections string).

Both MS Access bugs mentioned above exist in all versions of MS Access upto and including MS Access 2007. It is not currently known if Microsoft is planning to fix these issues and until it does, the above workarounds should be used.