Re: Failure of Linked table in Access...

jhewgley wrote:
I have an Access database with many (15-20) tables linked to tables
in and
SQL Server database via an ODBC DSN. An application has been built
in the Access database with various forms and reports. When opening
the most used form, upon occasion, I receive an "ODBC--call failed"
error and all subsequent queries on the PROJ table fail. All other
tables are ok. If I open the table in access to view the data, every
cell is filled with "#Name?". Does anybody recognize this problem.
Any ideas of what I should do?

Here is some more info that may help with troubleshooting.

1.) The table is one of many tables that are linked from SQL Server
into the same Access database. All tables come from the same SQL
Server database.
2.) The problem is being experienced with the same table (PROJ) in two
different Access databases pointing to two different SQL Server
databases (test and system test).
3.) None of the other linked tables in the database ever exhibit this
problem.
4.) The problem has been reproduced on more than one workstation.
5.) The problem never occurs on the first access to the table -
always on subsequent queries and the number of queries that can be
performed before the error begins to occur is indeterminate.
6.) Once the problem occurs, no further queries may be performed on
the
table until the Access database is closed and reopened.
7.) The problem occurs regardless of whether we use the standard SQL
Server ODBC driver or the SQL Native Client driver when creating the
DSN.
8.) The link has been deleted and recreated within the Access
database and this has not provided any relief.

Any DataTypes in that table that don't map exactly to Access/Jet DataTypes,
especially those in the primary key fields? BigInt, Numeric, Decimal, Text,
DateTime, etc., can sometimes cause problems. Often adding a Timestamp
field to the table helps.

Usually for the problems I'm describing one sees #Deleted rather than #Name
though.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com



.