Re: Access frontal interface for Oracle database
Date: Wed, 13 Aug 2008 01:02:04 -0700 (PDT)
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,
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 anextract 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]
' 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