My guess would a memory leak in MS Query. I could not see anything with your sample so I extended the database to 30k lines. I could then see the Excel memory usage slowly increasing with each query. I coded it to repeat and after a while I managed to use most of the memory and and received the i/o error.
Then I rewrote the query using ADO and put the 30k lines into an Access db. I asked it to query 1000 times. It is still running but the memory looks ok.
rough code based on Access.
Sub LoadData(TheType As String)
' This sub requires a reference to Microsoft Active X Data Object (ADO) 2.x
' In the VBE Tools > references and check the required object
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ws As Worksheet
Dim sSQL As String
Dim DataSource As String
Dim FieldCounter As Integer
DataSource = "c:\Downloads\Test.mdb"
Set ws = ThisWorkbook.Worksheets("QueryTable")
If TheType = "period" Then
sSQL = sSQL + " SELECT Contract, Period, TheValue FROM TestTable"
sSQL = " SELECT Contract,[Employee Name], TheValue FROM TestTable "
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & DataSource & ";"
Set rs = New ADODB.Recordset
rs.Open sSQL, cn, adOpenStatic, adLockOptimistic
For FieldCounter = 0 To rs.Fields.Count - 1
ws.Cells(1, FieldCounter + 1).Value = rs(FieldCounter).Name
' tidy up
Set rs = Nothing
Set cn = Nothing
Set ws = Nothing