Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Linked Table Error (2000)

    I am trying to link tables in a SQL Server 2000 database to an Access 2000 database. Most of the tables link OK and they all appear as linked tables in the Tables section of the database window. However, some of them, when I double click on them to open them, give an error message like this:

    Could not execute query; could not find linked table.
    [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name 'dbo.stkmaster. (#208)
    [Microsoft][ODBC SQL Server Driver][SQL Server] Statements could not be prepared. (#8180)

    Then a grid opens with the expected field names but all the cells filled with '#Name?'.

    I've looked at the connection strings for the tables (table design then right click title bar and select properties) and they look the same whether the tables open OK or not:

    This one works
    ODBC;DRIVER=SQL Server;SERVER=10.0.0.20;UID=KPOS;APP=Microsoft Open Database Connectivity;WSID=C046;Network=DBMSSOCN;Address=10 .0.0.20,1433;TABLE=dbo.stklabel

    This one doesn't:
    ODBC;DRIVER=SQL Server;SERVER=10.0.0.20;UID=KPOS;APP=Microsoft Open Database Connectivity;WSID=C046;Network=DBMSSOCN;Address=10 .0.0.20,1433;TABLE=dbo.stkmaster

    I've tried deleting the linked tables and relinking, but without success.

    I'm no SQL Server expert but I've gone to the server and used Query Analyser to do a SELECT * from one of the offending tables and it looks OK.
    Whether the table contains data or not does not seem to matter, some of the ones that work contain data and some do not.

    Can anybody help?

    Ian

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,497
    Thanks
    3
    Thanked 42 Times in 42 Posts

    Re: SQL Linked Table Error (2000)

    This isn't a problem I've seen before - so I'm about as stumped as you. The only possibility I can think of is some sort of special field being used that Access doesn't know what to do with. And those are pretty rare - by chance do some of the tables contain a SQL timestamp?
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Linked Table Error (2000)

    I think I've solved it. The error message lead eventually to a Knowledge Base article about problems with Access 2000 and file DSNs. The actual problem wasn't exactly the same but I switched to a system DSN anyway, and now I can open the problem tables.

    I started to use file DSNs after someone pointed out that they make managing your connections easier. However, there seem to be problems using them with Access. Are there any other pros and cons should be condidered when selecting a DSN type?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •