Oralce Connection

[Jim Heavey]

I am trying to translate the code generated by the macro recorder to straight
vb code where i can caret and manage the code my self. I know that I have
an Oracle driver installed.

Anyway I am not sure what type of a provider I should be using, I persume
something which would say Oracle, but I do not see that generated in the
code. Here is what was generated.

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _

"ODBC;DSN=PRG_OPS;UID=YYYYYYYY;PWD=XXXXXXX;DBQ=EDSSP.WORLD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM

When I attempt to write the code like the following:

Dim cnn As New ADODB.Connection
Dim strCnn As String
Dim iTry As Integer

strCnn = "Provider=OraOLEDB.Oracle.1;Password=operations;User
ID=PRG_OPS;Data Server=EDSSP.WORLD;Persist Security Info=True"

The compiler stops on the Dim Statement (New ADODB.Connection). Why does it
not recognize this?

Am I using the provider?

 

[Tim Williams]

You must add a reference to the ADO object library (or use late binding).

To add a reference go to Tools >> References

To use late binding:

Dim cnn As Object
set cnn = createobject("ADODB.Connection")

Tim