VBA - feed Excel pivot table from DAO recordset

[lovallee]

Hello,

I have the code below that is used to feed a pivot table cache with a DAO recordset. The code was from a similar macro used with ADO.

However the code fails at:

Set Workbooks(ActWbk).Worksheets(ActSht).Range("MainPivot").PivotTable.PivotCache.Recordset = DAOrs

Error is: Run-time error '1004' Application-defined or object-defined error

It fails with a DAO recordset but it was working with an ADO recordset Anyone knows why this fails with DAO?

Thank you!

Sub RefreshPivotCache()
   
   Dim aWbkName As String
   Dim aShtName As String
   Dim MainSourceFile As String
   Dim pt As PivotTable
      
'Variables for DAO
   Dim DAOstr As String
   Dim SQLstr As String
   Dim DAOws As DAO.Workspace
   Dim DAOdb As DAO.Database
   Dim DAOrs As DAO.Recordset
   
 'To refer back to active workbook/worsksheet
   ActWbk = ActiveWorkbook.Name
   ActSht = ActiveSheet.Name
   MainSource = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
 
'Create the connection and query strings
    DAOstr = "Excel 8.0;HDR=Yes;IMEX=1;"
    SQLstr = Range("SQLcommand").Value2
   
'Launching DAO
    Set DAOws = DBEngine.Workspaces(0)
    Set DAOdb = DAOws.OpenDatabase(MainSource, False, True, DAOstr)  'write/read
    Set DAOrs = DAOdb.OpenRecordset(SQLstr)
     
   Set Workbooks(ActWbk).Worksheets(ActSht).Range("MainPivot").PivotTable.PivotCache.Recordset = DAOrs
   Set pt = Workbooks(ActWbk).Worksheets(ActSht).Range("MainPivot").PivotTable
   pt.RefreshTable
 
'Close connection
    DAOrs.Close
    Set DAOrs = Nothing
    DAOdb.Close
    Set DAOdb = Nothing

Application.Calculate

End Sub

 

[kjv1611]

You need to post here:
forum707

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Hi,

It fails with a DAO recordset but it was working with an ADO recordset

That's a no-brainer!

My 1978 Vega doesn't run but my 2005 PT Cruiser does. What should I do???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

What should I do???

Put the Vega on eBay and see if you someone pays you $200k+!

http://www.autoblog.com/2008/11/10/ebay-find-of-the-day-1963-lemans-tempest-sells-for-226-521/

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Not for that rusted out bucket of bolts!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lovallee]

Well, thank you for the answer.

But nonetheless all "DAO versus ADO" discussions that I found on internet concludes that when the source database is MS Excel, DAO is 1) faster and 2) does not have the memory leak problem (Microsoft kb319998).

Yes DAO its older, but since DAO is apparently quicker and without memory leaks then I have would have liked to switch to it.

 

[SkipVought]

query the Excel worksheet only while the file is not open in Excel.

Is there any reson to have the source data workbook open during the query?

I use ADO extensively, in addition to MS Query, to acquire data from Oracle, DB2, MS Access and Excel. I never have the source workbook open, and consequently, have never experienced this memor leak problem.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lovallee]

Effectively SkipVought, the memory leak issues arise because the workbook is opened. However, having the data in the same workbook as the related pivot tables makes thing so much easier for the different users of this file.


I have been using SQL on the active workbook to feed pivot tables for a while for the following reasons.
- Database records are sometime located in several worksheets in the active workbook (having the same columns thought).
- There are often more that 65,536 records overall
- One can use SQL "UNION ALL" to merged all worksheets together and therefore bypass the 65,536 record limit often associated with pivot tables in Excel 2003 and prior
- Instead of having calculated fields as Excel formulas, I have calculated fields in the SQL SELECT statement, thereby reducing the file size and reducing the risk of users messing up with an Excel formula (SQL JOINs are quicker and cleaner than VLOOKUP()).

While it is not crucial to use DAO over ADO, I would have like to get rid of the memory leak problem which starts to make Excel very slow after about 4 SQL query refresh on my 46Mb file.

The easiest way to reproduce the DAO recordset bug is to:
1) Start a new workbook (with at least: Sheet1, Sheet2 and Sheet3)
2) in VBA window > Add new module > Menu tools > Reference > add "Microsoft DAO 3.6 Object library"
3) Put any data in Sheet1!A1:B2 which will act as the table.
4) Copy the following code in the module

Sub RefreshPivotCache()
      
  Dim MainSourceFile As String
        
'Variables for DAO
   Dim DAOstr As String
   Dim SQLstr As String
   Dim DAOws As DAO.Workspace
   Dim DAOdb As DAO.Database
   Dim DAOrs As DAO.Recordset

   
 'To refer back to active workbook/worsksheet
   MainSource = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
 
'Create the connection and query strings
    DAOstr = "Excel 8.0;HDR=Yes;IMEX=1;"
    SQLstr = "SELECT * FROM [Sheet1$]"
   
'Launching DAO
    Set DAOws = DBEngine.Workspaces(0)
    Set DAOdb = DAOws.OpenDatabase(MainSource, False, True, DAOstr)  'write/read
    Set DAOrs = DAOdb.OpenRecordset(SQLstr)
  
 'Just to test if the recordset has been created
   Range("Sheet3!A1").CopyFromRecordset DAOrs
    
   Set objPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
   Set objPivotCache.Recordset = DAOrs
   objPivotCache.CreatePivotTable TableDestination:=Range(Sheet2!A1), TableName:="SQLFedPt"
 
'Close connection
    DAOrs.Close
    Set DAOrs = Nothing
    DAOdb.Close
    Set DAOdb = Nothing

End Sub

 

[SkipVought]

You have presented compelling reasons to consider DAO.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}