Results 1 to 14 of 14
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Delete Records in a QUERY using a module.


    I have asked this in a previous thread. Can Code from a module delete records from a QUERY. Not a table, I know how to create Delete queries, and to do it manually. Yes or No. Thank you.

  2. #2
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I understand that the query must be updateable. Below is what I have tried.

    Function QryJournalDel()
    DeleteRecords = "Delete * From QryJournal"
    CurrentDb.Execute DeleteRecords
    End Function

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    All data is in tables. Queries don't have data,
    you can run delete queries from a module using docmd.openquery.

    but if you join tables in the query,you prob can't run the delete.
    for an example of how to delete with joined tables, use the query wizard an create a FIND DUPLICATES query.
    edit the design,
    You see it uses a sub query with the IN in the criteria. Make your delete query just like that.
    like:
    delete the * in table1 where table1.field IN (select key from table2)

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Quote Originally Posted by Perfac View Post
    I understand that the query must be updateable. Below is what I have tried.

    Function QryJournalDel()
    DeleteRecords = "Delete * From QryJournal"
    CurrentDb.Execute DeleteRecords
    End Function
    And what happened? Is there a problem?

  5. #5
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thanks Dave. I will test it again and reply to you what happens. What is your answer? Can VBA from a module delete records in a query?

    As a second option I assume the following.
    1. I used the "Find unmatched query" action to return a query which lists records in TableA not in QueryA.
    2. The query is read only, so I append the Unmatched list to a TableB.
    3. I create a new query which links TableA and TableB, showing only the Unmatched records.
    4. If I delete it manually in the query, all is well. The aim was to remove Unmatched records from TableA.
    5. I assume there is another clever way. Ranman maybe tried to go there.
    6. Since TableB holds the records I want to delete in TableA, is there VBA or update query that can do that?
    7. Can I add a where function that will delete records in TableA where a field is the same in both.

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    7. Can I add a where function that will delete records in TableA where a field is the same in both.
    Probably could with a WHERE EXISTS subquery. Perhaps an SQL guru can jump in here and be more specific.

  7. #7
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Click image for larger version. 

Name:	Compare5.png 
Views:	23 
Size:	42.9 KB 
ID:	41393
    Hi Dave, thank you. This is the error it shows. I have many the exact same and they work with tables.

  8. #8
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Example of one that works with tables. I wondered about the line "Dim InsertInto As String"

    Function DeleteWeek()
    Dim InsertInto As String
    '---------------------------------t01Week---------------------------------------------
    DeleteRecords = "Delete * From t01Week"
    CurrentDb.Execute DeleteRecords
    InsertInto = "INSERT INTO t01Week (WekID052, FstDayOfWeek052) VALUES (0,1)"
    CurrentDb.Execute InsertInto
    DeleteRecords = "Delete * From t01Week"
    CurrentDb.Execute DeleteRecords
    End Function

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You need to dim that variable.
    Code:
    DIM DeleteRecords as string
    , just like InsertInto.

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Or omit that variable completely as it really isn't necessary...

    CurrentDB.Execute "DELETE * FROM …"

    If you find the non updateable error occurs again, try selecting unique records using
    CurrentDB.Execute "DELETE DISTINCTROW * FROM …"

    You might find my extended article on synchronising data worth reading. It contains many different methods.
    See http://www.mendipdatasystems.co.uk/s...a-1/4594514001
    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

  11. #11
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I added that. Message "Could not delete from the specified tables", even though it is a query. Do you know how an expression will look in VBA that can do the following.
    All records found in TableB delete them if they are TableA. They will be.

  12. #12
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thread 11 is a reply to thread 9.

  13. #13
    Perfac's Avatar
    Perfac is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thank you Colin. I put in too many hours in the past week. Brain is a bit on strike. Thanks for your help. We will study tomorrow. But I just found a "home made" solution here.

  14. #14
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code:
    DELETE tblMembers.Members_PKFROM tblMembers AS M
    WHERE (((M.[Members_PK]) In (Select Members_PK From qryDeletelist D 
    WHERE M.Members_PK = D.Members_PK)));
    This solution uses a subquery.
    qryDeleteList is a select query for tblDeleteList.
    tblDeleteList could just as well have been used in the SQL and eliminated the need for qryDeleteList.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Delete query to delete records that meet criteria
    By neill_long in forum Queries
    Replies: 1
    Last Post: 06-11-2018, 02:41 PM
  2. Replies: 4
    Last Post: 12-13-2016, 09:48 AM
  3. Replies: 6
    Last Post: 01-13-2015, 04:54 PM
  4. How to Delete VBA Module
    By tgwacker in forum Modules
    Replies: 2
    Last Post: 12-30-2013, 09:36 PM
  5. Replies: 2
    Last Post: 01-24-2012, 02:16 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