We help IT Professionals succeed at work.

Excel - DB Query Not Updating

407 Views
Last Modified: 2012-05-08
Hi,

I have 4 DB queries in my Excel spreadsheet and for some reason,  when I run the report this month, the queries won't update...

I have not had this problem with them before...

When I run the same query in a new workbook, it works fine...

anyone any ideas why this is happening?? Or how I can force an update?

Cheers,

Eoin
Comment
Watch Question

Please give full details
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Do you get an error or does nothing happen?
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Also, how are you updating them - right-click and Refresh or using the Refresh All button?

Author

Commented:
I don't get an error. Nothing happens.

I use the right click refresh to do it, bit I've also written some code to do it in a macro.
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Are you 100% sure that the queries are what you think they are, and pointing at the correct db (just to eliminate the obvious!)

Author

Commented:
100% sure. I did the following to check it out:

?sheet1.querytables(1).connection and it shows the correct connection string!
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Did you check the commandtext too?

Author

Commented:
What's the command text?

It has updated every month for the past year connecting to the right db.
Grand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Yes, it is calling the correct query...

and yes, it is using parameters...
Rory ArchibaldGrand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
If you clear the parameters completely and refresh, does the data change?

Author

Commented:
For some reason, I came in this morning and it updates...

I didn't change anything..maybe, it was just having a bad day on Friday!

Thanks for the help Rory!

Author

Commented:
Problem sorted itself out...

Thanks for the help though Rory!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.