Access frontal interface for Oracle database

[Shakespeare]

Re: Access frontal interface for Oracle database


"Gloops" <gloops@invalid .zailes.orgschr eef in bericht
news:g7spnh$ett $1@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]

Re: Access frontal interface for Oracle database

On 13 Aug, 06:53, "Shakespear e" <what...@xs4all .nlwrote:

"Gloops" <glo...@invalid .zailes.orgschr eef in berichtnews:g7s pnh$ett$1@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.Connectio n

' Connection String. - Provider can be OraOLEDB.Oracle or MSDAORA
strCon = "Provider=MSDAO RA;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.Activ eConnection = MyCon

' Name of stored procedure
MyCommand.Comma ndText = "BGS.BOREHOLE_G EOLOGY_P4"
' Command type
MyCommand.Comma ndType = adCmdStoredProc
MyCommand.Comma ndTimeout = 15

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

Set MyParam2 = New ADODB.Parameter
MyParam2.Type = adVarChar
MyParam2.Size = 2
MyParam2.Direct ion = adParamInput
MyParam2.Value = [Forms]![frmSobiMain]![RT]
MyCommand.Param eters.Append MyParam2

'etc for as many parameters as required

'Execute stored procedure
MyCommand.Execu te

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]

Re: Access frontal interface for Oracle database

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]

Re: Access frontal interface for Oracle database

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]

Re: Access frontal interface for Oracle database

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]

Re: Access frontal interface for Oracle database

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]

Re: Access frontal interface for Oracle database

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.Connectio n

' Connection String. - Provider can be OraOLEDB.Oracle or MSDAORA
strCon = "Provider=MSDAO RA;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.Activ eConnection = MyCon

' Name of stored procedure
MyCommand.Comma ndText = "BGS.BOREHOLE_G EOLOGY_P4"
' Command type
MyCommand.Comma ndType = adCmdStoredProc
MyCommand.Comma ndTimeout = 15

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

Set MyParam2 = New ADODB.Parameter
MyParam2.Type = adVarChar
MyParam2.Size = 2
MyParam2.Direct ion = adParamInput
MyParam2.Value = [Forms]![frmSobiMain]![RT]
MyCommand.Param eters.Append MyParam2

'etc for as many parameters as required

'Execute stored procedure
MyCommand.Execu te

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]

Re: Access frontal interface for Oracle database


"Gloops" <gloops@invalid .zailes.orgschr eef in bericht
news:g7vfgo$3rv $1@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]

Re: Access frontal interface for Oracle database

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.