I am making a few attempts to batch process several query fetches to a SQL Server database, to pull data, in batches. If I do this in one single pass, it's just too much for Excel, and probably for basic SQL. I know for a fact SQL Server can handle much, much more, but this is NOT a SQL Server environment... it's basically an Excel environment. So, anyway, I'm trying to figure out a way to loop through an array, in steps, and process each step sequentially.
I am trying to do something like this... query rows 1-100 and process, query rows 101-200 and process, query rows 201-300 and process, etc, etc, until the end of the array, which is a bunch of unique IDs, all listed in ColumnA.
Here is the code that I am testing.
Sub TryMe()
'Initializes variables
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String
Dim lRow As Long
Dim lCol As Long
Dim myRange As String
Dim rng As Range, cell As Range
Dim MatDate As String
pswd = "pswd"
user = "user"
dbName = "DB"
server = "serverName.database.Windows.net"
'Setup the connection string for accessing MS SQL database
ConnectionString = "Provider=MSDASQL;Extended Properties=""Driver={ODBC Driver 17 for SQL Server};pwd=" & pswd & ";uid=" & user & ";Server=" & server & ";database=" & dbName & """"
'Opens connection to the database
cnn.Open ConnectionString
'Timeout error in seconds for executing the entire query; this will run for 15 minutes before VBA timesout, but your database might timeout before this value
cnn.CommandTimeout = 900
'MatDate = "'12/11/2019'"
'Find the last non-blank cell in column A(1)
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
LastFind = Cells(Rows.Count, 2).End(xlUp).Row + 1
Set rng = Range("A2:A" & LastRow)
stepbegin = 2
stepend = 100
For Each cell In rng
myRange = "'" & Join(Application.Transpose(Range("A" & LastFind & ":A" & stepend).Value), "','") & "'"
'myRange = Left(myRange, Len(myRange) - 1)
StrQuery = "Select * FROM myTable WHERE my_ID IN (" & myRange & ")"
'Performs the actual query
rst.Open StrQuery, cnn
'Dumps all the results from the StrQuery into cell A2 of the first sheet in the active workbook
Worksheets("Sheet1").Range("A2").CopyFromRecordset rst
LastFind = Cells(Rows.Count, 2).End(xlUp).Row
stepend = LastFind + 100
Next cell
End Sub
The thing is, I don't want to go cell by cell, but rather chunk by chink, so processing 100 cells at a time, or 1000 cells at a time. It has to be a batch job. I can't do the whole thing in one single pass. I tried a few different ways, and each time the job times out. I'm open to any and all ideas. The simpler, the better.
