Running SQL query via VBA

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?
LVL 13
Shanan212Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
lluddenCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
Dale FyeCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Dale >>> " INTO T65"...

mx
0
 
Dale FyeCommented:
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
 
Shanan212Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Dale FyeCommented:
the "wrong # of arguments" problem is because of the extra comma I mentioned in 36538612 and which MX corrected in his subsequent post.
0
 
Shanan212Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
Shanan212Author Commented:
Thanks all!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.