"Could not find linked table"
Hello!
I'm working with an Access 2003 frontend with links to tables via ODBC to a SQL Server 2000 backend. I recently deleted some of the tables which were left over from a long time ago which have not been used recently, had little or no data in them, and never really were used all that much anyways. Call it spring cleaning, I guess.
Anyways, I deleted a table called "Allergy" from the backend and the link to it in the frontend. Now, when I try to delete a row (and perhaps if I even try to edit a row) in a table which I assume used to be linked to the table Allergy, I get the following error message:
"Could not execute query; could not find linked table". This is referred to as "(#208)".
I have looked at a Knowledge Base article which references this error, but it does not seem to apply to my exact circumstance. I have tried creating a new blank database, relinking all of the existing tables to the backend and then importing all forms/queries/modules, but this does not appear to have had any effect. I have also tried re-creating the .dsn which is used to make the connection to the backend, but this does not appear to have any effect. I have looked at the system tables to try to see if there was still some reference to the table Allergy in MSysObjects or MSysRelationships, but there does not appear to be any such reference.
All that seems to work is to re-create the old tables in the backend itself. i.e. I can create a new table in the backend called "Allergy", give it a primary key and just let it sit there completely empty and then I can delete the row from the other table without issue. Note that I did not have to add a link to the table in the frontend - I only had to re-create the table in the backend.
For the time being this works. This is a workaround, however, and not a solution, which is where I hope all of you can help! I'd love to be able to delete the Allergy table and the other three tables for which I get the same error when I try to delete them from the backend (all four tables are empty except for a key).
Thanks very much!
Jamie
I'm working with an Access 2003 frontend with links to tables via ODBC to a SQL Server 2000 backend. I recently deleted some of the tables which were left over from a long time ago which have not been used recently, had little or no data in them, and never really were used all that much anyways. Call it spring cleaning, I guess.
Anyways, I deleted a table called "Allergy" from the backend and the link to it in the frontend. Now, when I try to delete a row (and perhaps if I even try to edit a row) in a table which I assume used to be linked to the table Allergy, I get the following error message:
"Could not execute query; could not find linked table". This is referred to as "(#208)".
I have looked at a Knowledge Base article which references this error, but it does not seem to apply to my exact circumstance. I have tried creating a new blank database, relinking all of the existing tables to the backend and then importing all forms/queries/modules, but this does not appear to have had any effect. I have also tried re-creating the .dsn which is used to make the connection to the backend, but this does not appear to have any effect. I have looked at the system tables to try to see if there was still some reference to the table Allergy in MSysObjects or MSysRelationships, but there does not appear to be any such reference.
All that seems to work is to re-create the old tables in the backend itself. i.e. I can create a new table in the backend called "Allergy", give it a primary key and just let it sit there completely empty and then I can delete the row from the other table without issue. Note that I did not have to add a link to the table in the frontend - I only had to re-create the table in the backend.
For the time being this works. This is a workaround, however, and not a solution, which is where I hope all of you can help! I'd love to be able to delete the Allergy table and the other three tables for which I get the same error when I try to delete them from the backend (all four tables are empty except for a key).
Thanks very much!
Jamie
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Jim Horn
If you have someone else's budget dollars to spend, you can also purchase Speed Ferret ($199), which will search your Access AND SQL Server databases in one search.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I ran the Find & Replace program, but as far as I can tell it didn't come up with any results.
I haven't tried deleting it through the .adp yet - I'll give that a shot jkorz.
Thanks again,
Jamie
I haven't tried deleting it through the .adp yet - I'll give that a shot jkorz.
Thanks again,
Jamie
When you hook it up to your Access app and open it, type 'Allergy' in the Find dialog. Make sure the buttons Tables, Queries, Forms, ... are all pressed, make sure the 'Find Only, No Replace' checkbox is checked, and hit the Find button. When it's done, hit the 'View Log' button and it will display a report for you.
ASKER
Right, that's what I did. There was no log to be viewed (I did have the "log to file" checkbox checked).
ASKER
Ok, I tried deleting a row from the .adp file and got a similar error message, which simply said (in what was essentially just a message box):
Invalid object name 'Allergy'.
There was no button to press for help or anything like that, just that message in a pop-up box.
Incidentally, if I create a table called Allergy, but don't put a correctly named field in it, then it gives me a different error message. In this case the field that appears to have been linked was named "Subject#" - I know this is a poor naming scheme, but it wasn't mine so please don't yell at me for it :). If instead I simply give the table a field called "sub" or anything else aside from "Subject#" then error message I get is:
ODBC--Delete on a linked table 'Subject' failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Subject#'. (#207)
This making any sense to anyone?
Jamie
Invalid object name 'Allergy'.
There was no button to press for help or anything like that, just that message in a pop-up box.
Incidentally, if I create a table called Allergy, but don't put a correctly named field in it, then it gives me a different error message. In this case the field that appears to have been linked was named "Subject#" - I know this is a poor naming scheme, but it wasn't mine so please don't yell at me for it :). If instead I simply give the table a field called "sub" or anything else aside from "Subject#" then error message I get is:
ODBC--Delete on a linked table 'Subject' failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Subject#'. (#207)
This making any sense to anyone?
Jamie
ASKER
Ahh!
I ran the Find & Replace program on the .adp and found that there were a number of triggers associated with the Subject table (which I've been trying to delete rows from). I've never actually worked with triggers before (and would never have guessed that there would be any in this database - the person who designed it knew even less then I do about DB's in general then I do), so I guess I'll do some reading up on them before I try to go editing them, but once I decide what to do with this I'll come back and attempt to award points where possible assuming that the problem is fixed.
If anyone feels like giving me a quick rundown on triggers or why this might cause a problem such as this, that'd be cool as well.
Thanks again everyone!
Jamie
I ran the Find & Replace program on the .adp and found that there were a number of triggers associated with the Subject table (which I've been trying to delete rows from). I've never actually worked with triggers before (and would never have guessed that there would be any in this database - the person who designed it knew even less then I do about DB's in general then I do), so I guess I'll do some reading up on them before I try to go editing them, but once I decide what to do with this I'll come back and attempt to award points where possible assuming that the problem is fixed.
If anyone feels like giving me a quick rundown on triggers or why this might cause a problem such as this, that'd be cool as well.
Thanks again everyone!
Jamie
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Good deal...
Speed Ferret is available at http://www.moshannon.com, and again does Access .mdb plus SQL Server db's in one search.
Good luck with your project.
-Jim
Speed Ferret is available at http://www.moshannon.com, and again does Access .mdb plus SQL Server db's in one search.
Good luck with your project.
-Jim
before you go spending $199 to fix a simple problem, if you can't figure out where the problem is, post the trigger(s) attached to the table that are FOR DELETE (if you can't figure that out, post em all) and I am sure I can tell you where the problem is
ASKER
The syntax that the triggers are in seems to be very simple. I've got a moderate amount of experience with programming in general as well as with query languages in specific (though I wouldn't claim to be advanced in either...I tend to learn things as I need them), so it's all pretty easy to decipher. There are two sets of triggers on the Subject table, one appears to be for updates and the other appears to be for deletes.
Are triggers created automatically when defining relationships between fields in tables? Or would these have to have been added manually? For example, do triggers have anything to do with the "Cascade update related fields" or "Cascade delete related records" checkboxes in the relationship properties window? If I check the cascade update or cascade delete, do I also need to have triggers? Or were these created automatically? Or are these completely redundant if the settings are correct in the relationship window?
Thanks again,
Jamie
Are triggers created automatically when defining relationships between fields in tables? Or would these have to have been added manually? For example, do triggers have anything to do with the "Cascade update related fields" or "Cascade delete related records" checkboxes in the relationship properties window? If I check the cascade update or cascade delete, do I also need to have triggers? Or were these created automatically? Or are these completely redundant if the settings are correct in the relationship window?
Thanks again,
Jamie
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
just out of curiousity, did you find that the "Cascade delete related records" was the culprit?
ASKER
It didn't appear to be...
Only a couple of the tables had that option checked, and they were not the problem tables (they also did not have similar Triggers set for them). I'm guessing that the guy that designed this database just put the triggers in before he really knew what he was doing. All the triggers appeared to do was to ensure that the keys were not being duplicated, which can be done using unique indexes anyways and by setting PK and FK values appropriately. Eventually I'll go through the rest of tables and try to make sure all of that stuff is set up properly, but for the time being I'm satisfied with not having to deal with those errors anymore.
Thanks a ton for your help, I appreciate it!
Jamie
Only a couple of the tables had that option checked, and they were not the problem tables (they also did not have similar Triggers set for them). I'm guessing that the guy that designed this database just put the triggers in before he really knew what he was doing. All the triggers appeared to do was to ensure that the keys were not being duplicated, which can be done using unique indexes anyways and by setting PK and FK values appropriately. Eventually I'll go through the rest of tables and try to make sure all of that stuff is set up properly, but for the time being I'm satisfied with not having to deal with those errors anymore.
Thanks a ton for your help, I appreciate it!
Jamie
no problem, glad to help out