Saturday, June 23, 2007

Error 3146: ODBC Call Failed

When you first create a link to an ODBC data source, Access will memorize the underlying table design, but it will not automatically update itself if the table design in changed; resulting in Error 3146 ODBC Call Failed.

This is in sharp contrast to the behavior when linking to external native Access tables - Access will automatically update the table definition, so no action is required when the design of the linked table is changed.

To update links to ODBC data sources, in Access 2002 or newer you can right-click on any linked table and then click on Linked Table Manager. This displays a list of all linked tables and their locations. Simply check-mark the table(s) to be refreshed and click OK. Access will then update the memorized table designs. (In older Access versions, the Linked Table Manager is found on the Tools menu.)

One caveat - you cannot use this method if you are trying to relink to an ODBC data source that uses a different driver. For example, if you have a DSN that points to a SQL Server 2000 database using the SQL Server provider, and you change the DSN to point to a SQL Server 2005 database using the SQL Native Client; this method will not work - Access will not recognize the new connection. In that case, you will need to actually delete the linked tables and then re-link using your new DSN.