VBA creating Pivot Table
 

hi all,
i'm trying to create a pivot table using vba in excel.
everytime i run the code i get an error message stating
the following:

"Run-time error 1004:

An operation that uses the database driver could not be
completed.

If the driver is a MS driver, make sure the driver file
isn't damaged, and if it is, reinstall the driver by
reinstalling MS Query. Etc..."

My code breaks down in the CreatePivotTable section. The
pivot table cache is there, and, I have tested placing the
cache on a worksheet, which works fine. I have
reinstalled excel on a couple of occasions, to no avail.
Don't have any idea how to fix this. I've tried four
different ways to create the pivot table, all with no
luck. Any help is most appreciated. This is my first
time coding for a pivot table so here's my code:

Sub CrPivotTable(colct)
Dim pivCache As PivotCache
Dim pivTab As PivotTable
Dim ptfield(), fields1, fields2() As String
Dim icount, i As Integer
Dim pcRange As Range
Dim rsTest As Recordset

'create pivot table and add necessary fields

'This section creates the worksheet to be updated
'Delete "Report" sheet

Application.DisplayAlerts = False
ActiveWorkbook.Worksheets("Report").Delete
Application.DisplayAlerts = True

Worksheets.Add
ActiveSheet.Name = "Report"


'Define columns
icount = colct

ReDim ptfield(icount), fields2(icount)


For i = icount - 12 To icount
ptfield(i - 12) = Worksheets("Data").Cells(1, (i))
Next

If myRS.EOF Then myRS.MoveFirst


'Define cache

ActiveSheet.Name = "Report"

With ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlExternal)

Set .Recordset = myRS

.CreatePivotTable( _
TableDestination:=Range("A5"), _
TableName:="PTRAX")

End With