reference is not valid message for pivot tables based on Access query

I've looked around but can't find an answer to this.

I have a workbook - an xlt - that has 6 worksheets that pull data from 
different queries in the same Access database.

I have several worksheets with on pivot table each.  The pivot tables are 
based on dynamic named ranges in the external data worksheets.  I use 
dynamic ranges because I have some adjacent calculated columns in the 
worksheets.

The workbook has only 6 pivot caches - the same as the number of worksheets 
that pull data.  I copied pivot tables from sheet to sheet so that they'd 
use the same cache, where appropriate.  Some caches feed only one pivot 
table, some feed several pivot tables.

All the pivot tables are set to refresh on open, as are the external data 
ranges.

This was all working fine, but today I started getting multiple "Reference 
is not valid" messages after clicking "enable automatic refresh" when 
opening the xlt.  If I click on a pivot table, I get a "not a valid pivot 
report" message (or something close to that, I don't remember exactly).

One of the last things I did before this started was to change a calculated 
field in the Access query that's included in all the queries that these 
sheets pull.  I changed a Reporting Quarter field from numeric to text, and 
the values from 1,2 ... to "Quarter 1"...

I'm also wondering if it's objecting to multiple pivot tables based on a 
single cache that's based on a dynamic range in a sheet that pulls from 
Access.

Thanks in advance,

Doug 


0
nobodyhere (32)
2/19/2009 1:35:17 AM
excel 39879 articles. 2 followers. Follow

4 Replies
976 Views

Similar Articles

[PageSpeed] 15

On Wed, 18 Feb 2009 17:35:17 -0800, "Doug Glancy"
<nobodyhere@replytogroup.com> wrote:


Hi Doug.

>This was all working fine, but today I started getting multiple "Reference 
>is not valid" messages after clicking "enable automatic refresh" when 
>opening the xlt.  If I click on a pivot table, I get a "not a valid pivot 
>report" message (or something close to that, I don't remember exactly).
>
>One of the last things I did before this started was to change a calculated 
>field in the Access query that's included in all the queries that these 
>sheets pull.  I changed a Reporting Quarter field from numeric to text, and 
>the values from 1,2 ... to "Quarter 1"...

Can you make a new extrernal data table in a new workbook from the Access
query?  I know that MSQuery doesn't like some Access functions.  I think
it's because they use different Jet versions, but I don't remember.


>
>I'm also wondering if it's objecting to multiple pivot tables based on a 
>single cache that's based on a dynamic range in a sheet that pulls from 
>Access.
>

I don' t think this is the problem.  At least it has never been a problem
for me.
-- 
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com
0
dkusleika (31)
2/19/2009 3:23:50 PM
Dick,

I figured it out.  I've got the external data all set to delete content on 
save, and the pivot caches all set to refresh on open.  It looks like the 
caches are trying to refresh from external data that hasn't refreshed yet. 
I think I need to control all this from Access, maybe in the WorkBookOpen 
event.

Do you have any suggestions?

thanks,

Doug

"Dick Kusleika" <dkusleika@gmail.com> wrote in message 
news:j5uqp49s6v4uj20nuaph4mue8934rblmjk@4ax.com...
> On Wed, 18 Feb 2009 17:35:17 -0800, "Doug Glancy"
> <nobodyhere@replytogroup.com> wrote:
>
>
> Hi Doug.
>
>>This was all working fine, but today I started getting multiple "Reference
>>is not valid" messages after clicking "enable automatic refresh" when
>>opening the xlt.  If I click on a pivot table, I get a "not a valid pivot
>>report" message (or something close to that, I don't remember exactly).
>>
>>One of the last things I did before this started was to change a 
>>calculated
>>field in the Access query that's included in all the queries that these
>>sheets pull.  I changed a Reporting Quarter field from numeric to text, 
>>and
>>the values from 1,2 ... to "Quarter 1"...
>
> Can you make a new extrernal data table in a new workbook from the Access
> query?  I know that MSQuery doesn't like some Access functions.  I think
> it's because they use different Jet versions, but I don't remember.
>
>
>>
>>I'm also wondering if it's objecting to multiple pivot tables based on a
>>single cache that's based on a dynamic range in a sheet that pulls from
>>Access.
>>
>
> I don' t think this is the problem.  At least it has never been a problem
> for me.
> -- 
> Dick Kusleika
> Microsoft MVP-Excel
> http://www.dailydoseofexcel.com 


