Hi,
I create Pivot Table in VBA from Access database (about 200 000 records). VBA load i.e. 1,7 million combination records and near 600 000 combination the program "fall down".
I use Excel 2000 and MS Access 2003.
Program code:
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.OptimizeCache = True
.Connection = Array("ODBC;DSN=Database MS Access;", _
"DBQ=C:\Temp\Data.mdb;", _
"DefaultDir=C:\Temp\;", _
"Driver={Microsoft Access Driver (*.mdb)};", _
"DriverId=25;FIL=MS Access;", _
"MaxBufferSize=8192;", _
"MaxScanRows=8;", _
"PageTimeout=5;", _
"SafeTransactions=0;", _
"Threads=3;", _
"UserCommitSync=YES;", _
"UID=admin;PWD=password)
.CommandType = xlCmdSql
.CommandText = Array("SELECT Table.Jedna,", _
"Table.Dva ", _
"FROM 'C:\Temp\Data.mdb'.Table Table")
.CreatePivotTable TableDestination:="'[Test.xls]List1'!R1C1", _
TableName:="Pivot Table"
End With
Thank you for your help.