Error on line set pvtCache = dbRecordset.recordset

Hi,

I am getting an error "Application-defined or object-defined error : 1004" on the highlighted line below.

Private Sub CommandButton1_Click()
Dim PvtCache As PivotCache
Dim AppAccess  As Access.Application
Dim dbRecordset As Variant

On Error GoTo errl

Set AppAccess = CreateObject("Access.Application")
AppAccess.OpenCurrentDatabase Sheet32.Cells(14, 3)
Set dbRecordset = AppAccess.CurrentDb.OpenRecordset _
("_FSTEP_BULK_HSFS_REVENUE")
Set PvtCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlExternal)

Set PvtCache.Recordset = dbRecordset
With PvtCache
    .CreatePivotTable TableDestination:=Range("A43"), _
        TableName:="Performance"
End With

With ActiveSheet.PivotTables("Performance")
    .SmallGrid = False
    With .PivotFields("C_4")
        .Orientation = xlRowField
        .Position = 1
    End With
    With .PivotFields("C_2")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With .PivotFields("C_7")
        .Orientation = xlDataField
        .Position = 1
    End With
End With
Exit Sub
errl:
MsgBox Err.Number & " " & Err.Description
AppAccess.CloseCurrentDatabase
Set AppAccess = Nothing
Set dbRecordset = Nothing
Set PvtCache = Nothing
End Sub

I cannot use ADODB or DAO because I am on Win 64-bit machine. But it should still work I assume so what is it that I am doing wrong here?

Can anyone please help me out with this?

Whenever I declare Dim dbRecordset as ADODB.Recordset, it doesn't compile. However I am able to do my operations if I use the Access Object Library. But why am I getting this error?

Well, that I can't. Win 64 bit does not compile ADO.

Private Sub CommandButton1_Click()
Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
 
' Open the connection.
Set cnnConn = New ADODB.Connection
With cnnConn
 .ConnectionString = _
 "Provider=Microsoft.Jet.OLEDB.4.0"
 .Open "G:\Departments\Circulation\MIS\HANDOVER\MIS FORMAT V2\MIS BILLBOARD VERSIONS 3.X\MIS DATABASE.accdb"
End With
 
' Set the command text.
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
 .CommandText = "Select * from _FSTEP_BULK_HSFS_REVENUE"
 .CommandType = adCmdText
 .Execute
End With
 
' Open the recordset.
Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand
 
' Create a PivotTable cache and report.
Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _
 SourceType:=xlExternal)
Set objPivotCache.Recordset = rstRecordset
With objPivotCache
 .CreatePivotTable TableDestination:=Range("A3"), _
 TableName:="Performance"
End With
 
With ActiveSheet.PivotTables("Performance")
 .SmallGrid = False
 With .PivotFields("Pressure")
 .Orientation = xlRowField
 .Position = 1
 End With
 With .PivotFields("Speed")
 .Orientation = xlColumnField
 .Position = 1
 End With
 With .PivotFields("Time")
 .Orientation = xlDataField
 .Position = 1
 End With
End With
 
' Close the connections and clean up.
cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordset = Nothing
Set cnnConn = Nothing

End Sub

If I use this code (with references set to ADODB library 6.0) then I get runtime error 3706 for application defined or object defined error on the line

.Open "G:\Departments\Circulation\MIS\HANDOVER\MIS FORMAT V2\MIS BILLBOARD VERSIONS 3.X\MIS DATABASE.accdb"

https://www.google.ae/#hl=en&safe=of...w=1024&bih=628you will find a lot of issues win Win 64 under these results

I don't have that. You see I am sitting on a Citrix desktop compiling this. There is no reference for that available.

Originally Posted by JosephP

it's not a reference - it's the provider string you should use instead of microsoft.jet.oledb.4.0

This solved it. Thank you for your patience and help!