Access frontal interface for Oracle database

[Gloops]

Hello everybody,

Is anyone able to give me some indications about how to develop an
Access interface for an Oracle database ?

I dispose of Access 2003 (11.6566.8107) SP2, Oracle 9i 9.2.0.1.0 and
ODBC 3.525.1117.0, on Windows XP Pro 5.1.2600 SP2 Nu 2600.

I failed executing an Oracle stored procedure from Access, and a trigger
to store data to a temporary table was active from SQL*Plus, but
not from Access.

So, my access base can connect one user, not a hundred as requested.
Anybody ?

[gym dot scuba dot kennedy at gmail]

"Gloops" <gl****@invalid.zailes.orgwrote in message
news:g7**********@aioe.org...

Hello everybody,

Is anyone able to give me some indications about how to develop an Access
interface for an Oracle database ?

I dispose of Access 2003 (11.6566.8107) SP2, Oracle 9i 9.2.0.1.0 and ODBC
3.525.1117.0, on Windows XP Pro 5.1.2600 SP2 Nu 2600.

I failed executing an Oracle stored procedure from Access, and a trigger
to store data to a temporary table was active from SQL*Plus, but not from
Access.

So, my access base can connect one user, not a hundred as requested.
Anybody ?

Don't use temporary tables. Oracle!=MS Sql Server
You would probably have to use a pass through query to do a stored proc.

[Gloops]

gym dot scuba dot kennedy at gmail a écrit, le 10/08/2008 03:15 :

>I failed executing an Oracle stored procedure from Access

You would probably have to use a pass through query to do a stored proc.

Well, would you mind giving a few details ?

[gym dot scuba dot kennedy at gmail]

"Gloops" <gl****@invalid.zailes.orgwrote in message
news:g7**********@aioe.org...
gym dot scuba dot kennedy at gmail a écrit, le 10/08/2008 03:15 :

>I failed executing an Oracle stored procedure from Access

You would probably have to use a pass through query to do a stored proc.

Well, would you mind giving a few details ?
I haven't used access in many years. This is my best guess. Did you do a
search for an example similar to the one you are trying to do?
Jim

[Gloops]

gym dot scuba dot kennedy at gmail a écrit, le 10/08/2008 15:34 :

>Well, would you mind giving a few details ?

I haven't used access in many years. This is my best guess. Did you do a
search for an example similar to the one you are trying to do?

Of course I did, I should very pleased to learn that your search appears
more efficient ...

The Execute method (of ADODB ? not sure I remember, I have a look
tomorrow) can execute, on Oracle, commands INSERT, DELETE, UPDATE, but
not EXECUTE, I could not execute a stored procedure in Oracle that way.

I searched "Access Oracle ODBC", do you see a better search ?
I have to admit I only had a look to the ten first anwsers.

[sybrandb]

On Sun, 10 Aug 2008 16:06:41 +0200, Gloops <gl****@invalid.zailes.org>
wrote:

>The Execute method (of ADODB ? not sure I remember, I have a look
tomorrow) can execute, on Oracle, commands INSERT, DELETE, UPDATE, but
not EXECUTE, I could not execute a stored procedure in Oracle that way.

It could in the past and I don't see why it can't now.
As 'execute' is a sql*plus keyword, and not a SQL keyword,
the syntax is { BEGIN <procedure>; END; }

(from memory)

--
Sybrand Bakker
Senior Oracle DBA

[Timmy!]

On Aug 9, 7:53 pm, Gloops <[e-mail]:

Hello everybody,

Is anyone able to give me some indications about how to develop an
Access interface for an Oracle database ?

I dispose of Access 2003 (11.6566.8107) SP2, Oracle 9i 9.2.0.1.0 and
ODBC 3.525.1117.0, on Windows XP Pro 5.1.2600 SP2 Nu 2600.

I failed executing an Oracle stored procedure from Access, and a trigger
to store data to a temporary table was active from SQL*Plus, but
not from Access.

So, my access base can connect one user, not a hundred as requested.
Anybody ?

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

