Editing Connection Strings and refreshing Pivot Tables

[fadydaddy]

Hello All,

Since this is my first post, a little introduction is in order. My name is Fady and I work in the media industry in the Middle East. Its a privilege to be a member of a good bunch of excel brainiacs *however, I'm quickly finding out I may not be one . YET! )

Anyways, here is my first post/problem. I would imagine its pretty simple but I am not able to crack this nut for some reason.

I have a MS Access .accdb file which I take around with me to different desktops.
I have one excel file .xlsm file which I use to create dashboards.

My problem is whenever I take these two files to another PC, I have to locate the same database file for each pivot table (of which I have 94 in the excel, btw).

So I created the following:

Please Login or Register  to view this content.

Then I try the following:

Please Login or Register  to view this content.

But the problem is that while refreshing, excel gives me a pop-up window saying "C:\blah.accdb" is not accessible. would you like to connect to "K:\blah.accdb" instead ? YES NO CANCEL". earlier the file was C:\blah.accdb but in the first function I had the connection string changed and yet when I refresh it asks me for confirmation. What I want it to do instead is just simply accept the new string and refresh it.

I know I am missing some sort of programming flow without which I just simply cannot access another DB. But what is it? Or is there something else? You help is appreciated! Thanks.

[MarvinP]

Hi,

Without understanding your whole question, I'm looking at the second peice of code above. This looks like code behind a worksheet, because it is Private. I wonder if a Private function can effect things outside itself. See this page on Scope to see if you get my implication and if it helps solve your problem.
http://www.cpearson.com/Excel/Scope.aspx
I was also thinking earlier in the question if using a global variable might make your code work on different machines but I'd need to dig further.

[fadydaddy]

Hi MarvinP

Thankyou for your reply.
The scope isn't the problem because I am not getting any coding errors.

I guess I should simply my question a bit.
I have two databases which have the same tables and queries
I have one excel file that has pivot tables that run a particular query from any of the two databases.
I just want the ability to change the database it is pointing to using a macro (which this code is doing) but without excel suggesting to me that I am currently pointing to a database which it cannot locate, and suggesting the new database (which is the one I have already told it to point towards) as a possible connection for each pivot table in my workbook *which are 94). So I am getting 94 message boxes from excel suggesting me to try a different database.

[fadydaddy]

What do I have to do in order to change the MS ACCESS database a EXCEL pivot table is pointing to?

[MarvinP]

I don't know the code in Access to link an XL spreadsheet.

It would seem to me you'd have to unlink the XL sheet and relink it again to even get close to what you are looking for.

[fadydaddy]

I still get the impression I have not made my problem clear enough.

I am attaching my work here.

In order to recreate the problem just open the testcase excel file, and refresh the pivot table. it will tell you that it cannot find the file....then goto my sub fileselector and and try to give me some code which will automatically update the file i am selecting from the change db file location button

[rkey]

Might be to quick and dirty though if i run your test file with this I don't have the prompt.

Please Login or Register  to view this content.

[fadydaddy]

Well since the data in both the tables in each database is the same you are not noticing the difference...change some of the values in any one db and u will see that it is not actually refreshing the data after all.

[JosephP]

you didn't refresh the cache. try this

Please Login or Register  to view this content.

[fadydaddy]

Hi JP,

Too good bro. Was wrestling with this for last 24 hours without sleep. Went to bed disappointed. Woke up and saw your post. Awesome work. Magical for me lol. thanks!

[JosephP]

you're welcome :-)

[fadydaddy]

Hi JP,

Okay I tried it and it worked...BUT

I must explain you the actual work scenario.

My database is kept on my office pc. And I have brought the excel file home and created an identical database and am working from here at home.

The test case files that i had prepared i had prepared them all at home.

now when i try your solution on my actual excel file it gives me this dialog box

\1

and since i have 94 pivot tables, imagine my frustration.

If anyone can shed some light on this, much appreciated!

[JosephP]

can you post an actual file (don't need all 94 pivot tables)?

[fadydaddy]

Hi JP,

I just added one line (bold) to you following code which solved everything. Thank you very much.

Please Login or Register  to view this content.

[JosephP]

Cool-thanks for letting me know :-)

[fadydaddy]

Hi JP,

Just arrived at work and found out it still does not work.

[JosephP]

what's the data source for the pivot? a table, query or SQL string?

[fadydaddy]

Its a query.

[JosephP]

what does the commandtext property of the pivotcache return? any reference to the source file in it?

[fadydaddy]

All of them return the source file I want it to connect to but still it gives that popup.

[JosephP]

what exactly does the commandtext return for say the first pivot table?

[fadydaddy]

The name of the query I want to run; e.g. FSTEP_RETURN_BULK_RECORDS

[JosephP]

can you post a workbook with one pivot table exhibiting the problem?