Re: Access frontal interface for Oracle database

From: purpleflash <kigl_at_bgs.ac.uk>
Date: Wed, 13 Aug 2008 01:02:04 -0700 (PDT)
Message-ID: <2d4c66f1-e066-4988-aedb-d9c42c322752@x41g2000hsb.googlegroups.com>

On 13 Aug, 06:53, "Shakespeare" <what..._at_xs4all.nl> wrote:
> "Gloops" <glo..._at_invalid.zailes.org> schreef in berichtnews:g7spnh$ett$1_at_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! Received on Wed Aug 13 2008 - 03:02:04 CDT