Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010
    Posts
    57

    Unanswered: Access linked table "retains" old target after server table renamed

    I've run into a most peculiar problem.

    I have an Access 2007 database with linked tables to a SQL Server 2008 R2 server.

    One of my linked tables pointed to server table [dbo.MyTable]. I recently moved that table to a new schema so it is now [fnc.MyTable].

    I deleted the linked table in Access and recreated it pointing to the new server table. Contents of the tables display fine (SELECT). I can add records (INSERT), and delete records (DELETE). However, when I try to change a record (UPDATE), Access tells me that:

    "Could not execute query; cold not find linked table. [Microsoft][OBDC SQL Driver][SQL Server]Invalid object name 'dbo.MyTable'. (#208)"

    So it looks like access, for UPDATE queries (and just UPDATE!) somehow remembered the previous name of the server table.

    I've tried:

    •Recreating the linked table using DSN links (via VBA) which is how I normally create the links
    •Creating the linked table using a DSN
    •Deleting the linked table and doing a compact and replace
    •Removing the "Timestamp" column in the table
    None of these have made a difference. Somehow Access is trying to point to the old table for UPDATE queries.

    I'm open to suggestions, as I'm at a loss of what to do now.

    Thanks,
    Carlos

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Is the table linked using a trusted connection or a specific user name and password?

    2. What are the roles (defined rights and permissions) of the connected user in the fnc schema?

    3. What's in the Connect property of the TableDef object for the linked table? You can check with:
    Code:
    Sub tdfconnect()
    
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        
        Set dbs = CurrentDb
        Set tdf = dbs.TableDefs("linked_table_name")
        Debug.Print tdf.Connect
        Set tdf = Nothing
        Set dbs = Nothing
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Aug 2010
    Posts
    57
    Head slap moment. This was not an Access problem at all. The SQL table in question had a trigger, and within that trigger was coded the name (and schema) of the table. When the table was moved, the trigger was not recoded. It wasn't Access trying to save to the wrong schema.table, it was SQL Server itself through the trigger.

    Doh!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Thanks for the info!
    Have a nice day!

Posting Permissions

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