0
nobodyhere (32)
2/19/2009 5:01:07 PM
On Thu, 19 Feb 2009 09:01:07 -0800, "Doug Glancy"
<nobodyhere@replytogroup.com> wrote:

>Dick,
>
>I figured it out.  I've got the external data all set to delete content on 
>save, and the pivot caches all set to refresh on open.  It looks like the 
>caches are trying to refresh from external data that hasn't refreshed yet. 
>I think I need to control all this from Access, maybe in the WorkBookOpen 
>event.
>
>Do you have any suggestions?
>

Set the pivot tables not to refresh automatically, and set up a QueryTable
class that refreshes all of the pivot tables once the QT is refreshed.

http://www.dailydoseofexcel.com/archives/2004/06/21/classes-other-events/

A simpler, but less easily extended, method is to just set everything to
manual refresh, and do them one-by-one.  Be sure to use BackgroundQuery =
False

MyQt1.Refresh False
MyQt2.Refresh False
MyQt3.Refresh False
MyPivot1.Refresh
MyPivot2.Refresh

and so on, in your Open event.
-- 
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com
0
dkusleika (31)
2/19/2009 8:58:57 PM
Dick,

Thanks for the suggestions.

Doug

"Dick Kusleika" <dkusleika@gmail.com> wrote in message 
news:7rhrp4hk1e7586ajlfm6de40e6i8f6bi0h@4ax.com...
> On Thu, 19 Feb 2009 09:01:07 -0800, "Doug Glancy"
> <nobodyhere@replytogroup.com> wrote:
>
>>Dick,
>>
>>I figured it out.  I've got the external data all set to delete content on
>>save, and the pivot caches all set to refresh on open.  It looks like the
>>caches are trying to refresh from external data that hasn't refreshed yet.
>>I think I need to control all this from Access, maybe in the WorkBookOpen
>>event.
>>
>>Do you have any suggestions?
>>
>
> Set the pivot tables not to refresh automatically, and set up a QueryTable
> class that refreshes all of the pivot tables once the QT is refreshed.
>
> http://www.dailydoseofexcel.com/archives/2004/06/21/classes-other-events/
>
> A simpler, but less easily extended, method is to just set everything to
> manual refresh, and do them one-by-one.  Be sure to use BackgroundQuery =
> False
>
> MyQt1.Refresh False
> MyQt2.Refresh False
> MyQt3.Refresh False
> MyPivot1.Refresh
> MyPivot2.Refresh
>
> and so on, in your Open event.
> -- 
> Dick Kusleika
> Microsoft MVP-Excel
> http://www.dailydoseofexcel.com 


0
nobodyhere (32)
2/20/2009 5:15:06 PM
Reply:

Similar Artilces:

Dynamic Table Should Always Maintain Borders When Data Is Updated
Dynamic Table Should Always Maintain Borders An Alignment When Data Is Updated Included in the Cases in Which The Updated Cells Were Previously Formatted And Customized By The User ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/communi...

Opening Access without Menus displayed
Access 97 I have created a simple data lookup form for a customer to look up his own jobs in a shared database - there is only the one form in the mdb(e) that auto-opens on application startup. I have unchecked all the Startup functions etc. The program auto-opens the form okay but there is the default menu bar displayed across the top of the application window. The tool bars do not show. Is there a way to open the program and not show ANY menus as well? My form close event also quits the application. I intend to provide this as an mde. Any ideas gratefully appreciated Piri Piri wrote: &...

