Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Compare items in two tables and delete the ones found in the first not in the second.

    I know how to set up a query that compare "Table1" with "Table2" for the purpose to list the records in "Table1" not found in "Table2". I then want to delete that list so that "unmatched" records will not be in "Table1" any more. It looks like the insert. The query is read only so no use trying to delete these records. Is there a way to delete those.Click image for larger version. 

Name:	Comparing.png 
Views:	37 
Size:	72.7 KB 
ID:	41183

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    If the query would be comparing two tables it should be updatable (meaning you could use it to delete the un-matched records). I assume you actually compare queries and some might not be updatable. The easiest way to do this would be to change the query into a make-table query (you don't need all the fields, just the unique ids) then create a second delete query in which you bring the newly created (by the unmatched query) temp table and join the Ids to the source table in which you want to delete the records.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I have a module where I can delete all records from a table as below. It works well. I am trying to delete it the same way but I want to delete all records from a query. Should this same VBA work on a query, or is there a change. Let us say the query name is QryJournalsub, how should the VBA look?

    Function UnbJournalDel()
    DeleteRecords = "Delete * From TblJournalSub"
    CurrentDb.Execute DeleteRecords
    End Function

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Yes it should (the last line should be CurrentDb.Execute DeleteRecords, dbFailonError) as long as the QryJournalsub query is updateable.

    See
    https://www.fmsinc.com/MicrosoftAcce...ble/index.html for more details.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    The first image shows how I put it in the module, the second shows the error message. The VBA on the form is correct otherwise I would not have got the message.
    Click image for larger version. 

Name:	Compare2.png 
Views:	26 
Size:	88.2 KB 
ID:	41280Click image for larger version. 

Name:	Compare.png 
Views:	26 
Size:	122.9 KB 
ID:	41281

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Yes, it seems right (other then missing Option Explicit and declaring the variable DeleteRecords as String), but q04CompareTablesDelete01 is probably not updateable, meaning you cannot use that query to edit, delete or add records. If you open that query as a regular Select query, is the * navigation button on the bottom enabled or not (grayed out like in your first picture)? What happens if you open the query and try to manually select and delete a record? There are many ways in which a query can become non-updateable (explained in the link in post #4). So to go around this you need to use a temporary table to store the unique IDs (primary keys) for the records you want to delete then create a new delete query that simply joins the target table to the temp one on those IDs.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Here is another link giving additional reasons why queries may not be updateable http://allenbrowne.com/ser-61.html
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    The query is updateable. I understand that it has to be. When one sets up a "find unmatched query" through the wizard the query seems to be not updateable. The query I am trying to delete the records from is updateable.

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    we are seeing everything except the sql code to your queries. It sounds like you are trying to delete records from or via a query rather than a table. Have you tried to do it manually? i.e. open q04comparetables... and delete one or more of the rows?

    Or perhaps you need to link the table to this query on an ID

  10. #10
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I want to learn if a module that is activated by a form can delete records in a query, and if it can, what is the code. I can give a long explanation why, if you want. I know how to do it manually.

  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the code you provided is correct in so far as it goes so the error message is to do with your query.

    Only issue with your code is

    a) you do not have option explicit at the top of your module (below option compare database)
    b) you have not declared DeleteRecords as a string - unless you have declared as a public variable in another module

    but neither of those issues will generate the error

  12. #12
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I added option explicit, the error below shows now when the form is opened.
    Click image for larger version. 

Name:	Compare4.png 
Views:	22 
Size:	51.3 KB 
ID:	41288

  13. #13
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Here is the query. Updateable. Want to delete the two records.
    Click image for larger version. 

Name:	Compare3.png 
Views:	22 
Size:	26.1 KB 
ID:	41289

  14. #14
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I linked two tables in this query. There are more fields in the tables. Does it make a difference if only two fields show in the query, or should all of them show? I want these two records to be deleted from the table. If I am going to explain it will take up your precious time.

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 07-02-2018, 09:40 AM
  2. I've found duplicates but how do I delete?
    By rocktap in forum Queries
    Replies: 6
    Last Post: 04-20-2015, 07:08 PM
  3. Delete based on compare
    By hdawn in forum Queries
    Replies: 3
    Last Post: 03-28-2014, 12:09 PM
  4. Replies: 12
    Last Post: 04-25-2013, 01:32 PM
  5. Replies: 1
    Last Post: 04-29-2010, 05:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums