By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,192 Members | 1,276 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,192 IT Pros & Developers. It's quick & easy.

How to gather data to Access local table from odbc connected database

P: 4
I am trying to append data to my local access database from an oracle connected database. I tried using the default query function but it take a very long time and it sort of hangs there and no results returned. i would like to know whether anyone know any codes to do this task. Appreciate all the help i can get. Thanks.
Jan 16 '07 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,494
Try opening the Oracle table by itself first. Is it very slow?
If not, then post the SQL of the query you have and we can see if it has anything obviously inefficient in it.
Jan 16 '07 #2

nico5038
Expert 2.5K+
P: 3,072
Best to use a so-called Pass Through query. You'll have to write a query in Oracle SQL and set the querytype to PassThrough. Then just the SQL will be sent to the Oracle database engine and just the results will be returned.
All needed is an ODBC connection.
Use this query as basis for an Access append query to add the data to an internal table.

Might help to check the helpfile on the Pass Through query.

Nic;o)
Jan 17 '07 #3

P: 4
Thought that the pass through query only does a select action and not append action. How do i do an append action with pass through query. Any sample code? Thanks for the reply.
Jan 17 '07 #4

nico5038
Expert 2.5K+
P: 3,072
The solution to the "select" was in my comment:

Use this query as basis for an Access append query to add the data to an internal table.

So after creating qryPassThrough create an INSERT INTO not based on a table, but on the query....


Nic;o)
Jan 17 '07 #5

NeoPa
Expert Mod 15k+
P: 31,494
Thought that the pass through query only does a select action and not append action. How do i do an append action with pass through query. Any sample code? Thanks for the reply.
Firstly, Nico's answer is correct and that is a good way to do it.
In response to your statement, though, PassThrough queries can be used for action queries.
An append query in this case, would need to be written to SELECT from a data source (table) available to the Oracle back-end database itself. It could not just refer to one of your Access tables.
Jan 17 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.