Re: Access frontal interface for Oracle database

On Aug 9, 7:53 pm, Gloops <glo..._at_invalid.zailes.org> wrote:
> Hello everybody,
>
> Is anyone able to give me some indications about how to develop an
> Access interface for an Oracle database ?
>
> I dispose of Access 2003 (11.6566.8107) SP2, Oracle 9i 9.2.0.1.0 and
> ODBC 3.525.1117.0, on Windows XP Pro 5.1.2600 SP2 Nu 2600.
>
> I failed executing an Oracle stored procedure from Access, and a trigger
> to store data to a temporary table was active from SQL*Plus, but
> not from Access.
>
> So, my access base can connect one user, not a hundred as requested.
> Anybody ?

Salut, Gloops.

I don't do this (running stored procs) very often not because one can't but because I have little experience in PL/SQL. However, you should be able to do this. If I can execute DDL, I'm sure stored procs won't be a problem.

You need a pass through query, though you've probably figured that out already if you've tried and failed to execute.

Take the syntax that you type into SQLPlus to execute the query and copy it into the PTQ query SQL View.

If the Oracle user that is being used for your ODBC connection string is not the (Oracle guys may need to help me out, I'm not 100% sure of the terminology) main user, ie, the user that makes up the DSN you are using was made under another "main" (again, bad term, sorry) user, you are going to need to prefix the proc name with the main user and a dot.

Here's an example using a create view statement (not Pl/sql), I know, but hopefully you will see what I am getting at:

Main user (again apologies the Oracle folks): Timmy User with specific privileges created under user Timmy: Apples

Apples is the user in the DSN.

Now, the following will run in SqlPlus when logged in as Timmy:

Create or Replace view v_my_view as
SELECT Fruit_type, Fruit_name, Fruit_locations FROM Fruits
WHERE Fruit_type = 'Apple'

If you were to run this in the Access environment as a PTQ using the DSN that uses the user name Apples, the syntax you need in Access is:

Create or Replace view v_my_view as
SELECT Fruit_type, Fruit_name, Fruit_locations FROM Timmy.Fruits
WHERE Fruit_type = 'Apple'

Note the from clause. You would need to do something similar to your stored proc.

If you are trying to do this via VBA code, let me know. There is one small trick to avoid an error message when VBA runs a PTQ that is a stored proc or update/delete/insert SQL.

Hopefully some of the cdo flks will correct some of my poor terminology above.

--
Tim   http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto