Excel Pivot Table and Disconnected Recordset

[jjob]

I am required to replace the current data supplied to a pivot table and replace it with a disconnected recordset. The recordset is created from a virtual table to which I have added data from 2 sources, a SQL table and an Access table, I can save the recordset to disk and open it and populate a pivot table in a simple test sheet, but attempting to change the Pivot Cache on the live sheet and point it to the disconnected recordset gives the error

1004 The connection for this PivotTable has been deleted.

which is correct, as I was told to delete the inital connection, the problems I now have is, if my recordset is disconnected and is a 'merged recordset' created from 2 SQL sources, why do I need a connection, and if I do, what one do I pick? The SQL server or the Access ?

TIA

 

[SkipVought]

hi,

attempting to change the Pivot Cache on the live sheet and point it to the disconnected recordset

HOW are you doing this?

Are ALL the fields the same in the ORIGINAL and NEW recordsets? If not then that is a problem.

I'm have trouble comprehending how you can access two separate data sources, other than using ActiveX Data Objects in VBA code.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jjob]

I define a recordset known as the 'virtual table' the documentation calls it that, although it is in fact an ADODB recordset - with no connection, it's definition is based on a SQL Server query defined for a Pivot table.

The mechanism is that I open the SQL Server query recordset and add the fields and field definitions from the SQL Server recordset, I then add all the SQL server data records to this Virtual table. Next I iterate other database queries (known as merging tables in the documentation) and add their data to the virtual table. Currently for testing I'm only using one Access database query, this 'merging table' query has the native fields names renamed in the query with 'AS Field' etc - so all are guaranteed to contain a subset of the SQL server fields, and their datatypes are, where needed, converted/adjusted to match the SQL Server datatypes). When testing, I completed this addition of records and closed the Access connection then saved the virtual table as a disconnected recordset to disk. When this persistent recordset is opened up in a test sheet, it works. The problem I have, is that for the live system, I have been asked not to save to disk (although I may have to do that) but to open up the appropriate Pivot workbook and set the Pivot cache to this virtual table(disconnected recordset), before I close it and destroy it ready for the next iteration - BUT , the Pivot cache is in a template, and as part of producing the template, all the connections were removed, now setting the Pivot Cache recordset to the new virtual table recordset produces the error above, and I can find nothing that suitably explains how connections and disconected and persistent recordsets are dealt with in Excel 2007 with Pivot tables etc.

 

[SkipVought]

Are you using VBA? If so post your VBA code and SQL code in forum707 and we can continue exploring a solution there.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jjob]

It is , but I am not able to post any code, I work in a secure environment and nothing can come in or go out - I am writing this from home, so I'm afraid I can't post any actual code.

 

[SkipVought]

Guess you're kinda on your own, then.

You need to look at the PivotTable object and find some VB Help on the PivotCache Connection property.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jjob]

Thanks, but I am hoping someone knows where there is anything useful, I've searched and searched and can find nothing that helps.

 

[SkipVought]

Did you check the help I suggested?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jjob]

First, my apologies for the delay in replying, I was at a seminar last night. Second, thank you for your time and effort on this, and finally, just in case anyone is doing anything similar, here is the history of how I got the thing to work.

So, I have solved my problems, although I am intrigued as to the underlying requirements of a pivot table.

The Connection message was correct, as I understood, but as I was using a disconnected/persistent record set, it had me confused as to why I needed one and I was convinced that I was missing some way of indicating the connection was not needed, failing to discover this elusive method, in the end I opted to do as requested by the error message. I found a copy of the pivot workbook used to create the template and recreated the template, complete with connection. Although the connection was not to the latest database(s) and the associated SQL bore only minor resemblance to the new record set. The presence of the connection stopped the ‘no connection’ error message and got me further along the track. Although I no longer received any error messages, I had no items in the Pivot table still, so I went back to a test workbook we used to prove a disconnected/persistent record set could be assigned to the cache and drive a pivot table.

The test Pivot worked with the original ‘single database’ disconnected/persistent record set file, but failed to work with my record set file, just as my live code failed – no errors, just no data.

I decided the answer had to be with the definition/creation of the Virtual table, as all other code of interest dealing with the record sets and pivot objects, appeared identical between the test bed and the live system. After much experimenting, checking, comparing, and dissecting both persistent record sets, the one difference between the two sets that attracted my attention as being consistent in each row, was that the working set had the data field attributes as 'nullable', but mine had them as 'not nullable' – so grasping at that straw, I added that attribute as adXLNullable (or whatever the constant is) to my field definitions when adding the fields at the creation of my virtual table (rather than defaulting it as I had been doing, and which, oddly enough the test code did too, but that appeared to default the other way to my default), I then ran the code, created a persistent record set and ran against the test pivot - lo and behold it worked.

I now have live code running and the databases being merged into the one record set that drives the Pivot.

My next, but no longer urgent and desperate, step is to find some authoritative documentation that tells me why on earth I need a connection to a database when I want to use a disconnected record set. The record set I create is filled with data from 3 separate databases spread around the country, and these are currently of 2 different types, 1 SQL Server, and 2 Access – with the possibility of other ODBC record sets having to be added, and I run on the fly data type conversions between the data from the disparate data fields when the supposedly identical data resides in different types of data column, so no connection is going satisfy the merged record set as a whole. Which, I guess, is why I was asked to perform this odd exercise in the first place.

 

[SkipVought]

Look at your PivotTable Options. There is a option, ]Save source data with file or not.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jjob]

Thanks, I'll look at that, although I've only been looking at code with the Cache with a sample data set, as I have no control over the templates worksheets, connections, sQL and tables etc, other than I read the data for all this into my code via a control worksheet.