[Gloops]

sy******@hccnet.nl a écrit, le 11/08/2008 00:18 :

On Sun, 10 Aug 2008 16:06:41 +0200, Gloops <gl****@invalid.zailes.org>
wrote:

>The Execute method (of ADODB ? not sure I remember, I have a look
tomorrow) can execute, on Oracle, commands INSERT, DELETE, UPDATE, but
not EXECUTE, I could not execute a stored procedure in Oracle that way.

It could in the past and I don't see why it can't now.
As 'execute' is a sql*plus keyword, and not a SQL keyword,
the syntax is { BEGIN <procedure>; END; }

(from memory)

OK thanks, I shall try BEGIN, although the ODBC error message box
answered me that the only recocknized keywords were INSERT, DELETE and
UPDATE (and perhaps one more, that was on Friday).

I hope you do not mix up, as BEGIN is a key word that begins the code of
an Oracle procedure.

In SQL*Plus, I launch the stored procedure by typing EXECUTE followed by
the name of the procedure -this is why I tried this from Access.

So, I am going to type BEGIN instead, that is not expansive after all :)
I tell you that tonight.

[Gloops]

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.

[Gloops]

gazzag a écrit, le 11/08/2008 12:21 :

http://download.oracle.com/docs/cd/B...ist.htm#i13945

Well thank you, now that I could establish concurrency thanks to
temporary tables inside the Access interface, I think this can help
ensure consistency.

Supposing I can cope with the treatments I have to finish, I presume a
good moment to read this can be on next Friday.

[Shakespeare]

"Gloops" <gl****@invalid.zailes.orgschreef in bericht
news:g7**********@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

[gym dot scuba dot kennedy at gmail]

"Shakespeare" <wh*****@xs4all.nlwrote in message
news:48*********************@news.xs4all.nl...

>
"Gloops" <gl****@invalid.zailes.orgschreef in bericht
news:g7**********@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

[Gloops]

gym dot scuba dot kennedy at gmail a écrit, le 12/08/2008 05:59 :

In Oracle you do NOT need temporary tables. In Oracle you can keep thedata
consistent with several users reading the same table. In Oracle they each
see the table and do not see uncommitted transactions from other users.

Well, perhaps you remember, the topic of the thread is "how to cope
without temporary tables from Access" ?
Or in case of no answer, "how to update temporary tables".

I answered point 2 by hosting the temporary tables in Access, not in
Oracle, as in Oracle the update failed. The drawback is a big Access
file, but at least it works.

If you have an idea without temporary tables ...

I think I already saw that part of the solution is to forbid
modifications and use a snapshot recordset for the form, as well as
avoiding requests to fulfill the lists, but that is not suffisant.

If anybody tested something that works ...

[Shakespeare]

"Gloops" <gl****@invalid.zailes.orgschreef in bericht
news:g7**********@aioe.org...
gym dot scuba dot kennedy at gmail a écrit, le 12/08/2008 05:59 :

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.

Well, perhaps you remember, the topic of the thread is "how to cope
without temporary tables from Access" ?
Or in case of no answer, "how to update temporary tables".

I answered point 2 by hosting the temporary tables in Access, not in
Oracle, as in Oracle the update failed. The drawback is a big Access
file, but at least it works.

If you have an idea without temporary tables ...

I think I already saw that part of the solution is to forbid
modifications and use a snapshot recordset for the form, as well as
avoiding requests to fulfill the lists, but that is not suffisant.

If anybody tested something that works ...
================================================== ===========

You asked for a corrected syntax. I think I did that in my previous post.
Did you try it?

Shakespeare

[Gloops]

Shakespeare a écrit, le 12/08/2008 07:52 :

You asked for a corrected syntax. I think I did that in my previous post.
Did you try it?

Shakespeare

Oh yes, add END after BEGIN MAJSES;

Did you really test it like that ?
Promised, I shall try.