How to write to the registry in Vista from Access
Hi... This is my first post and I want to know if there is somewhere a code to write to the registry. I have one from someone in the past but I try to use it on Windows Vista and instead of putting the key in the local machine/software it is adding to some virtual folder. Does anyone knows why??? Thanks... This is not really an Access question, but a Vista one. My best guess though is that you need administrative privileges to write to the registry, and Vista by default does not let anyway have those privileges without specifically logging in that way. -- Arvin Meyer, MCP, MVP ht...

Reference Footnotes
How do I enter a footnote at the bottom of a page? The footnotes are important--it's for a college internship assignment. Thanks! -- kasper kasper <kasper@discussions.microsoft.com> was very recently heard to utter: > How do I enter a footnote at the bottom of a page? The footnotes are > important--it's for a college internship assignment. Thanks! You have to create footnotes manually. -- Ed Bennett - MVP Microsoft Publisher ...

Save as in Access 2002
I have a user who is running Office XP Pro on Windows XP Pro and when she tries to export an Access table to Excel she does not have Excel as an option in the drop down list. Any ideas on how to fix this? Look for the file Msexcl40.dll on the harddrive (it should be in the System folder), and use regsvr32.exe to reregister it. This problem is discussed in http://support.microsoft.com/?id=209805 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (No private e-mails, please) "Jonathan Hearn" <jhearn@envirocon.com> wrote in message news:002301c3c0de$09859d10$a50...

Export to Text Files Based on Field Value
I have an Access table with personal information (e.g., first name, last name, birth date, etc.) for thousands of people. The table is sorted by state. I want to be able to run a macro on that table that will create separate pipe delimited text files for each state with all fields included. Any help would be appreciated? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1 Instead of exporting the table, export a query based on the table. You will have to have some code that loops through the states and modifies the quer...

merging 3 spreadsheets into one based upon a single common data field
OK I hope you guys can help me out. Here is the scenario. I have 3 spreadsheets. The main spreadsheet I working with has 500 lines with a unique number string. The next two spreadsheets have about 12000 lines but also contain the same unique string as the first spreadsheet. I want to search the 2nd and 3rd spreadsheet using the first spreadsheets unique id number and then take the information from the 2nd and 3rd spreadsheet and then merge it into the first. Does that make since? Any help would be great. -- xchosen --------------------------------------------------------------------...

Cannot open earlier version of access database
I'm trying to open Northwind database with MS Access 2003. The problem is that a dialog box appears prompting me to go to Database > Tools > Convert Database, however, the dialog box re-appears over a couple of times and disallows the user from doing anything within MS Access. If you are getting the Convert dialog you will need to convert the database to the version that you are working in and save it and then open it next time instead of the original file. During the convert process it will not change the original file. It will make a NEW copy for you to use. I wonder w...

Accessing Money reconciled statements?
Is there any way to access past reconciled statements? For some reason, when I went to reconcile it says that my starting balance doesn't match my ending balance from the last statement. I thought I would find the last statement and enter that ending balance as my starting. Not as easy as I thought. Thanks in advance. You won't be able to do that - if the ending balance is modified, you're just shifting the problem back. You'll need to find where you have either deleted a reconciled transaction, or unreconciled a previously reconciled transaction and make an appropri...

Count Based on Comparison with Two Fields
I recently learned that I can count the number of items in one column that match items in another column by using: =COUNT(MATCH( 'Sheet1'!G2:G15001, 'Sheet2'!A2:A15, 0)) However, I now need to count records on one sheet in which TWO fields match items in both of two separate columns (i.e., a comparison of two columns to two columns and a count of records in the first that have fields matching elements in the second) . My attempt to use the above statement with an AND failed. Can someone explain how to do this? Hi I would suggest that you post a before a...

