VBA Connection to Oracle - Runtime Error 91

[nigelh]

I am trying to establish a connection from an Excel workbook to an Oracle DB both to insert data and to extract data. I have had this type of cnnection working before, but to SQL Server. Unlike a previous post, I do not get a Runtime error 91 when opening the connection, but when I try to set the ActiveConnection (highlighted in the code below).

I have tried both the "msdaora" and "OraOLEDB.Oracle" as the provider, but neither resolves the problem. I have the ActiveX Data Objects 2.8 Library installed as well.

Relevant code fragment is as follows (note the parameters to the connection string are passed into this routine):

dim dboCn as ADODB.Connection
dim dboCmd as ADODB.Command
dim dboParams as ADODB.Parameters
dim strConnectString as string

strConnectString = "Provider=msdaora;Data Source=" & strDBName _
   & ";User Id=" & strUserId & ";Password=" & strPassword   

set dboCn = new ADODB.Connection
dboCn.Open (strConnectString)

if dboCn is Nothing then exit sub

set dboCmd.ActiveConnection = dboCn

set dboParams = dboCmd.Parameters

Can anyone give guidance on the problem?

Thanks

 

[xenou]

The syntax looks wrong to me. You don't set the command activeconnection, you just assign it:

Replace:

Set dboCmd.ActiveConnection = dboCn

With:

dboCmd.ActiveConnection = dboCn

There seem to be other problems as well - you need to create and append parameters, so that looks wrong too. And your connection will not be nothing if its been created as an object, even if the connection fails to open - so that test doesn't work.

In short, I'm not sure where you got your code from but it was a bad example!

ξ

 

[nigelh]

Thanks, Xenou.

I have used the "Set .." command successfully before - this time, however, I had forgotten part of the declaration:

dim dboCmd as New ADODB.Command

With that declaration in place, I now get through to the problems you pointed out with the parameters - I haven't worked with these before, only with recordsets being returned from the database. The code fragment in the example was boiler-plate: I will go away and research further: if there are some good examples out there on the net, would be grateful for a URL.

Nigelh

 

[RoryA]

I would recommend you do not use Dim ... As New - do it as two separate lines:

dim dboCmd as ADODB.Command
Set dboCmd = New ADODB.Command

 

[xenou]

For parameters try:
http://www.vb6.us/tutorials/using-ado-and-stored-procedures-vb6

command object are not used as much as recordsets, but in general (off the top of my head):

Dim cmd as ADODB.Command
Dim cn As ADODB.Connection

Set cmd = new ADODB.Command
set cn = new ADODB.Connection

Call cn.Open(Connection_String)

With cmd
    .ActiveConnection = cn
    .CommandText = SQL_String
    .CommandType = x
End with

Edit, I mean, not that command objects aren't used as much (They are used a lot), but that there's more introductory examples on the web involving recordset objects than command objects. Still, there's plenty out there. Just start googling with ADO in the search terms.