Thanks much DM Unseen! Your insight was key to getting the following
code to work. Just seems too bad that you have to use an excel sheet
range as opposed to a variable to automatically change the parameter.

thanks again!


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 ID() As String
Dim Royalty() As Integer
Dim Class() As Integer
Dim MineDate() 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)

'*** 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
Select Case Len(StartNum)
Case Is = 1: First = "MOPB00000" & StartNum
Case Is = 2: First = "MOPB0000" & StartNum
Case Is = 3: First = "MOPB000" & StartNum
Case Is = 4: First = "MOPB00" & StartNum
Case Is = 5: First = "MOPB0" & StartNum
Case Else: First = "MOPB" & StartNum
End Select

Select Case Len(EndNum)
Case Is = 1: Last = "MOPB00000" & EndNum
Case Is = 2: Last = "MOPB0000" & EndNum
Case Is = 3: Last = "MOPB000" & EndNum
Case Is = 4: Last = "MOPB00" & EndNum
Case Is = 5: Last = "MOPB0" & EndNum
Case Else: Last = "MOPB" & EndNum
End Select

Application.ThisWorkbook.Worksheets("Query").Range ("I1") = First
Application.ThisWorkbook.Worksheets("Query").Range ("I2") = Last

Set Param1 = qt.Parameters.Add(First, xlParamTypeVarChar)
Param1.SetParam xlRange, Range("Query!I1")
Set Param2 = qt.Parameters.Add(Last, xlParamTypeVarChar)
Param2.SetParam xlRange, Range("Query!I2")

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

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

Dim J As Long, v As Variant, OldCount As Long
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


End Sub
--------------------


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