"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
jrb3222Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Hi jrb3222,

Download the 'Find & Replace' utility from http://www.rickworld.com (free 30-day trial, $39 to buy), then do an application-wide search for 'Allergy'.  This add-in will search all application properties, tabledefs, query defs, everything for any instance of a string.  

I could make a couple of guesses here, but this approach will keep you somewhat sane, as you avoid the wild-goose chase.

Hope this helps.
-Jim
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
jkorzCommented:
have you tried making an access data project and deleting it through there? That will at least tell you if the error is coming from the server or the mdb.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jrb3222Author Commented:
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
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
jrb3222Author Commented:
Right, that's what I did.  There was no log to be viewed (I did have the "log to file" checkbox checked).
0
jrb3222Author Commented:
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
0
jrb3222Author Commented:
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
0
jkorzCommented:
triggers are actions that occur on either insert, delete or update of a record... they can do basically anything a stored procedure can do and are written in T-SQL

Any changes made to a table inside a trigger will look basically the same as regular SQL syntax (because regular sql syntax IS T-SQL)... DELETE FROM, INSERT INTO, etc...
the one big thing is that there are two non-persistant (that's the best word I can think of for it) tables that are accessable by triggers, INSERTED and DELETED, which contain the records which were... you guessed it... inserted or deleted from the table that the trigger is on

You can find the microsoft T-SQL reference here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_tsqlcon_6lyk.asp

triggers are really easy to learn, ESPECIALLY if you have ever written a stored procedure and they are very useful once you get to know them
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
0
jkorzCommented:
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
0
jrb3222Author Commented:
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

0
jkorzCommented:
To tell you the truth, I have never used either of the cascade checkboxes, mostly because I have never had a need for them. My suspicion though is that they are created through that. If you posted the triggers I could tell you for sure. I have never seen access create a trigger automatically though, although if it did, the cascading options would most likely be something that triggers would automatically be created for.  In your case I would put my money on you having the "Cascade delete related records" option checked (especially if you built everything in the sql db), otherwise somebody would have had to write a trigger from scratch.

I strongly suggest finding a tutorial online about triggers and learning the gist of them, they are the equivalent of an event handler in access or vb and are absoultely essential for a database professional. Triggers, stored procedures and user defined functions are the big three things that SQL server has that access doesn't and those three alone can justify moving an access db to SQL server if you are working with a complicated database. Once you know a little more about triggers, you will be able to recognize when they can be used to make your work much easier. Then you use that reference I posted above and do what you gotta do... (this is the method I used to learn it)

Hope that all made sense, good luck!
0
jkorzCommented:
just out of curiousity, did you find that the "Cascade delete related records" was the culprit?
0
jrb3222Author Commented:
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
0
jkorzCommented:
no problem, glad to help out
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.