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.
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.
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
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)
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.
Probably could with a WHERE EXISTS subquery. Perhaps an SQL guru can jump in here and be more specific.7. Can I add a where function that will delete records in TableA where a field is the same in both.
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
You need to dim that variable.
, just like InsertInto.Code:DIM DeleteRecords as string
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
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.
Thread 11 is a reply to thread 9.
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.
This solution uses a subquery.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)));
qryDeleteList is a select query for tblDeleteList.
tblDeleteList could just as well have been used in the SQL and eliminated the need for qryDeleteList.