Pivot table #11
I asked this yesterday but I am having difficulty getting to my original post. Page 1 of this news group keeps brining me to feb 17th.... anyways this is what i asked yesterday: Is there a way I can set up a pivot table with sub categories? i.e. Type of Contact is my heading and under it I have 4 categories (phone, walk-in, etc...) Thanks. A. I got this reply: If you add Type of Contact to the row area, you should see all of the categories. Can you describe the fields in your data table, and how you'd like them to appear in the pivot table? My answer to this is: I would like...

Message Filtering on MS Outlook 2000
Hi I'm Leo I have a problen on my MS Outlook Message Filtering, this is the scenario. User A sends a message to User B (inside my organisation) and User C (outside) then the message goes to User B Inbox, then User C replies to User B and the message goes to Junk Folder. This is not usually heppening when User C sends Email on User B, normally it goes to User B inbox. We have a Rule that filters the Email Address of the sender so I think that the MS Outlook filters the message thats why it goes to Junk Folder. My question is how did MS Outlook filters messages even if we do not have Rul...

My OL 2000 will not open-get error message
I have MS Office Premium-all of the other programs work. When I try to open OL2000 I get the the error msg. MS Outlook has encountered a problem and needs to close. I removed MS Office Premium and re-installed it-same problem. I also clicked repair from Control Panel-same problem. -- Roma Does it work correctly when trying to start Outlook in Safe Mode? Start-> Run; outlook.exe /safe For additional troubleshooting steps see; http://www.howto-outlook.com/faq/outlookdoesntstart.htm -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www...

Specific Age Query for a Date Range
Table = Personal Field = Birthdate I need to create a query to show who is of a certain age for January 1, 2008 to December 31, 2008. For example, I need to know who will be 50, 60, 65, 70 and 75. I would like to create the query so that when I click the query to open it, I have to input the age. I've done other queries to show who is of a specific age as of today: AgeYears: DateDiff("yyyy",[Birthdate],Date())-IFF(Format([Birthdate],"mmdd")>Format(Date(),"mmdd",1,0), with criteria of [Age in Years] ... but I cannot figure out how to pull a report...

CRM4
Hi, On CRM4, I obtain an ASP.NET error while trying to access the website of my default organization. The message error is the following : "Caller does not have enough privilege to set CallerOriginToken to the specified value". I didn't find anything on the Web... Does anyone have an idea ? Fabien Majurel I have the same problem, I am going to reinstall and see if that helps Mike "Fabien Majurel" wrote: > Hi, > > On CRM4, I obtain an ASP.NET error while trying to access the website of my > default organization. The message error is the following...

Changing text based on criteria in an access report
Hello, I've got a textbox in a report, some records of which may contain text within { }. I need to find some way to format only that text (which may be in the middle of a string) so that it appears underlined and so that the brackets disappear. To illustrate, I posted a 5KB graphic here: http://img219.imageshack.us/img219/3523/jexamplemb2.png Does anybody have any suggestions or ideas? Thanks in advance! Mitch Access 2007 can format the text for you, using standard HTML for the underlining. Use the Replace() function to swap the braces for the HTML tags. Earlier versions of Ac...

A program is trying to access e-mail #2
When I hit new or reply I get this box saying, "A program is trying to access e-mail addresses you stored in Outlook. Do you want to allow this?" Symantec says it's a Microsoft problem. Microsoft says its a security patch sp3? How do I stop this dam message from poping up???? Outlook 2002 SP3 adds additional properties to the list of those that are affected by the security features. As a result, one of your add-ins needs updated to the latest version - antispam add-ins are a common cause although others are affected by the changes as well. Many add-ins were updated follow...

