Solved

Running SQL query via VBA

Posted on 2011-09-14
12
351 Views
Last Modified: 2012-05-12
SELECT ZONE.CITY, ZONE.PROVINCE, ZONE.TCOMBI,  INTO T65
FROM [ZONE]
WHERE (((ZONE.PROVINCE)="ONTARIO") AND ((ZONE.ZONE)="BEYOND"));

Open in new window


I am trying to run the above SQL via VBA using this

CurrentDb.Execute "SELECT ZONE.CITY, ZONE.PROVINCE, ZONE.TCOMBI,  INTO T65
FROM [ZONE]
WHERE (((ZONE.PROVINCE)="ONTARIO") AND ((ZONE.ZONE)="BEYOND"))"

Open in new window


I am getting syntax error. Could anyone please help?
0
Comment
Question by:Shanan212
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 18

Assisted Solution

by:lludden
lludden earned 50 total points
ID: 36538542
Replace the " around the parameters with '

CurrentDb.Execute "SELECT ZONE.CITY, ZONE.PROVINCE, ZONE.TCOMBI,  INTO T65
FROM [ZONE]
WHERE (((ZONE.PROVINCE)='ONTARIO') AND ((ZONE.ZONE)='BEYOND'))"
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 300 total points
ID: 36538569
Try this

CurrentDb.Execute "SELECT ZONE.CITY, ZONE.PROVINCE, ZONE.TCOMBI,  INTO T65
FROM [ZONE]
WHERE ZONE.PROVINCE=" & Chr(34) & "ONTARIO" & Chr(34) & " AND ZONE.ZONE = " & Chr(34) & "BEYOND" & Chr(34) , dbFailOnError

mx
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36538574
The Execute method is for action queries (Update, Insert, Delete), not Select queries.

What are you actually trying to do?  Do you want to open the query?  Why would you want to do so from within VBA?  A better approach would be to use this select statement as the recordsource for a form or something like that.

0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 75
ID: 36538594
Dale >>> " INTO T65"...

mx
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36538612
Oops,

Looks like there is an extra comma, just before the "INTO T65"

and I agree with Joe (MX) that you should use the dbFailOnError to close it out.
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36538637
The above answers giving me error higlighting the 'execute' part (Wrong Number of arguments)

I am trying to run 2 queries.

1 - Filtering based on user entered values. A simplified version would be this

CurrentDb.Execute "SELECT ZONE.CITY, ZONE.PROVINCE, ZONE.TCOMBI,  INTO T65
FROM [ZONE]
WHERE (((ZONE.PROVINCE)=[Forms]![Point2Province]![oprovcombo]))"

Open in new window


2. I am also trying to do a calculation on the above code itself. But I am confused on that as well.

[code]CurrentDb.Execute "SELECT ZONE.CITY, ZONE.PROVINCE, ZONE[highlight]*0.65[/highlight].TCOMBI,  INTO T65
FROM [ZONE]
WHERE (((ZONE.PROVINCE)=[Forms]![Point2Province]![oprovcombo]))"

Open in new window


So now the question is in 2 parts. The #1 is not working with the error. I also want to know if #2 is possible. Would I be using the '.execute' for #2?

Thanks!
0
 
LVL 75
ID: 36538646
I almost ... made that embarrassing post ...  fortunately you did first!     LOL :-)

Extra comma ... yes ...
                                                                                       ' removed ...............vv
CurrentDb.Execute "SELECT ZONE.CITY, ZONE.PROVINCE, ZONE.TCOMBI  INTO T65
FROM [ZONE]
WHERE ZONE.PROVINCE=" & Chr(34) & "ONTARIO" & Chr(34) & " AND ZONE.ZONE = " & Chr(34) & "BEYOND" & Chr(34) , dbFailOnError
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 300 total points
ID: 36538671
This:

CurrentDb.Execute "SELECT ZONE.CITY, ZONE.PROVINCE, ZONE.TCOMBI,  INTO T65
FROM [ZONE]
WHERE (((ZONE.PROVINCE)=[Forms]![Point2Province]![oprovcombo]))"

to this: (again extra comma removed)

CurrentDb.Execute "SELECT ZONE.CITY, ZONE.PROVINCE, ZONE.TCOMBI  INTO T65
FROM [ZONE] WHERE ZONE.PROVINCE = " & Chr(34) & [Forms]![Point2Province]![oprovcombo] & Chr(34) , dbFailOnError
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 150 total points
ID: 36538732
the "wrong # of arguments" problem is because of the extra comma I mentioned in 36538612 and which MX corrected in his subsequent post.
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36538911
The table is working as I wanted

I also designed a 'calculation based' query and that is outputting as well. I had errors but I corrected them myself by doing [zone]![city]*0.65 (bringing into brackets)

Thanks for the help so far!

1. Now I have the query output to a table. However, I want the query to overwrite the existing table, everytime the query is run.

My coding is like this

DoCmd.SetWarnings False
CurrentDb.Execute "SELECT ZONE.CITY, .....
DoCmd.SetWarnings True

Open in new window


Should I be putting something so it overwrites the existing query output table? (the full query is in above comments)

2. The query itself is taking my WHOLE screen going to the left and I have to scroll a mile (as the original I have is huge) Is there a way to break it down within VBA console?

Thanks!
0
 
LVL 75
ID: 36539094
Well ... did we clean up your original question/issue ... syntax error?

If so, best to close out this Q and ask a new one with new issue.

thx.mx
0
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 36539193
Thanks all!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question