You know, there has been a time to test different possibilities, and now
a time (too short) to apply one. It is right that the first one was too
short as I took a long time to find a newsgroup where I obtained
answers. Because of this, I did only let you two chances to provide the
good answer, I am sorry about this, it is very few.

I tell you tomorrow.
And if you have another idea I can also test on Thursday (and Monday and
Tuesday if it is very quick), if it works I shall know for my next
Oracle base.

[Shakespeare]

"Gloops" <gl****@invalid.zailes.orgschreef in bericht
news:g7**********@aioe.org...
Shakespeare a écrit, le 12/08/2008 07:52 :

You asked for a corrected syntax. I think I did that in my previous post.
Did you try it?

Shakespeare

Oh yes, add END after BEGIN MAJSES;

Did you really test it like that ?
Promised, I shall try.

You know, there has been a time to test different possibilities, and now
a time (too short) to apply one. It is right that the first one was too
short as I took a long time to find a newsgroup where I obtained
answers. Because of this, I did only let you two chances to provide the
good answer, I am sorry about this, it is very few.

I tell you tomorrow.
And if you have another idea I can also test on Thursday (and Monday and
Tuesday if it is very quick), if it works I shall know for my next
Oracle base.
=========================================
Ok, I see. With all the answers and discussions here it must be hard to
follow up!

Good luck,

Shakespeare

[purpleflash]

On 13 Aug, 06:53, "Shakespeare" <[e-mail]:

"Gloops" <[e-mail] in berichtnews:g7**********@aioe.org...
Shakespeare a écrit, le 12/08/2008 07:52 :You asked for a corrected syntax. I think I did that in my previous post.

Did you try it?

Shakespeare

Oh yes, add END after BEGIN MAJSES;

Did you really test it like that ?
Promised, I shall try.

You know, there has been a time to test different possibilities, and now
a time (too short) to apply one. It is right that the first one was too
short as I took a long time to find a newsgroup where I obtained
answers. Because of this, I did only let you two chances to provide the
good answer, I am sorry about this, it is very few.

I tell you tomorrow.
And if you have another idea I can also test on Thursday (and Monday and
Tuesday if it is very quick), if it works I shall know for my next
Oracle base.

=========================================
Ok, I see. With all the answers and discussions here it must be hard to
follow up!

Good luck,

Shakespeare

Executing a stored procedure from Access to an Oracle backend is fine
- triggers are internal to Oracle and are as the name suggests
triggered from an 'event' happening in Oracle not Access. Therefore if
you for example run an update query from within Access then any
onupdate trigger in Oracle will fire!

To excute a procedure I use ADODB
******************************************* the following is an
extract currently running fine in 97, 2000 and 2003

' Only needed if a connection is to be made
Set MyCon = New ADODB.Connection

' Connection String. - Provider can be OraOLEDB.Oracle or MSDAORA
strCon = "Provider=MSDAORA;Data Source=" _
& Servername & ";User ID=" & [Forms]![frmSobiMain]![MYORAID] &
";Password=" & [Forms]![frmSobiMain]![MYORAPASS]

MyCon.Open strCon
' End of connection
' Create command using current DB access
Set MyCommand = New ADODB.Command
Set MyCommand.ActiveConnection = MyCon

' Name of stored procedure
MyCommand.CommandText = "BGS.BOREHOLE_GEOLOGY_P4"
' Command type
MyCommand.CommandType = adCmdStoredProc
MyCommand.CommandTimeout = 15

' Set up Parameters
Set MyParam1 = New ADODB.Parameter
MyParam1.Type = adVarChar
MyParam1.Size = 6
MyParam1.Direction = adParamInput
' set parameter to current SOBI QS
MyParam1.Value = [Forms]![frmSobiMain]![QS]
MyCommand.Parameters.Append MyParam1

Set MyParam2 = New ADODB.Parameter
MyParam2.Type = adVarChar
MyParam2.Size = 2
MyParam2.Direction = adParamInput
MyParam2.Value = [Forms]![frmSobiMain]![RT]
MyCommand.Parameters.Append MyParam2

'etc for as many parameters as required

