[AccessD] Oracle into Access

Well, you would do it the same way you would in an SQL statement directly to 
Oracle. If it can't be done in an SQL statement than this probably isn't 
gonna work for what you need.

What I do when I do a pass through query is to fire up SQL+ and my text 
editor and create the SQL in the text editor and paste it into SQL+ to 
run/test it and keep tweaking until it's working. Then I go into Access and 
create a new query and select SQL-Specific and then Pass-through and you 
will be given a completely blank window to paste the Oracle format SQL into.

The biggest problem I had in getting it going the first time was getting the 
ODBC Connection string set correctly. Then I found the wizard building for 
the ODBC Connection String (the three dots elipsis thing at the right of the 
field in the query properties should have clued me in - a big "duh" on my 
part) and instead of taking pot shots at what I thought should be in there I 
let the wizard build the connection string for me and it's worked well ever 
since.

You still need to set up the ODBC data source to the Oracle database.

But, if it's not doable in an Oracle SQL statement than this isn't the thing 
for what you're trying to do.

Gary Kjos
garykjos at hotmail.com





>From: "Gowey Mike W" <Mike.W.Gowey at doc.state.or.us>
>Reply-To: accessd at databaseadvisors.com
>To: <accessd at databaseadvisors.com>
>Subject: RE: [AccessD] Oracle into Access
>Date: Thu, 17 Apr 2003 08:44:35 -0600
>
>Thanks Gary,
>
>So when I open a Pass thru query, how do I call a function that is built
>on the Oracle side?  On the Oracle side there is a package that contains
>several functions that changes data in the tables.  I want to be able to
>call one of these functions and have it run.  Most of these functions
>manipulate records in the tables by moving records from one table to
>another.
>
>A Brief description of what is happening:  In access I give the user the
>ability to correct problems with a record and once the record is
>corrected the function from Oracle needs to be called so that it can
>move that record to the permanent table and remove it from the temp
>table.  It also has code that checks the record again for accuracy and
>inputs a record in a History table with what fields where changed.
>
>Mike Gowey, MCP
>Technical Support Analyst
>SRCI ISSD Team Leader
>(541)881-4808
>
>
>-----Original Message-----
>From: Gary Kjos [mailto:garykjos at hotmail.com]
>Sent: Thursday, April 17, 2003 7:21 AM
>To: accessd at databaseadvisors.com
>Subject: Re: [AccessD] Oracle into Access
>
>
>Hi Mike,
>
>Not entirely clear on what a "Custome Package" is in Oracle. But I use
>Access front ends to Oracle databases every day, primarily using ODBC
>Linked
>tables. I have a couple applications that I use pass-through queries.
>With a
>pass-through query you create an Oracle SQL statment and it is then sent
>
>directly to Oracle without interpretation by Access. This allows you to
>do
>anything you could do using another SQL to Oracle interface, including
>calling Oracle Stored Procedures or custom functions. So I think the
>answer
>to your question is YES.
>
>Have a look in the Access help on "Pass-Through Query" and perhaps it
>will
>get you started.
>
>Gary Kjos
>garykjos at hotmail.com
>
>
>
>
>
> >From: "Gowey Mike W" <Mike.W.Gowey at doc.state.or.us>
> >Reply-To: accessd at databaseadvisors.com
> >To: <accessd at databaseadvisors.com>
> >Subject: [AccessD] Oracle into Access
> >Date: Wed, 16 Apr 2003 09:59:33 -0600
> >
> >
> >Hi Everyone,
> >
> >Has anyone know if it is possible to call a custome package from Oracle
>
> >and run it in Access?  Is this possible?
> >
> >Thanks,
> >
> >Mike Gowey, MCP
> >Technical Support Analyst
> >SRCI ISSD Team Leader
> >
> >_______________________________________________
> >AccessD mailing list
> >AccessD at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/accessd
> >Website: http://www.databaseadvisors.com
>
>
>_________________________________________________________________
>Help STOP SPAM with the new MSN 8 and get 2 months FREE*
>http://join.msn.com/?page=features/junkmail
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com


_________________________________________________________________
Help STOP SPAM with the new MSN 8 and get 2 months FREE*  
http://join.msn.com/?page=features/junkmail