I created an app that uses ADO to query Oracle and populate spreadsheets
with the data.
When the app was put on a user's 2003 platform, some issues arose.
The connectionString provider and the record set use had to change.

This was the old code:

Set Conn1 = New ADODB.Connection

Conn1.ConnectionString = _
"Provider=MSDAORA.1;" & _
"Password=" & pwd & ";" & _
"User ID=" & uid & ";" & _
"Data Source=" & dbs & ";" & _
"Persist Security Info=True"

Set RS1 = New ADODB.Recordset

Conn1.Open

RS1.ActiveConnection = Conn1

Dim sSQL As String
sSQL = ""

' query deleted

RS1.Open sSQL

'need to check if no data returned!
If RS1.BOF Then
MsgBox ("No Data for Range")
ActiveSheet.Delete
RS1.Close
Conn1.Close
Exit Sub
End If

[A3].CopyFromRecordset RS1

RS1.Close
Conn1.Close


The new code looks like this:

Set Conn1 = New ADODB.Connection

Conn1.ConnectionString = _
"Provider=OraOLEDB.Oracle;" & _
"Password=" & pwd & ";" & _
"User ID=" & uid & ";" & _
"Data Source=" & dbs & ";" & _
"Persist Security Info=True"

Set RS1 = New ADODB.Recordset

Conn1.Open

RS1.ActiveConnection = Conn1

Dim sSQL As String
sSQL = ""

' query deleted

RS1.Open sSQL

'need to check if no data returned!
If RS1.BOF Then
MsgBox ("No Data for Range")
ActiveSheet.Delete
RS1.Close
Conn1.Close
Exit Sub
End If

i = 3
j = 0
While Not RS1.EOF
For Each fld In RS1.Fields
j = j + 1
Cells(i, j).Value = fld.Value
Next fld

i = i + 1
j = 0
RS1.MoveNext
Wend

RS1.Close
Conn1.Close


I can almost understand the provider changing, but not the record set
use.
Is there another way around this problem, or a better solution?

Are there any other incompatibilities that I have to watch out for?
We are getting ready to roll out the application...

Thanks for any insights!


--
cesw
------------------------------------------------------------------------
cesw's Profile: http://www.excelforum.com/member.php...o&userid=27117
View this thread: http://www.excelforum.com/showthread...hreadid=469935