P
Plateriot
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:=xlExternal)
.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?
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:=xlExternal)
.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?