How to delete records in one table based on another table - with 2 fields to link
Posted on 2012-08-24
In MS Access (happens to be 2003 in this case, but that shouldn't really affect the underlying question here), I have a large "master" table, and a 2nd small table which contains information relating to that large table. To be pefectly correct ... the large table happens to be an ODBC linked table to a Microsoft SQL 2005 database, and the small table is a "local" table in the Access database.
The small local table contains a list records which I want to delete from the big linked table. Specifically, it contains the the key field pair values of the records I want removed from the large table.
What I have been trying, are variations on the following (in Access):
FROM BigTable INNER JOIN DeleteList ON (BigTable.FirstField = DeleteList.FirstField) AND (BigTable.SecondField = DeleteList.SecondField);
It should be obvious, but in case not ... "BigTable" is the large master table in the MS SQL 2005 database, and DeleteList is the local Access table containing the key values of the records I want to remove from the large master table. In the BigTable, the two fields FirstField and SecondField are defined as its primary key.
Nothing I try seems to work (I get error messages such as "Could not delete from specified tables").
I've done this type of thing many, many times ... but, only where there was a single unique field to link between the two tables (and in those cases, I typically use IN or EXISTS syntax, rather than JOINs). But, this is my first time trying to do the same thing where more than one link/field is required - and I don't think the IN or EXISTS will work for that (at least, I can't think of the syntax to do it).
Unfortunately, I'm not having success (yet) with the JOIN, either <smile>.
If someone could point me in the right direction, I would certainly appreciate it. (I suspect I'm going to be embarrassed and kick myself when I find out how easy it is ... but that's okay, I don't bruise too easily <LOL>.)