Re: Access frontal interface for Oracle database

"Shakespeare" <whatsin_at_xs4all.nl> wrote in message news:48a0876f$0$184$e4fe514c_at_news.xs4all.nl...
>
> "Gloops" <gloops_at_invalid.zailes.org> schreef in bericht
> news:g7q0ad$hpl$1_at_aioe.org...
>> Well, here is some feedback.
>>
>> I obtained error 3146 while executing "EXECUTE MAJSES", or "BEGIN
>> MAJSES", either by ADODB.Execute, or by
>> Workspace.Connection.QueryDef.Execute
>>
>> If I try, from the query interface of Access, to launch "EXECUTE MAJSES"
>> or "BEGIN MAJSES", I am answered "Invalid SQL instruction; DELETE,
>> INSERT, PROCEDURE, SELECT or UPDATE awaited". If I try and launch
>> "PROCEDURE MAJSES" I am blamed for a syntax error in the PARAMETERS
>> clause, and same thing if I try to save the record to define parameters
>> from a function.
>>
>> I could execute "INSERT INTO TMPCLIENTS SELECT * FROM TABCLIENTS" (in
>> fact, the code of MAJSES), but after that the TMPCLIENTS table was still
>> empty.
>>
>> A session is opened by the sub to execute a query, and closed before the
>> end of the sub. I fear that the temporary table is proper to each session
>> that is opened like that, so, if ten sessions are opened that way, ten
>> copies of the same table are created with no way to see data from one to
>> the other.
>> So, I presume that a session has to be opened public in a module when
>> opening the database, to be closed only when closing the database, and
>> all subs that have to operate on the base must refer to that session
>> object, but I do not have enough time to test the different syntaxes and
>> objects to implement this, so this will be for my next mission on Oracle.
>> I tried with other newsgroups before, I discovered these two ones by
>> browsing a book last Saturday once I could somewhat situate the question.
>>
>> One special point of attention is how to obtain that the session object
>> in the code refers to the same session as the user interface.
>>
>> It would be tempting to launch OraDatabase.Execute "PROCEDURE MAJSES" or
>> "EXECUTE MAJSES", ORA-00900 invalid SQL instruction, or
>> OraDatabase.Execute "BEGIN MAJSES", ORA-06550 SQL execution error, line 1
>> column 12, PLS-00103, end of file symbol met in place of one of the
>> following symbols :
>> := . ( @ % ;
>>
>> In the same time, if you see how to correct the syntax, it could be
>> interesting to do some tests about it, even if I have no time to use it
>> for the final results. I see some examples in the help of OraParamArrays,
>> but as I do not exactly situate how much time I need for this, I have to
>> start up.
>>
>> In the meanwhile, I could create temporary tables in Access. Of course,
>> like that, the Access interface will become very fat, but at least
>> several people can access at the same time.
>>
>> It was a very good idea to tell me I do not need temporary tables. Please
>> feel free to propose a way to do like that, from Access.
>
> It should be
> "BEGIN MAJSES ; END ;"
>
> Shakespeare
>

In Oracle you do NOT need temporary tables. In Oracle you can keep the data consistent with several users reading the same table. In Oracle they each see the table and do not see uncommitted transactions from other users. Jim Received on Mon Aug 11 2008 - 22:59:00 CDT