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.
•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.
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.