Results 1 to 3 of 3
  1. #1
    18ck is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2012
    Posts
    2

    Linked Tables Giving Error Message

    Hi all,
    I have an access database I am using for moving data around between databases and today I am getting an error which was not happening yesterday and I can't think what's happened.

    I set up some linked tables referenceing empty tables in a SQL Server database. When I click on these tables, I can see the table headings and no data because its empty. I run a query that puts data into this table, then another query that adds more. I click on the same linked table and I get an error message:
    Could not execute query; could not find linked table
    [Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name dbo.blahblah_table. (#208) [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (#8180)

    If I then delete all the data using SQL Server Management studio, I can open the linked table again and view the empty table.

    This occurs with brand new linked tables and with tables that I set up days ago and worked perfectly up until yesterday. It does not happen with two linked tables referencing an Oracle database. What it seems is that I can insert data into these tables and I can view them when empty but if there is any data in them then I can't view the contents or JOIN to them in any other update statements that would carry out further updates - and this last bit is the one that is preventing me from getting my work done. All

    Has anyone seen anything like this at all?
    Thanks

    Details:
    Windows XP - in a citrix environment, which is probably not helping
    SQL SERVER 9.05.0000, set to autocommit
    Access 2003
    Tables are fairly standard - numbers, dates, text fields containing short, coded values, no more than about 16 characters



    Things tried so far:
    Log out - log back on
    Switch to different computer, try there
    Complete restore of SQL Server database, recreate underlying SQL server tables using script
    Recreate the linked table
    Look at data in SQL Server (it is visible)

  2. #2
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    If possible, I would create a new ODBC DSN to link the tables and then link them into a new accdb file. Make sure when linking the tables that you check the box which says "Save Password".

    Also keep in mind that if there is another accdb which links the same tables but uses a different ODBC Name, this can cause symptoms where it works for one user, then doesn't, then after refreshing the linked tables it works again for that user but then doesn't work for another user (even if the ODBC Name is spelled or capitolized slightly different). Remember that anyone which links to those tables must have the exact same ODBC DSN name or you'll get mixed errors which can drive you crazy.

    This also assumes that you have the tables setup correctly with an identity field which can often cause issues if there isn't an identity type field in the table (with it set to auto-increment). Also try adding in a 'TimeStamp' defined field into the table. Some SQL Server db tables don't like being updated in certain environments if there isn't a timestamp defined field in the table. After I added a timestamp type field to the table, issues such as you described went away.

  3. #3
    18ck is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2012
    Posts
    2
    Quote Originally Posted by pkstormy View Post
    If possible, I would create a new ODBC DSN to link the tables and then link them into a new accdb file. Make sure when linking the tables that you check the box which says "Save Password".

    Also keep in mind that if there is another accdb which links the same tables but uses a different ODBC Name, this can cause symptoms where it works for one user, then doesn't, then after refreshing the linked tables it works again for that user but then doesn't work for another user (even if the ODBC Name is spelled or capitolized slightly different). Remember that anyone which links to those tables must have the exact same ODBC DSN name or you'll get mixed errors which can drive you crazy.

    This also assumes that you have the tables setup correctly with an identity field which can often cause issues if there isn't an identity type field in the table (with it set to auto-increment). Also try adding in a 'TimeStamp' defined field into the table. Some SQL Server db tables don't like being updated in certain environments if there isn't a timestamp defined field in the table. After I added a timestamp type field to the table, issues such as you described went away.
    Thanks very much for your full and detailed reply. Hm, I'm not sure about this though. The very first suggestion sounds possible and it may come to that - athough recreating the whole thing feels a bit last-resorty. As for the rest, I'm afraid I don't think any of them is the specific cause of my problem. Some of the tables do have identity columns and/or timestamps, some don't, but they all worked before and they all don't work now. The layout of the tables is defined by a supplier's import process so altering them at this late stage would be problematic. Refreshing or recreating the linked tables didn't change anything, which I think rules out the two-different dsn theory too.

    For now, I have a workaround: I am appending to the linked tables, then using a passthrough query to suck the data back into the accdb file to use as a reference in later queries. It's messy but at least it lets me move forward.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. DCount on a Query giving #Name? error
    By Huddle in forum Access
    Replies: 9
    Last Post: 06-20-2012, 11:40 AM
  2. Replies: 1
    Last Post: 03-25-2012, 01:53 PM
  3. Error message in tables!
    By sunshine in forum Access
    Replies: 1
    Last Post: 11-10-2011, 07:09 AM
  4. ODBC Linked tables Error
    By tpcervelo in forum Access
    Replies: 0
    Last Post: 02-10-2011, 01:40 PM
  5. NoData() still giving me an error.
    By cowboy in forum Programming
    Replies: 3
    Last Post: 04-08-2010, 12:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums