Running Oracle's Stored Procedure 1

[orna]

Hi

I need to run an Oracle procedure.
Get this error "cannot execute a select query"
Can someone See the problem here??
Thanks for any help

Set qryOracleProc = CurrentDb.CreateQueryDef("")
qryOracleProc.Connect= "ODBC;DSN=ORAxx;PWD=xxxx;DBA=W
sql = "begin OracleProc(1);end;"
qryOracleProc.sql = sql
qryOracleProc.ReturnsRecords = False
qryOracleProc.ODBCTimeout = 60
qryOracleProc.Execute

 

[PHV]

I'd use an ADODB.Command object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Fattire]

PHV is correct (as always)...

Dim con1 As New ADODB.Connection 'con to oracle

con1.Open "Provider=MSDAORA;" & "Data Source=DATABASE;" & "User Id=APPSLOGIN;" & "Password=APPSPWD"

 

[orna]

Thanks PHV & Fattire

I Tried the connection:

con1.Open "Provider=MSDAORA;" & "Data Source=MyDATABASE;" & "User Id=MyUser;" & "Password=MyPWD"

I get an error "Oracle Error Qccurred".

 

[HarleyQuinn]

You need to substitute in the values MYDATABASE, MyUser and MyPWD with the values relevant to your DB.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

If they're variables try something like:

"Provider=MSDAORA;Data Source=" & MyDATABASE & ";User Id=" & MyUser & ";Password=" & MyPWD

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[orna]

Thanks HarleyQuinn
I did insert the right parameters and yet the same error rise.

 

[PHV]

Have a look here:

http://www.connectionstrings.com/?carrier=oracle

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[orna]

didn't help
thaw

 

[HarleyQuinn]

What Oracle error do you get returned?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[orna]

the connection works:
con1.Open "Provider=MSDAORA;Data Source=xxxx;User Id=yyyy;Password=zzzz"

and i build a pass-through query - "Test".

How can i run the query through con1?

 

[HarleyQuinn]

If you want to do this through ADO then use something like:

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection

cn.Open ("Your connection string...")

Set rs = cn.Execute("The SQL from your pass through query...")
    'you've got the recordset, use it for what you want

'Clean up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Please note though, running a query on an Oracle DB through ADO (as shown above) is generally significantly different code than running an SP from VBA.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[orna]

HarleyQuinn, you did it
Many Thanks!!!!!!!

 

[HarleyQuinn]

Glad I could help, thanks for the star

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.