Does a Pivot External Source to SQL Server stay connected?
 

I have a routine that passes different parameters to a database in a loop,
and for each parameter, it creates a pivot table on a separate tab.

I was previously connecting to an MS Access database, but I converted the
Pivot Table's VBA so that it could read from SQL Server...

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.Connection = _
"ODBC;DRIVER=SQL Server;SERVER=" & strServerName & ";UID=" & strUID
& ";PWD=" & strPWD & ";MaxBufferSize=2048;PageTimeout=10;"
.CommandType = xlCmdSql
.CommandText = Array(strSQL)
.CreatePivotTable TableDestination:= _
"'[" & ThisWorkbook.Name & "]" & PivotLocation,
TableName:=strPivotName, _
DefaultVersion:=xlPivotTableVersion10
End With

But now, it only succeeds in the first passed parameter of the loop. i.e.
the next parameter returns an empty pivot table.

The reason I haven't provided the code for the loop, is because I only
changed the above code.

What is it about SQL Server that would change being able to loop this vs
being able to loop this in MS Access?