Create a table based on VBA based SQL

To turn a basic SELECT query into a Make table query, the syntax will be something like this:

SELECT ...Fld1, Fld2, ...etc INTO NewTable
FROM OriginalTable
WHERE ...
OrderBy ...

Yes but how do I execute it?

I ve done further research using this
http://www.databasejournal.com/features/msaccess/article.php/10895_3505836_2/Executing-SQL-Statements-in-VBA-Code.htm

But I am getting sytax errors. Does this means I have to rewrite my SQL? Does this means the SQL in SQL-pane and SQL and VBA are different (by some ways)

Thanks!

Then get rid of the line breaks in the SQL and you can "Run" the query (that creates the table) using this basic syntax:

CurrentDb.Execute "SELECT......INTO......FROM......WHERE......ORDER BY......", dbFailOnError

Hi,

So I have this code from SQL window

SELECT OUT.CITY, OUT.PROVINCE, OUT.[ROUTING TERMINAL], OUT.TCOMBI, OUT.ZONE, OUT.Min, OUT.LTL, OUT.[500], OUT.[1M], OUT.[2M], OUT.[5M], OUT.[10M], OUT.[20M] INTO OB
FROM OUT
WHERE (((OUT.CITY)=[Forms]![Point2Point]![ocitytxt]) AND ((OUT.PROVINCE)=[Forms]![Point2Point]![oprovcombo]));

Select allOpen in new window

Now I want it to be run on VBA so that I can change where the sql is getting data from (ie: I want to change the text box sources based on an IF statement.

Would that be like this?

rentDb.Execute "SELECT OUT.CITY, OUT.PROVINCE, OUT.[ROUTING TERMINAL], OUT.TCOMBI, OUT.ZONE, OUT.Min, OUT.LTL, OUT.[500], OUT.[1M], OUT.[2M], OUT.[5M], OUT.[10M], OUT.[20M] INTO OB
FROM OUT
WHERE (((OUT.CITY)=[Forms]![Point2Point]![ocitytxt]) AND ((OUT.PROVINCE)=[Forms]![Point2Point]![oprovcombo]))"

Select allOpen in new window

Still giving me syntax errors though :/

"Still giving me syntax errors though :/"
What errors?

What is "rentDB"?
(Try it as CurrentDB, if it is the same DB that you are in)
How is it declared?
...etc

It is currentDB as it is in the same database. Sorry that was a copying error.

What do you mean by "how it is declared"

The error I am getting now is "Expected End of statment"  

at this point

INTO OB" FROM OUT WHERE

Select allOpen in new window

<What do you mean by "how it is declared">
Well if it is the currentDB, then use CurrentDB.

If you are going to give it a different name, then it has to be declared...


<I am getting now is "Expected End of statment"  >
<INTO OB" FROM OUT WHERE>
...odd, because in none of the snippets you posted above have that clause...?

Where is that double quote coming form?
Remember what I posted...
A basic Insert will look something like this:
CurrentDb.Execute "SELECT......INTO......FROM......WHERE......ORDER BY......", dbFailOnError


JeffCoachman

CurrentDb.Execute "SELECT OUT.CITY, OUT.PROVINCE, OUT.[ROUTING TERMINAL], OUT.TCOMBI, OUT.ZONE, OUT.Min, OUT.LTL, OUT.[500], OUT.[1M], OUT.[2M], OUT.[5M], OUT.[10M], OUT.[20M] INTO NEW1 FROM OUT WHERE (((OUT.CITY)=[Forms]![Point2Point]![ocitytxt]) AND ((OUT.PROVINCE)=[Forms]![Point2Point]![oprovcombo]))"

Select allOpen in new window

Ok using your example and some online research I was able to correct that error. Now I am getting 'Too few Parameters. Expected 2'

The main question I have appart from the error is that (I haven't tried it yet) is it possible to put a calculation in the middle of above table?

Eg: (partial coding being this)

(((OUT.CITY)=[Forms]![Point2Point]![ocitytxt])[b] *0.65[/b] 

Select allOpen in new window

Thank you for your efforts so far

Thanks!

ok