Re: Access frontal interface for Oracle database

From: Gloops <>
Date: Mon, 11 Aug 2008 07:35:47 +0200
Message-ID: <g7oj3j$ne3$>

Hello Timmy,

I read very quickly before urging to the office, I shall read more carefully tonight.

Well, the user name is repeated at the beginning of the ODBC connexion name, and on my machine another connexion has the user name, so I presume there should be no problem with that.

I see you give details about using a view, I was rather quick when studying this point, I think I remember a view shows the same data as in the tables it presents, so if you modify something in a view it is modified in the tables ? In this case I fear that a view does not avoid to lock the table, so several users cannot open the same view, I presume ?

I was trying to use a stored procedure (so, an UPDATE query) in order to copy the data to a temporary table, so that the user does not lock the table, but opens a copy instead. I this conception erroneous ?

I read the answers again tonight to see if a proposal allows me something quick to develop, in the meanwhile I am going to use independant forms and controls, and copy the data from the tables during the Form_Open, and backwards during the AfterUpdate. That is very heavy as a model, but I think it should have the advantage to work.

Anyway thank you for the time spent.

Timmy! a écrit, le 11/08/2008 06:31 :
> 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
> ^o<
> /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
> /^^ "What's UP, Dittoooooo?" - Ditto
Received on Mon Aug 11 2008 - 00:35:47 CDT

Original text of this message