473,320 Members | 1,950 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Running a query in VB statement

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
6 1421
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
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
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
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
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
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

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

Similar topics

1
by: John Sway | last post by:
I'm writing a web-based "Query analyser" tool for our company intranet. It allows a user to type any SQL statement in a form, and execute it over the Web. The SQL can be a query that returns...
1
by: gary b | last post by:
Hello When I use a PreparedStatement (in jdbc) with the following query: SELECT store_groups_id FROM store_groups WHERE store_groups_id IS NOT NULL AND type = ? ORDER BY group_name
29
by: pb648174 | last post by:
I have a very long transaction that runs on the same database that other users need to use for existing data. I don't care if they see data from the transaction before it is done and am only using...
5
by: PeteCresswell | last post by:
Access tends to take over my machine when I run long queries. The queries in question are Append queries. Some of the jobs we're talking about run 45 minutes to an hour...so the loss in...
8
by: Tony Williams | last post by:
I have a database that hold s figures for each quarter. I want to create a query whereby the user can ask for the running total of the data upto the quarter they enter into a prompt. The database...
1
by: Aaron West | last post by:
Try this script to see what queries are taking over a second. To get some real output, you need a long-running query. Here's one (estimated to take over an hour): PRINT GETDATE() select...
8
by: John | last post by:
Hi I am using the below code to run a sql on an underlying access table; insStr = "INSERT INTO ( Action, , , Request_Date ) " & _ "VALUES (""Modify Client"", 93, ""Administrator"", Now())"...
4
by: dougans | last post by:
Hey there people, Need help on this one if you can spare me a second!!! No idea why, been trying for 2 hours to fix this now and I feel like a complete loser haha. Anyone have any idea why...
5
by: =?ISO-8859-1?Q?Gear=F3id?= | last post by:
Hey, This may sound odd, but is there anyway to catch the current or just run query from inside a trigger? Kinda like how profiler displays the query just as you've run it, along with all the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.