Driver version: 7.1.10
ADO version 2.6
Hello,
I have an Excel app that sends a batch query with 3 select statements. This
is to reduce network traffic. I must be setting something incorrectly
because I get no data and it appears only 1 recordset is being returned.
This code works fine with MS SQL. Can the driver handle multiple
recordsets?
I've tried using a connect statement with a cursorlocation set to client,
but this results in a 0 RecordCount. With the cursor set to server, I get
a -1 which is to be expected.
Any guidence would be appreciated.
TIA
-patrick
Public Const strsqlA As String = "SELECT feddiv, COUNT(UPC) AS CountOfOZUPC
FROM Chesh_No_OZ_v GROUP BY feddiv;" & _
"SELECT DivID, COUNT(UPC) AS CountOfOZUPC
fROM FL_No_OZ_v GROUP BY DivID;" & _
"SELECT DivID , COUNT(UPC) AS
CountOfOZUPC FROM Loc_No_OZ_v GROUP BY DivID"
' Open compound recordset.
strCnn = "Driver
={Postgresql};Server=myserver;Database=mdc_oz;UID=mcom_user;PWD="
Set Rs = New ADODB.Recordset
mySQLArray = Array(strsqlA, strsqlB, strsqlC, strsqlD, strsqlE,
strsqlCount)
Set Rs = New ADODB.Recordset
WS.Activate
WS.Cells.Select
Selection.ClearContents
WS.Range("A1").Select
X = 2
Rs.Open mySQLArray(ArrayLoop), strCnn
DoEvents
If Rs.RecordCount = 0 Then
MsgBox "No Records are available for your selection"
Rs.Close
Else
'Send Header
fldCount = Rs.Fields.Count
For R = 1 To fldCount
WS.Cells(1, R).Value = Rs.Fields(R - 1).Name
Next R
WS.UsedRange.Font.Bold = True
' Try printing results from each of the 3 SELECT
' statements.
booNext = True
intCount = 1
Do Until Rs Is Nothing
Do While Not Rs.EOF
WS.Range("A" & X).CopyFromRecordset Rs
Loop
Set Rs = Rs.NextRecordset
X = getlastrow(WS, X)
Loop
Application.Selection.CurrentRegion.Columns.AutoFit
Application.Selection.CurrentRegion.Rows.AutoFit
' Close ADO objects
If Not Rs Is Nothing Then
Rs.Close
End If
End If
Next
Set Rs = Nothing
-TIA
Patrick Hatcher
Macys.Com
Legacy Integration Developer