?
Solved

"Could not find linked table"

Posted on 2005-03-31
16
Medium Priority
?
946 Views
Last Modified: 2009-07-29
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
0
Comment
Question by:jrb3222
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 4
16 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1000 total points
ID: 13673643
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13673654
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
 
LVL 8

Assisted Solution

by:jkorz
jkorz earned 1000 total points
ID: 13673722
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:jrb3222
ID: 13673762
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13673785
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
 

Author Comment

by:jrb3222
ID: 13673804
Right, that's what I did.  There was no log to be viewed (I did have the "log to file" checkbox checked).
0
 

Author Comment

by:jrb3222
ID: 13674517
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
 

Author Comment

by:jrb3222
ID: 13674568
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
 
LVL 8

Assisted Solution

by:jkorz
jkorz earned 1000 total points
ID: 13674668
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13674791
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
 
LVL 8

Expert Comment

by:jkorz
ID: 13674866
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
 

Author Comment

by:jrb3222
ID: 13675051
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
 
LVL 8

Assisted Solution

by:jkorz
jkorz earned 1000 total points
ID: 13675258
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
 
LVL 8

Expert Comment

by:jkorz
ID: 13683451
just out of curiousity, did you find that the "Cascade delete related records" was the culprit?
0
 

Author Comment

by:jrb3222
ID: 13683559
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
 
LVL 8

Expert Comment

by:jkorz
ID: 13683651
no problem, glad to help out
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question