Re: Access frontal interface for Oracle database

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. Received on Mon Aug 11 2008 - 13:27:25 CDT