Re: Access frontal interface for Oracle database
Date: Mon, 11 Aug 2008 05:52:43 GMT
Message-ID: <LsQnk.732$7N1.529@trnddc06>
"Gloops" <gloops_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
Received on Mon Aug 11 2008 - 00:52:43 CDT