Solved

Apending a Table using VBA SQL query

Posted on 2011-09-21
3
259 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

737 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