Pivot Table and data from MS Access
 

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 300 000 combination
the program "fall down" - run-time error 1004: Problem with obtain of data.

Environment:
MS Excel is version 2000
MS Access is version 2003
Windows XP SP2

Program code:
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.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. Best regards Radke