"ODBC Call Failed" error when linking to SQL Server

[Arthur Medzhitov]

Hi.

I am trying to create a link to an external table in
Access 97 database. External table is located in SQL
Server database. I use SQL Server authentication and a DSN
that points to that SQL Server database.

At first the connection establishes and the linked table
opens in a datasheet view, but all fields have #Name?
value, and error message "ODBC Call Failed" is displayed.
When I attempt to open this table again, it does not open
at all and I get a different error message:

Line1: Incorrect syntax near '.'

This behaviour is not very consistent - this works fine on
some other machines.

I appreciate any help regarding this issue.

Thanks,
Arthur

 

[Joe Fallon]

Check the table for invalid characters in the name or columns.
(if they are all just letters with no spaces you are fine - if anything else
check it closely.)

Do all tables have PKs?

Is it all tables or just a few?

Who is the owner? Is it dbo?

 

[Arthur Medzhitov]

Joe, thanks for your reply.

1) Table names and columns are all valid characters, one
word, no spaces, etc. (Also, links work fine from another
PC with Access 2000).
2) All tables have primary keys.
3) This happens only for some tables. They have at least
one thing in common - a field of text type.
4) All tables belong to DBO.

Thanks,
Arthur

 

[Joe Fallon]

Try writing a query on the linked table (do NOT open the linked table
directly.)
Omit the Text (memo) field from the query.

Does it work?

 

[Guest]

I have had this problem, if one of your key fields is
decimal it seems to cause this problem. Convert it to int
and the table should then be seen in access. Unfortunately
I do not know the reprecussions in terms of your front-end!

Hope this has been of help.