'Execute stored procedure
MyCommand.Execute

This works fine for me and my Access front ends are all mult-user
operating against large complex datasets (million row plus) The above
procedure creates a replicant of a complete record automatically the
record is in a one to many to many relationship. (NO temporary tables
are used)

Good luck!

[Gloops]

Shakespeare a écrit, le 11/08/2008 20:39 :

It should be
"BEGIN MAJSES ; END ;"

Well, right, this executes with no error message. Thanks.

Nevertheless, remains the problem that the table in the user interface
remains empty, just as when I executed the detailed query (INSERT INTO
TMPCLIENTS SELECT * FROM TABCLIENTS).

So, probably it would be interesting to test this with a database object
declared at the opening of the menu form, that remains opened to the
closure of the base, and close the database object only at that moment,
and same thing for a session.

Probably, there is also a tip to find, so that the database in the
interface is the same as the database in the code. Maybe it is quite as
simple as using CurrentDb() instead of OpenDatabase -and defining a
session on it. Until I do that, I am not sure there is not still
something else to finalize afterwards.

At the moment being, I found a way to propose something that works, and
I have quite shortly the time to put it in place. So, I cannot spend
that time to finalize another solution, allas. The next time I have the
environment I try that.

If somebody has time and the development environment and feels
interested with finding all the different points of attention to
implement an Access interface to an Oracle database, let him (her) feel
free to do it, and kindly tell us.

So, your answer helps to run an Oracle stored procedure that I defined
to initialize a temporary table on Oracle. This is one way.

A few persons told a temporary table is not needed. This is another way.

Perhaps there is still another solution, maybe.

I feel several developers could be interested by the description of the
whole process to obtain an interface with which :
- users can display data on several machines at a time
- one user can modify the data at a time, and attempts to do it on
another machine receive an alert message
- and, the progress compared to what I am going to do, temporary data is
mainly not stored on Access, which avoids to inflate the interface.

Thank you very much for your help and participation, even if I could not
implement your proposal yet.

[Gloops]

Hello, Eurêka !

Well, I found the answer to the question : how to obtain that I do not
need temporary tables (at least, to display data).

In the options of Access 2003, Advanced tab, after the default file
format, you have a default open mode options group, with the values
"shared" and "exclusive". After that, a default lock options group, with
the values "none", "general", "modified record". After that, a checkbox
"open with locked records".

This last checkbox was checked, causing the problems I enumerated. I
completely ignore what it aims, but once it is unchecked (and of course
the default open mode set to shared), several users can load the base.

It is quite strange that the file name does not appear in the options
box, but the effects of the options can be observed on the other machine
with the execution kit.

In fact, before being sure it is a good idea to open the form directly
on the main table, I have to finish the modification functions, I am
late on that, and the further search I did was to solve the problems I
encountered to modify data.

http://officesystemaccess.seneque.net (in French)

[Gloops]

Shakespeare a écrit, le 13/08/2008 07:53 :

Ok, I see. With all the answers and discussions here it must be hard to
follow up!

Good luck,

Shakespeare

Come on, do not be that sarcastic.
It is right that I came at the last moment and I imagine it is not
pleasant, but I took note of a few very helpful hints.
You know, as the development is not all finished (and I wonder what this
week-end will be), it is quite possible I have an occasion to call a
stored procedure the way you said -for something else as feeding a
temporary table.

[Gloops]

As a sort of epilogue ...

You know what, I had to spend a lot of time to deal with questions I did
not know, so, now I realize I have a somewhat untidy code, and I should
be well inspired to reconcentrate on simple guidelines (seeing where I
opened and closed objects ...)

This is an illustration of what this week is (I tend to say was, as we
stay at home on Friday).

I usually avoid to impact such a pressure, sorry for having things
coming altogether.
This first experience on Oracle was very instructive, and I must admit
staying in the delays appears like a challenge.

[Gloops]

purpleflash a écrit, le 13/08/2008 10:02 :

