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.
6 comments:
I got the problem but only if I want to export to excell, other than that it working fine. You know what is the reason?
Loann
If you have a table that needs to be relinked due to a change in the underlying table design, it will not generate the ODBC error until you actually try to access the data in that table. Go to the database window & try to open each table that is used in the export, that might tell you which table has a problem.
Thanks! This was what I needed to know to fix my error!
I've an Access application with SQL back. I dynamically connected the sql server with DSN less mechanism. The problem is when any of my client who uses my solution get disconnected (due to power failure) from server, the application shows ODBC connection failed but when power comes up it'll not automatically reconnect even if I want to try the refresh link?
That behavior matches my experience - once Access encounters an ODBC error it cannot recover - you have to close Access and then re-open to clear the error condition.
I have a similar situation: in some systems, when I run my application it shows the 3146 error, but it still opens. If I open a form, it shows the message again, but it still does open and allows me to edit, but the message keeps showing up.
Here's some of the scenario I have: I'm using two databases: one for "Live" environment and one for "Play". In the first form, the user logs on and selects the Environment, so I relink the tables to the correct SQL database.
Funny thing is that it doesn't do the same in all machines... :-(
Any ideas/help is greatly appreciated.
Post a Comment