467,083 Members | 1,209 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,083 developers. It's quick & easy.

Append query from Oracle Back-End


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
  • viewed: 6593
Share:
4 Replies
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
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
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

"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.

Similar topics

2 posts views Thread by JMCN | last post: by
12 posts views Thread by zwasdl@gmail.com | last post: by
2 posts views Thread by RichardP via AccessMonster.com | last post: by
3 posts views Thread by nujcharee@googlemail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.