Solved

Apending a Table using VBA SQL query

Posted on 2011-09-21
3
254 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 119

Accepted Solution

by:
Rey Obrero 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

744 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

14 Experts available now in Live!

Get 1:1 Help Now