• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

Query Table not refreshing

I have a colleague who is not able to refresh a query table.  If he opens a file that has a query table in it with data... clears out a portion of the query table data that was originally saved with the file and right clicks the query table and selects "Refresh Data" nothing happens... the data is not refreshed.  Is this due to a security setting related to query tables?  any thoughts?

Thanks!
0
ClayCanvas
Asked:
ClayCanvas
  • 4
  • 3
  • 2
  • +2
2 Solutions
 
dlmilleCommented:
can you provide a sample with this error?

Dave
0
 
broro183Commented:
Hi,

Hopefully the below helps as I'm assuming you are using excel 2007. Also, I'm off to bed & probably won't check this thread again before tomorrow night.

Is the "refresh" option greyed out?

Is the data actually coming from the expected location?
You can check this in excel 2007 by pressing [alt + A + O] & going through the list of connections. Or, you could select a cell in the QT, right click - Table - External Data Properties, clicking on the button to the right of "name:" and checking the "connection file" &/or "data source" section of the "connection string" in the "Definition" tab.

re the security setting,
Again in excel 2007, you could try:
[alt + T + O], Trust Center (on left), Trust Center Settings (on right), External Content, and adjust the settings as necessary.

My settings are the two which are stated as "not recommended" by MS. However, I recommend you research via search engines & the Help files BEFORE making any changes to these settings. Research will enable you to make an informed decision based on your needs & the potential impacts.

hth
Rob
0
 
ClayCanvasAuthor Commented:
Thanks for your responses Dave & Rob

Dave,

Unfortunately I'm not able to provide a copy of the file due the sensitive nature of the information.  The file is working good for ~30 people including myself.  There's just one person it's not working for and he appears to have an issue on his computer. It worked for him up until late last week and then something changed for him.  I noticed when I sat down on his computer the query tables were not refreshing even when I would manually right click inside the query table data and select "Refresh Data".

Rob,

The individual with the problem is running Excel 2003 on his computer.  To answer your question when I right clicked on the query table data when using his machine it was not grayed out it so I was able to select "Refresh Data" from the right click menu but the table would not refresh.  I tried a couple different query tables within the file and received the same response from all of them.

You mentioned some security settings.  Are there any security settings in Excel 2003 that I can look at that might impact query tables?

Thanks for your help,

Steve


0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
broro183Commented:
hi Steve,

Sorry I haven't responded sooner & am off to bed now. Unfortunately, I currently have no access to an excel 2003 application...

*scratch head* hmmm...
I think your best bet is going to be pressing the good ol' [F1] key on their machine & searching for "security" or "updating external links".
Speaking of which, try pressing [alt + e + k] to bring up the external links dialogbox & (if it exists in 2003) clicking on the [startup prompt...] button. The only other idea is 2003's "odds & sods drawer" - perhaps you'll find a setting in [alt + t + o].

If you file like something more complex/risky (I have never tried this & haven't read the link properly myself!), you could have a read of  http://www.pctools.com/forum/showthread.php?38497-Refresh-Query-in-Excel-2003-(WXP-Pro)

I'm sorry I don't think I can be of any more help, but let us know if this helps & if not I'll go Googling - it's all about the keywords ;-)

Rob
0
 
SiddharthRoutCommented:
ClayCanvas: Have you tried resetting the connection?

Sid
0
 
QlemoC++ DeveloperCommented:
Did you try to edit the query (as Rob already mentioned)? Maybe even with Microsoft Query?
Which data source are you querying?
0
 
ClayCanvasAuthor Commented:
I'm not at work today or tomorrow so I'm somewhat limited on what I can try or have my coworker try in the short term.  Thanks for the inputs so far... I emailed him and asked him to restart his computer just to kind of reset everything including the connection to the network.  

Sid... when you mentioned resetting the connection are referring to logging off the computer and logging back on or is there something that should be done from Excel?

Qlemo ... the source the query table is connected to is an Access database located on our company's network.

Rob... thanks for your feedback.  I tried Alt - e - k.... no response on Excel 2003.  I looked thru the different tabs after selecting Alt - t - o.  I wasn't able to locate anything that would seem to be connected to query tables or external connections (maybe I overlooked something?).  I read the info from the link you provided.  It looks like the info from the link is referring to modifying the registry to control whether or not there's a pop up when the query table is set up to automatically refresh when there's a change.  Did I misinterpret the info from the linked web page?  Is there more to it?  Currently I don't have the query tables setup to automatically refresh so if I'm understanding correctly I'm thinking it may not apply directly to the file I'm working on.  Does that sound correct?

Thanks again for the responses

Steve
0
 
SiddharthRoutCommented:
>>>>Sid... when you mentioned resetting the connection are referring to logging off the computer and logging back on or is there something that should be done from Excel?

No I meant disconnecting from Access and rebuilding the connection to Access. :)

Sid
0
 
broro183Commented:
hi Steve,

I've done some Google searching but I haven't found an obvious answer to your issue. I think Sid may be right & that you could need to disconnect & rebuild the connection to Access. It's probably worth trying to do this on both the excel 2003 machine & on a excel 2007 machine as well.

---------------
I used the shortcut key combinations to save myself some typing in the last post. In long-hand [alt + e + k], is Excel's Edit menu followed by the "Links..." option. If there was no response you should be seeing it as "greyed out"/disabled when you look at it via the menu. This means that my "*scratch head*" guess was off the mark!

re the link for changing registry settings,
Yes, your comments do sound correct.
I don't know if "there is more to it" or not. I don't have excel 2003 so I can't test the effect of making the suggested changes (which may not even be possible based on the permissions of the user's profile). It is only a guess based the statement near the end that says
2 Excel will not prompt you when you open a file containing a query or PivotTable that has been configured to refresh automatically. Excel will refresh the query or PivotTable automatically. (http://www.pctools.com/forum/showthread.php?38497-Refresh-Query-in-Excel-2003-(WXP-Pro))
So, it's up to you whether you want to try it or not & see if it works... ;)

Does the user have the same MS Service Packs as everyone else on 2003 machines?

Does the user still have the same "mapped drives" as everyone else?

Did you find anything in excel 2003 when "pressing the good ol' [F1] key on their machine & searching for "security" or "updating external links"?

I'm not sure if this will help you track down the issue or not, but here's a shameless plug for another Kiwi: Rob van Gelder's excellent Query Editor add-in!

hth
Rob
0
 
ClayCanvasAuthor Commented:
Thanks for the different suggestions/ideas.  In the end the solution ended up being re-installing Excel on the users computer.  After the re-install everything worked good.

Steve
0
 
broro183Commented:
Thanks for the points Steve - I'm pleased you got it working :-)

Rob
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now