By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,438 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

Running a query in VB statement

P: 17
I am trying to delete duplicate records from a table. I have successfully created a query to find the duplicates. My problem is running the query in the following statement


SQLDeleteDuplicates = "DELETE " tblMoneyDue.*, tblMoneyDue.ID _
& "FROM tblMoneyDue WHERE (((tblMoneyDue.ID) = _
& "DLookUp("ID","qryListCopiesToDelete","ID" >1)))"


It is the DLookup part that is causing the problem! I am sure that I should not be using "" around ID and qryListCopiesToDelete but do not know where to go next.

Any help gratefully received
Oct 28 '08 #1
Share this Question
Share on Google+
6 Replies


P: 93
Hi

I normally use access query design view and once i'm happy with results I go to SQL view and copy paste the code (which I know is working) and make the adjustments in the VB code.

Hope it helps
Emil
Oct 28 '08 #2

P: 17
Hi, I ceated the query in query design and it worked fine! I copied and pasted the script into Visual Basics and used:

DoCmd.RunSQL (SQLDeleteDuplicates)

to run the script.

MS didnt like the "" quotes around the query, and started me wondering if I was using the wrong DoCmd or in fact what I should use around the query instead of quotation marks. Any help appreciated.
Oct 29 '08 #3

P: 93
Hi

You use the correct method.

The below one is incorrect is several places

SQLDeleteDuplicates = "DELETE " tblMoneyDue.*, tblMoneyDue.ID _
& "FROM tblMoneyDue WHERE (((tblMoneyDue.ID) = _
& "DLookUp("ID","qryListCopiesToDelete","ID" >1)))"

Try this I haven't tested it and I'm not sure if the double quote will work but should give an idea of what to check.

SQLDeleteDuplicates = "DELETE tblMoneyDue.*, tblMoneyDue.ID " _
& "FROM tblMoneyDue WHERE (((tblMoneyDue.ID) = " _
& "DLookUp(""ID"",""qryListCopiesToDelete"",""ID "" >1)))"

p.s. Try using INNER JOIN instead of DLookUp; you won't have any issues with that :)

Regards
Emil
Oct 31 '08 #4

P: 17
Thanks for the corrections, sadly the script still does not work.

I have tried your idea of "INNER JOIN" (in Query Design) using the following:

DELETE tblMoneyDueBP.*, tblMoneyDueBP.ID
FROM tblMoneyDueBP INNER JOIN qryListCopiesToDelete ON tblMoneyDueBP.ID = qryListCopiesToDelete.ID
WHERE (((tblMoneyDueBP.ID) Is Not Null));

This produced a message stating that "Could not delete from specified tables", another problem!!! Any help appreciated.

Many thanks
Oct 31 '08 #5

P: 93
Hi

I have created a table + query and used your DELETE script went to query design view clicked run and it worked.

I suspect your already try to change DELETE to SELECT to see if the query is selecting the proper data in your database?

I'm sorry but I cannot see a reason why it doesn't work. I will send you a private message with my email address if you want to send a sample of your db and I will be able to answer your question very quickly :)

Regards
Emil
Nov 4 '08 #6

P: 17
Hi

I have created a table + query and used your DELETE script went to query design view clicked run and it worked.

I suspect your already try to change DELETE to SELECT to see if the query is selecting the proper data in your database?

I'm sorry but I cannot see a reason why it doesn't work. I will send you a private message with my email address if you want to send a sample of your db and I will be able to answer your question very quickly :)

Regards
Emil

Thanks for that I will happily send db for you to view. However I have gone another route and have now succeeded in jmy goal (my thanks to Neopa for his advice).

To avoid trying to run queries in VB he suggested adding a field (named Delete)to my table that updated to "Yes" (or similar) this then gave me a field to Delete Records against if "Yes". Hopes this makes sense (code :


Expand|Select|Wrap|Line Numbers
  1. SQLUpdDelete = "UPDATE " & strTblMoneyDue & " SET " & strTblMoneyDue & ".Delete = -1 where (" & strTblMoneyDue & ".Balance > 0 )"
  2. SQLDeleteDups = "DELETE " & strTblMoneyDue & ".* FROM " & strTblMoneyDue & " WHERE " & strTblMoneyDue & ".Delete = -1"

It works great!! ;-))
Nov 4 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.