SQL query with loop

[Hatye]

Hi all

I am using this code to connect to SQL server to fetch some data:

Sub DataLoop()

Dim Conn As New ADODB.Connection
Dim recset As New ADODB.Recordset
Dim sqlQry As String, sConnect As String


FrDt = Range("B1").Value
ToDt = Range("B2").Value




sqlQry = "SELECT COUNT(VoNo) from dbo.SupTr where ValDt between " & FrDt & " and " & ToDt & " and VoTp=21"
sConnect = "Driver={SQL Server};Server=[server name]; Database=xxxx;Trusted_Connection=yes;"


Conn.Open sConnect


Set recset = New ADODB.Recordset


    recset.Open sqlQry, Conn
    Sheets("Ark1").Cells(7, 3).CopyFromRecordset recset
    recset.Close


Conn.Close


Set recset = Nothing


End Sub

This works great, but I would like to add it into a loop from row 7 and until the end of the list.
I would like to enter the database ('xxxx' above) from cell value A7 (and down the list) and insert the return value (recset) into C-column in the same row.

 

[mse330]

Hey Hatye

Try the below code

Sub DataLoop()
Dim Conn As New ADODB.Connection
Dim recset As New ADODB.Recordset
Dim sqlQry As String, sConnect As String
FrDt = Range("B1").Value
ToDt = Range("B2").Value
For x = 7 To Range("A" & Rows.Count).End(xlUp).Row
    sqlQry = "SELECT COUNT(VoNo) from dbo.SupTr where ValDt between " & FrDt & " and " & ToDt & " and VoTp=21"
    sConnect = "Driver={SQL Server};Server=[server name]; Database=" & Cells(x, 1) & ";Trusted_Connection=yes;"
        Conn.Open sConnect
        Set recset = New ADODB.Recordset
            recset.Open sqlQry, Conn
            Sheets("Ark1").Cells(x, 3).CopyFromRecordset recset
            recset.Close
        Conn.Close
        Set recset = Nothing
Next x
End Sub

 

[Hatye]

Excellent, thank you so much

Hey Hatye

Try the below code

Sub DataLoop()
Dim Conn As New ADODB.Connection
Dim recset As New ADODB.Recordset
Dim sqlQry As String, sConnect As String
FrDt = Range("B1").Value
ToDt = Range("B2").Value
For x = 7 To Range("A" & Rows.Count).End(xlUp).Row
    sqlQry = "SELECT COUNT(VoNo) from dbo.SupTr where ValDt between " & FrDt & " and " & ToDt & " and VoTp=21"
    sConnect = "Driver={SQL Server};Server=[server name]; Database=" & Cells(x, 1) & ";Trusted_Connection=yes;"
        Conn.Open sConnect
        Set recset = New ADODB.Recordset
            recset.Open sqlQry, Conn
            Sheets("Ark1").Cells(x, 3).CopyFromRecordset recset
            recset.Close
        Conn.Close
        Set recset = Nothing
Next x
End Sub

 

[mse330]

Glad to help & thanks for reporting back