Running a query in VB statement

[boliches64]

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


SQLDeleteDuplic ates = "DELETE " tblMoneyDue.*, tblMoneyDue.ID _
& "FROM tblMoneyDue WHERE (((tblMoneyDue. ID) = _
& "DLookUp("ID"," qryListCopiesTo Delete","ID" >1)))"


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

Any help gratefully received

[emsik1001]

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

[boliches64]

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 (SQLDeleteDupli cates)

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.

[emsik1001]

Hi

You use the correct method.

The below one is incorrect is several places

SQLDeleteDuplic ates = "DELETE " tblMoneyDue.*, tblMoneyDue.ID _
& "FROM tblMoneyDue WHERE (((tblMoneyDue. ID) = _
& "DLookUp("ID"," qryListCopiesTo Delete","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.

SQLDeleteDuplic ates = "DELETE tblMoneyDue.*, tblMoneyDue.ID " _
& "FROM tblMoneyDue WHERE (((tblMoneyDue. ID) = " _
& "DLookUp(""ID"" ,""qryListCopie sToDelete"",""I D"" >1)))"

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

Regards
Emil

[boliches64]

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.I D
FROM tblMoneyDueBP INNER JOIN qryListCopiesTo Delete ON tblMoneyDueBP.I D = qryListCopiesTo Delete.ID
WHERE (((tblMoneyDueB P.ID) Is Not Null));

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

Many thanks

[emsik1001]

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

[boliches64]

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 :

SQLUpdDelete = "UPDATE " & strTblMoneyDue & " SET " & strTblMoneyDue & ".Delete = -1 where (" & strTblMoneyDue & ".Balance > 0 )"
SQLDeleteDups = "DELETE " & strTblMoneyDue & ".* FROM " & strTblMoneyDue & " WHERE " & strTblMoneyDue & ".Delete = -1"

It works great!! ;-))