Solved

Running SQL query via VBA

Posted on 2011-09-14
12
317 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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 - Access MVP) 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

706 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now