Failure of Linked table in Access...

[Guest]

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.

 

[Rick Brandt]

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]

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.

Another thing. SQL Server allows field names that Access does not. This
can cause grief in links as well so see if any of the field names as defined
on the server are unusual. Don't rely on the names you see in the link.

 

[Guest]

Rick, thanks for your suggestions. I checked both the datatypes and the
field names and don't see anything unusual - no keywords or special
characters except for underscores. The only datatype I've used in the list
you provided is the numeric type which I have used several times. It seems
like the best choice for the fields where it is used.

If you have any other ideas, please let me know.

 

[Rick Brandt]

Rick, thanks for your suggestions. I checked both the datatypes and
the field names and don't see anything unusual - no keywords or
special characters except for underscores. The only datatype I've
used in the list you provided is the numeric type which I have used
several times. It seems like the best choice for the fields where it
is used.

If you have any other ideas, please let me know.

See how your Numeric types are being mapped in the design view of the link.
This might not be a problem in newer versions since Jet 4.0 added the
Decimal type, but in Access 97 a Numeric or Decimal will be mapped to either
Single, Double, or Text depending on the length and precision used. If it
gets mapped to Single or Double then the value in Access can get rounded
compared to the value on the server and this can cause problems, especially
if the field is part of the Primary Key.

The only trouble with that theory is that I have only seen this problem
manifest itself two ways. One is to show #Deleted in the table datasheet
instead of the data and the other is when editing you get the error "Another
user has changed this record since you began editing it". I have never
seen it cause #Name.

 

[Guest]

Rick,

It turns out that the problem was with a dynamic sql statement I was
executing via ADO prior to opening the form. The dynamic sql called a custom
function in one of my Access module which in turn performed a second SQL
query via a disconnected ADO recordset. The second query/function used a
value from the first query. The second query was selecting from a totally
different table than the 1st. Also, the 1st SQL statement was an Update
statement. In the end, the 1st query was blocking the second query and
eventually it all just timed out. I was able to remedy the situation by
setting the isolation level of the second connection object to
ReadUncommitted (db.IsolationLevel = adXactReadUncommitted).

A better remedy would probably been to use a join or a nested subquery in
the first SQL Update statement, but that would have messed up the table
driven nature of the update statement.

It sounds like you were on track with "Another user has changed this record
since you began editing it" but I just got an ODBC timeout instead of that
message.

The next time I open a table and see #Name? everywhere, I am going to look
for locking issues right off the bat. BTW, I used the Activity Monitor in
SQL Server Management Studio Express to identify what SQL Server processes
were blocking others. I filtered the Process Info view by my SQL Server User
and the Application (Microsoft Office 2003).

Thanks again for your suggestions!