Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Running SQL query via VBA

Posted on 2011-09-14
12
Medium Priority
?
368 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 18

Assisted Solution

by:lludden
lludden earned 200 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 1200 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 48

Expert Comment

by:Dale Fye
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

mx
0
 
LVL 48

Expert Comment

by:Dale Fye
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 1200 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 48

Assisted Solution

by:Dale Fye
Dale Fye earned 600 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

604 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