So the final code boils down to less than a page. Thanks for the tips.


Code:
--------------------
Sub loopingquery()

Dim qt As QueryTable, Param1 As Parameter, Param2 As Parameter
Dim sqlstring As String, connstring As String
Dim StartNum As Variant, EndNum As Variant, First As String, Last As String
Dim Count As Long
Dim J As Long, v As Variant, OldCount As Long

'*** Setting up the query table ***
ThisWorkbook.Worksheets("Query").Activate
sqlstring = "SELECT sstn_surface_samples.sample_number, sstn_surface_samples.Royalty, sstn_surface_samples.Type, sstn_surface_samples.Mined FROM Fusion_Central.dbo.sstn_surface_samples sstn_surface_samples WHERE (sstn_surface_samples.sample_number=? AND sstn_surface_samples.sample_number<=?)"
connstring = "ODBC;DSN=central;UID=admin;PWD=xyz;APP=Micros oft Office 2003;WSID=USCCWEBMETLURGI;DATABASE=Fusion_Central; Network=DBMSSOCN"

With ActiveSheet.QueryTables.Add(Connection:=connstring , Destination:=Range("A1"), Sql:=sqlstring)
End With

Set qt = Sheets("Query").QueryTables(1)
Set Param1 = qt.Parameters.Add("First", xlConstant)
Set Param2 = qt.Parameters.Add("Last", xlConstant)

'*** Looping through the samples in the Database _
'*** in chunks of 65,000 and loading them into the _
'*** appropriate arrays ***

Do

StartNum = EndNum + 1
EndNum = StartNum + 64999

First = "MOPB" & Format(StartNum, "000000")
Last = "MOPB" & Format(EndNum, "000000")

Param1.SetParam xlConstant, First
Param2.SetParam xlConstant, Last

With qt
.RefreshStyle = xlOverwriteCells
.BackgroundQuery = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With

Range("A2").Select
If Selection.Value = "" Then Exit Do

Range(Selection, Selection.Offset(0, 3)).Select
Range(Selection, Selection.End(xlDown)).Select

Selection.Name = "Data"
v = [Data]
OldCount = Count
Count = Count + UBound(v, 1)
ReDim Preserve ID(Count)
ReDim Preserve Royalty(Count)
ReDim Preserve Class(Count)
ReDim Preserve MineDate(Count)
For J = 1 To UBound(v, 1)
ID(J + OldCount) = v(J, 1)
Royalty(J + OldCount) = v(J, 2)
Class(J + OldCount) = v(J, 3)
MineDate(J + OldCount) = v(J, 4)
Next J

Loop
--------------------


--
Laurin
------------------------------------------------------------------------
Laurin's Profile: http://www.excelforum.com/member.php...o&userid=26832
View this thread: http://www.excelforum.com/showthread...hreadid=491841