execute Oracle procedure

[Dave]

Hi,

The code below connects to an Oracle database and executes an Oracle
procedure. This works when developed in original Access db. When another db
is created and the code is copied from the original to the new db and
executed the following Compile error appears: "Invalid use of New Keyword"
for this line:
Set cnMarvin = New Connection

What is needed to reset this instance?

Thank you
Dave


Private Sub Form_Close()

'declare and instantiate the object variables
Dim cnMarvin As Connection
Set cnMarvin = New Connection
Dim rsMLP As Recordset
Set rsMLP = New Recordset
Dim comExecute As Command
Set comExecute = New Command

' connect to MARVIN

With cnMarvin
.Provider = "MSDASQL"
.ConnectionString = "DRIVER={Microsoft ODBC for
Oracle};UID=???????;SERVER=dmrprod;Password=??????"
.Open
End With

comExecute.ActiveConnection = cnMarvin
comExecute.CommandText = "begin marvin.Procedure; end;"
Set rsMLP = comExecute.Execute

comExecute.CommandText = "commit"
Set rsMLP = comExecute.Execute

cnMarvin.Close
End

End Sub

 

[Stefan Hoffmann]

hi Dave,

The code below connects to an Oracle database and executes an Oracle
procedure. This works when developed in original Access db. When another db
is created and the code is copied from the original to the new db and
executed the following Compile error appears: "Invalid use of New Keyword"
for this line:

Dim cnMarvin As Connection
Set cnMarvin = New Connection

What is needed to reset this instance?

There are two Connections you can use in two different libraries: ADODB
and DAO.
As your sample indicates that you like to use ADODB you need to add
ADODB (MS ActiveX Data Objects) as a reference and specify the namespace
in your code:

Dim cnMarvin As ADODB.Connection
Set cnMarvin = New ADODB.Connection

Use the ADODB. prefix also for your recordsets and commands.

' connect to MARVIN

Don't connect to Marvin, he has already enough pain with the diodes down
his left side...)


mfG
--> stefan <--

 

[Dave]

That did it. I'll post this on my wall in big red letters.
Ha ha: not that Marvin - The Marine Resources Environmental Information
System (MARVIN)

Thanks

Dave