Has anyone linked MS Access and MS Money?
Greetings. I have a registration database for a school that uses MS Access. Our book keeper uses Quicken, so that all the student names, addresses, etc. needs to be entered twice. I am thinking of migrating the financial records to MS Money, so that we can do some VBA programming or the like to send student data from Access to Money. Does anyone know if this can be done, or have any experience doing this sort of thing? Thanks! Gary See http://umpmfaq.info/faqdb.php?q=10 in specific and http://umpmfaq.info/faqdb.php?cat=3 in general. "GaryDave" <garydave@hotmail.com> w...

duplicate messages #3
Each time I click "send/receive", the same two email messages are sent to my inbox. If i clik three times, the two messages will drop into my inbox for a total of six messages! Understand, i do not have "save messages on the server" selected. I have uninstalled and reinstalled outlook twice with to no avail? Suggestions? Happens to me too. My ISP said it is poorly written spam that fails to complete sending properly, so Outlook times out without completing the task of downloading including recognising what has already been downloaded and what has not. Why can&#...

Message Tracking Center not functioning
The Message Tracking Center has been working fine for many months, however today I tried to use it and received the error message: "The tracking database on SERVER_NAME is not available or the message has left the Exchange organization. Facility: Microsoft Exchange Management ID no: c1032751 Microsoft Exchange Management I've tried turning tracking off and on under the ESM server properties. I've tried changing the folder location (I hopes it reset something). The permissions on the SERVER_NAME.log folder appear to be open for Administrators. Also looking at the tracking ...

HTML is being converted to text in outlook messages
I have a mixed environment of Exchange 2k3 SP2, and Exchange 5.5 SP4. Currently, the Ex5.5 server is acting as the bridgehead, so all inbound/outbound mail passes through the IMC on that box. My problem is with HTML content within emails. I recieve the messages ok (they are displayed as HTML), but when I forward them, the email loses the HTML content, and displays it in plain text. I have checked the "HTML" box setting in the IMC on the Ex5.5 bridghead server. Is there anywhere else that this needs to be enabled? Any help is appreciated. On Thu, 9 Mar 2006 09:20:31 -080...

Pivot in a Join
Can you do a pivot in a join where you pass a value into the pivot table as part of the query? In the following example, I am trying to do my pivot select using a value from the first table (TABLE1), but when I use Value1 in my pivot table, I get an error Invalid Column. I also tried Table1.Value1 but got a can't be bound. SELECT Value1, Value2 FROM TABLE1 INNER JOIN ( SELECT Value1,[2],[3],[4] FROM ( SELECT Category, RatingValue from RatingTable WHERE RatingID = Value1 <---- Problem with Value1 ) AS ...

Simple update Query
Hi, I'm sure I've done this in the past but can't figure how. I have 2 tables "A" and "B" both with the following fields Town; Council I want to update Table "A"'s Council field with the Council information in table "B" Could someone please give me the SQL code to do this. Thanks in advance UPDATE TableA INNER JOIN TableB ON TableA.Town = TableB.Town SET TableA.Council = TableB.Council. IF you can only do this using design view -- Open a new query -- Select the two tables -- Set up a join by dragging from town to town -- add the Cou...

Programmatically creating a pass-through query
Hi, Is there a way to programmatically create pass-through query in Access 2003? Sub test () Dim db As Database Dim strSQL As String Dim qdfs As QueryDefs Dim qdf As QueryDef strSQL ="Some pass-through SQL statement here" Set db = CurrentDb Set qdfs = db.QueryDefs Set qdf = db.CreateQueryDef("qry_PassThru", strSQL) qdf.SQL = strSQL End Sub But the code about will not create a pass-through query, Access thinks it is a regular Access query. Is there a way to specify ...

How to combine two identically structured tables to query them
Hi, I am sure there is a way that you can have a query that effectively takes two separate tables that have the identical column layout and add them together and then perofrm a query on the combined set of data. I just can't remember how to do it. My reason for need in this is that I have a table called "SellerNumbers" that has 4 million rows of data in it and what I want to do to improve performance is archive the oldest 2 years worth of data to an identical table called "SellerNumbersArchive", however the users want to be able to occasionaly run quer...