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

Posted on 2012-08-24
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
    This is how you do it in T-SQL:
    DELETE  b
    FROM    BigTable b
            INNER JOIN DeleteList d ON b.FirstField = d.FirstField
                                       AND b.SecondField = d.SecondField

    Open in new window


    Author Comment

    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 31

    Accepted Solution

    delete from bigtable b
    where exists
    (select 1 from littletable l
     where b.ifield = l.field1
       and b.field2 = l.field2);
    LVL 6

    Assisted Solution

    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

    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.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now