Unanswered: Invalid Primary Key error during table linking
Something strange has happened to my table. I used Enterprise Manager today to delete 3 columns. When I went to re-link the table using Access Linked Table Manager, it gave me an error. I then deleted the link to the table, and tried to Link it again using 'Get External Data---Link Tables'. I am getting an error (no surprise!):
" 'dbo.tblSpaceUse.PK_RoomID' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long".
When I go into Enterprise Manager to 'manage Indexes' on the table, it shows me that the existing index is in fact dbo.tblSpaceUse.PK_RoomID.
About a month ago, I had to rename the index, because it had been pointing to the wrong table. The SQL I used to rename it (in Query Analyzer) is:
EXEC sp_rename 'dbo.tblSpaceUse.PK_RoomID', 'tblSpaceUse.PK_RoomID', 'INDEX'
I have been using the table successfully since then, until today. I have not done anything with the index; the only change I attempted was to delete 3 columns (not related to the index). I do not think I have made any changes to the table since I renamed the index.
I tried to run the rename SQL again (a desperate attempt!) and get the error message:
Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 192
Either the parameter @objname is ambiguous or the claimed @objtype (INDEX) is wrong.
Any ideas on what went wrong and what I can do to fix it???