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.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!
>
>
Received on Wed Aug 13 2008 - 15:47:14 CDT