Access frontal interface for Oracle database

#**101**

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

Quote:

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.

Quote:

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!

#**102**

"Gloops" <gloops (AT) invalid (DOT) zailes.org> schreef in bericht
news:g7vfgo$3rv$1 (AT) aioe (DOT) org...
Shakespeare a écrit, le 13/08/2008 07:53 :

Quote:

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

#**103**

"Gloops" <gloops (AT) invalid (DOT) zailes.org> schreef in bericht
news:g7vfgo$3rv$1 (AT) aioe (DOT) org...
Shakespeare a écrit, le 13/08/2008 07:53 :

Quote:

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

#**104**

"Gloops" <gloops (AT) invalid (DOT) zailes.org> schreef in bericht
news:g7vfgo$3rv$1 (AT) aioe (DOT) org...
Shakespeare a écrit, le 13/08/2008 07:53 :

Quote:

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

#**105**

"Gloops" <gloops (AT) invalid (DOT) zailes.org> schreef in bericht
news:g7vfgo$3rv$1 (AT) aioe (DOT) org...
Shakespeare a écrit, le 13/08/2008 07:53 :

Quote:

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

#**106**

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.

#**107**

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.

#**108**

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.

#**109**

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Access frontal interface for Oracle database

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Access frontal interface for Oracle database - 08-13-2008 , 03:47 PM

Re: Access frontal interface for Oracle database - 08-14-2008 , 02:54 PM

Re: Access frontal interface for Oracle database - 08-14-2008 , 02:54 PM

Re: Access frontal interface for Oracle database - 08-14-2008 , 02:54 PM

Re: Access frontal interface for Oracle database - 08-14-2008 , 02:54 PM

Re: Access frontal interface for Oracle database - 08-20-2008 , 03:13 PM

Re: Access frontal interface for Oracle database - 08-20-2008 , 03:13 PM

Re: Access frontal interface for Oracle database - 08-20-2008 , 03:13 PM

Re: Access frontal interface for Oracle database - 08-20-2008 , 03:13 PM