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

Append query from Oracle Back-End

P: n/a

When I try to use an append query from an oracle link it takes forever.
I am exploring the idea of doing an append action using a pass-through
query.

If I have an Oracle ODBC connection to server OraTest. User: User1
Password: password

and I am trying to append all records in table: tblTEST that are code:
"abc"

any suggestions on the SQL code that I need to use for the Access2003
to Oracle9i pass-through query?

Thanks for any tips or references.

RBollinger

Feb 7 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
robboll wrote:
When I try to use an append query from an oracle link it takes forever.
I am exploring the idea of doing an append action using a pass-through
query.
I'm not sure what you mean by the first sentence - do you mean from
Oracle into Jet? This can take a little while - I used to use Jet make
table queries years when I brought Oracle data from Oracle into Jet.
And of course, I used Jet linked tables.
If I have an Oracle ODBC connection to server OraTest. User: User1
Password: password
For both passthrough queries and linked table queries, the above does
not actually matter in Access itself - it does matter for setting up the
DSN you use.
and I am trying to append all records in table: tblTEST that are code:
"abc"
Is tblTest an Oracle table? What are you "appending" from? Not enough
information.
any suggestions on the SQL code that I need to use for the Access2003
to Oracle9i pass-through query?


What I can tell you is that SQL code in a pass through query in Access
is EXACTLY like you would write using Oracle Forms or what most Oracle
folks are familiar with, SQLPlus. "Append queries" in Oracle are
referred to as insert statements - somewhat similar in structure to a
Jet dialect insert or "append query".

There is a usenet oracle hierarchy: however, I would not ask such a
general there without having done some research first - you'd get
crucified! 8) It's an excellent and huge resource but in contrast to
this newsgroup, the folks there are generally very intolerant of
questions that look like one hasn't visited OTN or read any Oracle on
line documentation.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Feb 8 '06 #2

P: n/a
Tim,

The syntax is what I am trying to find. Access 2003 MDB linked to an
Oracle Back-End. Oracle Table tblTEST. Field Name: Code

Need to create an append query (or insert statement) using a
pass-through query thus avoiding Jet -- appending to a table with the
same structure in Access (e.g., tblTestAccess).
Tim Marshall wrote:
robboll wrote:
When I try to use an append query from an oracle link it takes forever.
I am exploring the idea of doing an append action using a pass-through
query.


I'm not sure what you mean by the first sentence - do you mean from
Oracle into Jet? This can take a little while - I used to use Jet make
table queries years when I brought Oracle data from Oracle into Jet.
And of course, I used Jet linked tables.
If I have an Oracle ODBC connection to server OraTest. User: User1
Password: password


For both passthrough queries and linked table queries, the above does
not actually matter in Access itself - it does matter for setting up the
DSN you use.
and I am trying to append all records in table: tblTEST that are code:
"abc"


Is tblTest an Oracle table? What are you "appending" from? Not enough
information.
any suggestions on the SQL code that I need to use for the Access2003
to Oracle9i pass-through query?


What I can tell you is that SQL code in a pass through query in Access
is EXACTLY like you would write using Oracle Forms or what most Oracle
folks are familiar with, SQLPlus. "Append queries" in Oracle are
referred to as insert statements - somewhat similar in structure to a
Jet dialect insert or "append query".

There is a usenet oracle hierarchy: however, I would not ask such a
general there without having done some research first - you'd get
crucified! 8) It's an excellent and huge resource but in contrast to
this newsgroup, the folks there are generally very intolerant of
questions that look like one hasn't visited OTN or read any Oracle on
line documentation.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto


Feb 8 '06 #3

P: n/a
robboll wrote:
Tim,

The syntax is what I am trying to find. Access 2003 MDB linked to an
Oracle Back-End. Oracle Table tblTEST. Field Name: Code

Need to create an append query (or insert statement) using a
pass-through query thus avoiding Jet -- appending to a table with the
same structure in Access (e.g., tblTestAccess).


A Passthrough append query would only work if both tables were on the server. A
Passthrough SQL statement is sent to and run entirely on the remote system and
as such it has no "knowledge" of any of your local Access objects and cannot
interact with them.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Feb 8 '06 #4

P: n/a

"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:vz******************@newssvr13.news.prodigy.c om...
robboll wrote:
Tim,

The syntax is what I am trying to find. Access 2003 MDB linked to an
Oracle Back-End. Oracle Table tblTEST. Field Name: Code

Need to create an append query (or insert statement) using a
pass-through query thus avoiding Jet -- appending to a table with the
same structure in Access (e.g., tblTestAccess).


A Passthrough append query would only work if both tables were on the
server. A Passthrough SQL statement is sent to and run entirely on the
remote system and as such it has no "knowledge" of any of your local
Access objects and cannot interact with them.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


If what you want is to move data from Oracle to Access, what you can do is
create a Pass-Through SELECT query to collect the desired data, and then
create a regular Access APPEND query that uses the Pass-through query as its
source. Something like this:

Insert into tblTestAccess Select * from qryMyPassThrough

Anne
Feb 10 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.