Solved

Create a table based on VBA based SQL

Posted on 2011-09-09
12
273 Views
Last Modified: 2012-05-12
SELECT INTER.TCOMBI, INTER.Service, INTER.Min, INTER.LTL, INTER.Class, INTER.[500], INTER.[1M], INTER.[2M], INTER.[5M], INTER.[10M], INTER.[20M] INTO BTER
FROM INTER
WHERE (((INTER.TCOMBI)=(GetO([Forms]![Point2Point]![ocitytxt] & [Forms]![Point2Point]![oprovcombo])) & (GetO([Forms]![Point2Point]![dcitytxt] & [Forms]![Point2Point]![dprovcombo]))) AND ((INTER.Service)=[Forms]![Point2Point]![servicecombo]))
ORDER BY INTER.Class;

Open in new window


Hi, I have the above code in a query's sql view. For the ease of changing values inbetween, I want to out put the above from a nested if statement; hence changing some values here and there

How do I do this? I am new to combining SQL with VBA :/

Thanks in advance!

Regards,
Shanan
0
Comment
Question by:Shanan212
  • 7
  • 5
12 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36512521
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 ...
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36512628
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!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36512669

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
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 36512686
...Note,
...be sure that the form(s) that hold the query's parameters are open when this is run...
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36524452
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]));

Open 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]))"

Open in new window


Still giving me syntax errors though :/
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36524586
"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
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 13

Author Comment

by:Shanan212
ID: 36524631
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

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36524980
<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


0
 
LVL 13

Author Comment

by:Shanan212
ID: 36526016
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]))"

Open 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] 

Open in new window


Thank you for your efforts so far
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
ID: 36527035
<'Too few Parameters. Expected 2'>
The form that provides your parameters must be open, and have the parameters available when you run the code.
0
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 36530905
Thanks!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36531284
ok
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

867 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

15 Experts available now in Live!

Get 1:1 Help Now