Solved

Apending a Table using VBA SQL query

Posted on 2011-09-21
3
258 Views
Last Modified: 2012-05-12
Hi again!

Again, as usual, I have done my fair share of research last night on this. My issue is to append a table using the following query output.

Last night I saw an article on internet but I couldn't get to it this morning. I know we have to use "Insert Into" statement. All the examples available are using "sql" coding within VBA instead of converting like I did.

Could you please help me the following to output to a table by appending it?

Table name is "PROVINCE TO COUNTRY RATES"

The fields that exists are: (as copied from SQL)

INSERT INTO [PROVINCE TO COUNTRY RATES] ([ORIGIN CITY], [ORIGIN PROVINCE], [DESTIN CITY], [DESTIN PROVINCE], SERVICE, Class, [MIN], LTL)

I want to integrate the above into this query

CurrentDb.Execute "SELECT '" & ocity & "' as [ORIGIN CITY], '" & oprov & "' as [ORIGIN PROVINCE], ZONE.CITY AS [DESTIN CITY], ZONE.PROVINCE AS [DESTIN PROVINCE], INTER.SERVICE, INTER.Class, INTER.[MIN], INTER.[LTL] INTO [TEMP2] FROM [ZONE] INNER JOIN INTER ON ZONE.[TCOMBI] = INTER.DTCOMBO WHERE INTER.SERVICE =" & Chr(34) & [Forms]![Prov2Country]![servicecombo] & Chr(34) & " And ZONE.[ZONE] = " & Chr(34) & "LOCAL" & Chr(34) & " And INTER.[TCOMBO] Like " & Chr(34) & userinput & "*" & Chr(34) & " ORDER BY ZONE.CITY, INTER.Class", dbFailOnError

So I do not want to output to table called 'TEMP2' rather append to table called 'PROVINCE TO COUNTRY RATES'

Any help is much appreciated!
0
Comment
Question by:Shanan212
  • 2
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 36574984
try this


CurrentDb.Execute "INSERT INTO [PROVINCE TO COUNTRY RATES] ([ORIGIN CITY], [ORIGIN PROVINCE], [DESTIN CITY], [DESTIN PROVINCE], SERVICE, Class, [MIN], LTL)
SELECT '" & ocity & "' as [ORIGIN CITY], '" & oprov & "' as [ORIGIN PROVINCE], ZONE.CITY AS [DESTIN CITY], ZONE.PROVINCE AS [DESTIN PROVINCE], INTER.SERVICE, INTER.Class, INTER.[MIN], INTER.[LTL]   FROM [ZONE] INNER JOIN INTER ON ZONE.[TCOMBI] = INTER.DTCOMBO WHERE INTER.SERVICE =" & Chr(34) & [Forms]![Prov2Country]![servicecombo] & Chr(34) & " And ZONE.[ZONE] = " & Chr(34) & "LOCAL" & Chr(34) & " And INTER.[TCOMBO] Like " & Chr(34) & userinput & "*" & Chr(34) & " ORDER BY ZONE.CITY, INTER.Class", dbFailOnError
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36575060
There are no errors but the table is empty!

I suppose you want to see the database? Is it ok to upload it to into dropbox?

Thanks
0
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 36575111
Hi,

Nevermind, I somehow made it work via removing spaces, etc

Thanks!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

830 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