Public Function go_merchandising()
Dim cn As Object:               Set cn = CreateObject("ADODB.Connection")
Dim rst As Object:              Set rst = CreateObject("ADODB.recordset")
Dim ex As Object:               Set ex = CreateObject("Excel.Application")

Dim wb, sh, pivot_cache As Object

cn.Provider = "Microsoft.ACE.OLEDB.12.0"
cn.ConnectionString = "Data Source=...DB.accdb"
cn.Open

rst.Open "SELECT top 100 * from table", cn

ex.Visible = True
Set wb = ex.workbooks.Add
Set sh = wb.ActiveSheet

Set pivot_cache = wb.PivotCaches.Create(2) 'пробовал и .add
Set pivot_cache.Recordset = rst 'вот тут ошибка!!!!!!!!!!! 1004 ошибка
    pivot_cache.CreatePivotTable sh.cells(1, 1), "pivot_temp"



End Function

Public Function go_merchandising()
Dim cn As Object:               Set cn = CreateObject("ADODB.Connection")
Dim rst As Object:              Set rst = CreateObject("ADODB.recordset")
Dim ex As Object:               Set ex = CreateObject("Excel.Application")

rst.CursorLocation = adUseClient 'создать на клиенте

Dim wb, sh, pivot_cache As Object

cn.Provider = "Microsoft.ACE.OLEDB.12.0"
cn.ConnectionString = "Data Source=...DB.accdb"
cn.Open

rst.Open "SELECT top 100 * from table", cn

ex.Visible = True
Set wb = ex.workbooks.Add
Set sh = wb.ActiveSheet

Set pivot_cache = wb.PivotCaches.Create(2) 'пробовал и .add
Set pivot_cache.Recordset = rst 'rst.CursorLocation = adUseClient 'создать на клиенте - решило проблему
    pivot_cache.CreatePivotTable sh.cells(1, 1), "pivot_temp"



End Function

Range("A1").CopyFromRecordset rst