Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


How to delete records in one table based on another table - with 2 fields to link

Posted on 2012-08-24
Medium Priority
Last Modified: 2012-09-05

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):

DELETE BigTable.*
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>.)


Question by:SingsBass
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38330574
This is how you do it in T-SQL:
FROM    BigTable b
        INNER JOIN DeleteList d ON b.FirstField = d.FirstField
                                   AND b.SecondField = d.SecondField

Open in new window


Author Comment

ID: 38331248
Thanks for the quick response.

I don't think I would ever have thought to try using table name aliases.  I'll certainly file that little trick away for future reference <grin>.

Unfortunately, even with the aliases, Access is still giving me the same error message (Could not delete from specified tables.).  The T-SQL code translates perfectly into something that can be viewed in Access' "query design" mode - but it won't run.

After some more searching (on the error message), I have just come across an MS KB article (240098) which suggests setting the UniqueRecords property of the Access query to Yes (equivalent of using the DISTINCTROW clause) - however, the article seems to only apply to Access 2000 (not 2003?), and only when using an .mdb/Jet environment.  So, I'm not sure it really applies here.  I suppose it wouldn't hurt too much to try it <shrug>.

I guess I'll fool around a bit more, now that I have this alias trick to try.  Maybe I'll end up having to move the "delete list" into the SQL database (rather than having it in an Access table), and run a T-SQL query (in the Management Studio, or via the OSQL tool, or similar) to get this whole thing to work.  It is beginning to "smell" like it might be a quirk of the mixed environment (i.e. Access and MS SQL).

If you (or anyone else) has some ideas in the meantime, by all means, chime in.  Otherwise, if I get this solved before I get any more suggestions, I'll come back and post the results/solution.


LVL 32

Accepted Solution

awking00 earned 800 total points
ID: 38332865
delete from bigtable b
where exists
(select 1 from littletable l
 where b.ifield = l.field1
   and b.field2 = l.field2);

Assisted Solution

fhlio_admin earned 800 total points
ID: 38335206
I think I would move the data to SQL Server and run it in T-SQL.

However, the issue could be several issues, one of which you touched on.  Primarily, the error seems to deal with permissions.

Permissions can be on the user, or within Access.  Access will ask for fields on linked tables if it is confused.  Did it pop up such a box?

If it is not a permissions issue and you want to do it in Access, you could run it through a recordset loop and delete the items by primary key like follows:

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset( _
    "SELECT DISTINCT BigTable.FirstField, BigTable.SecondField " & _
    "FROM " & _
        "BigTable " & _
        "INNER JOIN DeleteList ON " & _
            "(BigTable.FirstField = DeleteList.FirstField) " & _
            "AND (BigTable.SecondField = DeleteList.SecondField);" _

Do While Not rst.EOF
    Docmd.SetWarnings False
    Docmd.RunSQL _
        "DELETE BigTable.* " & _
        "FROM BigTable " & _
        "WHERE " & _
            "(BigTable.FirstField = " & rst!FirstField & ") " & _
            "AND (BigTable.SecondField = " & rst!SecondField & ");"
    Docmd.SetWarnings True

Open in new window

If BigTable.FirstField or BigTable.SecondField are not text/string fields, then they will need qualifications around them in the Docmd.RunSQL statement.

Author Closing Comment

ID: 38368137
Sorry for my delay - I got pulled onto a different (emergency) project, and couldn't get back to testing/resolving this issue until now.

I want to thank "acperkins" for his first suggestion.  Although it did not actually work under my present circumstances, I did learn something from it (i.e. using aliases on JOINs).

The other two suggestions which were submitted (by "awking00" and "fhlio_admin") did work during my tests - so I have marked them both as answers, and split the points.  The only reason I gave "awking00" the "best solution" status in this case, is because it is a simpler solution and would be the most efficient of the two on large datasets.  Having said that, the looping solution provided by "fhlio_admin" would certainly have its uses in cases where other manipulations/validations were required - so I'm definitely going to file that one away.

For those who are reading this, you may be interested to know that the simplest solution of all turned out to be following the suggestion outlined in MS KB 240098 <shrug> - using the UniqueRecords property (DISTINCTROW clause).  Although that article was apparently targeted at Access 2000, I can (now) confirm that it works just as well for Access 2003.  (I suspect it would also apply to later versions of Access - 2007?, 2010? - but, at this point, I cannot confirm that 100% as I have not had time/cause to test it in those environments.)

Thanks again to all who contributed.



Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

578 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