Executing a stored procedure from Access to an Oracle backend is fine
- triggers are internal to Oracle and are as the name suggests
triggered from an 'event' happening in Oracle not Access. Therefore if
you for example run an update query from within Access then any
onupdate trigger in Oracle will fire!

Well, this remains me that the event (well, the trigger) was not fired
when inserting data from Access -whereas it was when inserting data from
Oracle. Anything that I did wrong ? Or perhaps another option to set
correctly ?

Well, when you speak about a stored procedure, do not let us forget that
it can be stored in Access or in Oracle, and of course the syntax is not
the same to call it.

It seems you are speaking of something stored on Oracle, with the
interesting aspect that you pass parameters. It is right that to use
parameters in such a context, an example is somewhat very valuable.
Thank you.

To excute a procedure I use ADODB
******************************************* the following is an
extract currently running fine in 97, 2000 and 2003

' Only needed if a connection is to be made
Set MyCon = New ADODB.Connection

' Connection String. - Provider can be OraOLEDB.Oracle or MSDAORA
strCon = "Provider=MSDAORA;Data Source=" _
& Servername & ";User ID=" & [Forms]![frmSobiMain]![MYORAID] &
";Password=" & [Forms]![frmSobiMain]![MYORAPASS]

MyCon.Open strCon
' End of connection
' Create command using current DB access
Set MyCommand = New ADODB.Command
Set MyCommand.ActiveConnection = MyCon

' Name of stored procedure
MyCommand.CommandText = "BGS.BOREHOLE_GEOLOGY_P4"
' Command type
MyCommand.CommandType = adCmdStoredProc
MyCommand.CommandTimeout = 15

' Set up Parameters
Set MyParam1 = New ADODB.Parameter
MyParam1.Type = adVarChar
MyParam1.Size = 6
MyParam1.Direction = adParamInput
' set parameter to current SOBI QS
MyParam1.Value = [Forms]![frmSobiMain]![QS]
MyCommand.Parameters.Append MyParam1

Set MyParam2 = New ADODB.Parameter
MyParam2.Type = adVarChar
MyParam2.Size = 2
MyParam2.Direction = adParamInput
MyParam2.Value = [Forms]![frmSobiMain]![RT]
MyCommand.Parameters.Append MyParam2

'etc for as many parameters as required

'Execute stored procedure
MyCommand.Execute

This works fine for me and my Access front ends are all mult-user
operating against large complex datasets (million row plus) The above
procedure creates a replicant of a complete record automatically the
record is in a one to many to many relationship. (NO temporary tables
are used)

Good luck!

[Shakespeare]

"Gloops" <gl****@invalid.zailes.orgschreef in bericht
news:g7**********@aioe.org...
Shakespeare a écrit, le 13/08/2008 07:53 :

Ok, I see. With all the answers and discussions here it must be hard to
follow up!

Good luck,

Shakespeare

Come on, do not be that sarcastic.
It is right that I came at the last moment and I imagine it is not
pleasant, but I took note of a few very helpful hints.
You know, as the development is not all finished (and I wonder what this
week-end will be), it is quite possible I have an occasion to call a
stored procedure the way you said -for something else as feeding a
temporary table.
================================================

Sorry, this was not meant to be sarcastic at all; I really DO understand
that all these answers may be confusing and time consuming!

Shakespeare

[Gloops]

Mission endend last night.

I stayed late in the night, but eventually got a form able to modify data.

The (advanced) options of Access do not include a lock of data by
default, the form is based on a temporary table in the Access interface,
intiated during the load of the menu form, edits are disabled by
default, and enabled when clicking on a button, that in the same time
modifies the backcolor to inform the user -another button saves the
record, puts the backcolor to its initial value, and copies data with an
SQL request to the Oracle database.

In a first time the execution of the query was impossible, as I forgot
to close a few objects. Strangely the correction of the problem did not
produce effects immediately.

If I was at a normal moment of the mission I should now do some tests
about locks, with an explicit lock on the record when clicking on the
Modify button.

Happy to have obtained something, thank you very much for your help.