Linked Tables Giving Error Message

[18ck]

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)

[pkstormy]

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.

[18ck]

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.