Vito Vito - 5 months ago 18
SQL Question

How to set the parsing query result range without knowing the number of records?

I run SQL query in excel through excel-vba& ADO.
I parse the result using looping
Then ,i find that i have to know how many records sql will generate before parsing the result .

In reality , I don't know the numbers of query result records before generating the query .

Any functions or method can let me know so that i can put on the loop statement ?

(i have got insight of .Fields() .Properties() ,but not work )

Sub sbADO()
Dim sSQLQry As String
Dim ReturnArray

Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset

Dim DBPath As String, sconnect As String



DBPath = ThisWorkbook.FullName

'You can provide the full path of your external file as shown below
'DBPath ="C:\InputData.xlsx"

sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"

Conn.Open sconnect
sSQLSting = select ....' it is too long so i choose to skip

Set rs = Conn.Execute(sSQLSting)



Do While Not rs.EOF




'Officer = rs.Fields(i).Value
For j = 5 To 29 ' THIS IS THE MAIN CODE I NEED TO IMPROVE
'worksheet1.Cells(7, 11) = rs.Properties.Count
worksheet1.Cells(j, 1) = rs.Fields(0).Value
worksheet1.Cells(j, 3) = rs.Fields(2).Value
worksheet1.Cells(j, 4) = rs.Fields(3).Value
worksheet1.Cells(j, 7) = rs.Fields(6).Value



' Insert data to your worksheet here




rs.MoveNext
Next j


Loop


rs.Close




End Sub





For j = 5 To 29 '
next j


This is the range I should set that affect the range of worksheet parsing .There is totally 24 numbers of query record result in this case .If I set j too large (e.g. To 30), it will prompt out 3021 error -BOF & EOF should be true .Hence , the range should fit the numbers of record well .

Answer

You don't need a loop for j. Just increment j after all the cell assignments and before the end of the do loop.

Sub sbADO()
    Dim sSQLQry As String
    Dim ReturnArray

    Dim Conn As New ADODB.Connection
    Dim mrs As New ADODB.Recordset

    Dim DBPath As String, sconnect As String
    DBPath = ThisWorkbook.FullName

    'You can provide the full path of your external file as shown below
    'DBPath ="C:\InputData.xlsx"

    sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"

    Conn.Open sconnect
    sSQLSting =  select ....  ' it is too long so i choose to skip

    Set rs = Conn.Execute(sSQLSting)
    j = 5
    Do While Not rs.EOF
        'worksheet1.Cells(7, 11) = rs.Properties.Count
        worksheet1.Cells(j, 1) = rs.Fields(0).Value
        worksheet1.Cells(j, 3) = rs.Fields(2).Value
        worksheet1.Cells(j, 4) = rs.Fields(3).Value
        worksheet1.Cells(j, 7) = rs.Fields(6).Value
        j = j + 1
        rs.MoveNext

    Loop

    rs.Close
End Sub