Re: Access frontal interface for Oracle database

On 11 Aug, 06:52, "gym dot scuba dot kennedy at gmail" <kenned..._at_verizon.net> wrote:
> "Gloops" <glo..._at_invalid.zailes.org> wrote in message
>
> news:g7oj3j$ne3$1_at_aioe.org...
> 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  http://www.ucs.mun.ca/~tmarshal/
> > ^o<
> > /#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
> > /^^ "What's UP, Dittoooooo?" - Ditto
>
> Oracle!=sqlServer.
> Readers do NOT block writers in Oracle.(writers do not block readers either)
> You do not need a temp table.
> Jim- Hide quoted text -
>
> - Show quoted text -

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#i13945

HTH -g Received on Mon Aug 11 2008 - 05:21:45 CDT