Link to home
Start Free TrialLog in
Avatar of Shanan212
Shanan212Flag for Canada

asked on

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?
SOLUTION
Avatar of lludden
lludden
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.

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.
Avatar of Shanan212

ASKER

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!
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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!
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
Thanks all!