Excel File link to Access DB when location of DB has changed

[Chelsea_indoors]

Hello,

I have an xls file (around 10 tabs) with pivot tables that use an MS Access table as data base.

Problem is:
I have to frequently change the link to the Access DB as the DB is monthly put onto a different location (reporting purpose).
Now, I am aware about how to change the link to the DB (via MS Query), but - as the xls file has around 10 tabs all linked to the specific DB (all to the same single table!) - I do not want to relink each xls tab one by one but rather all together.
Also, when relinking one by one, the size of the xls file is blown up as it seems to create each link individually creating individual data cubes in the context.

Anyone knows how to switch the MS Query link for the full file all together?

Much appreciated.

Regards
C_I

 

[Fazza]

Hi, Chelsea

Basically, easiest might be to loop through in VBA and set, for each pivot table, the 'connection' property. Make the connection to the target mdb file.

That might be all that is required. However, if the SQL includes the mdb file path within it, this either needs updating or better would be to remove it entirely.

I normally work with Excel 2003. Suggest you try this code simply to see the current connections. Untested.

sub untested()
dim wks as worksheet
dim pt as pivottable
 
for each wks in activeworkbook.worksheets
for each pt in wks.pivottables
debug.print wks.name & " - " & pt.name & " - " & pt.connection
debug.print vbtab & "=> " & pt.SQL & vbcr
next pt
next wks
set pt=nothing
set wks=nothing
end sub

Do you see the mdb file information in the SQL? If so, it would be best to remove it.

Can you do that?

Once that is done, we can sort out the VBA to do a slight manipulation of the connection property (it is only text) to accommodate changing to differnet mdb files.

I won't be on line again this evening: be back tomorrow. If I can help then, please post a sample of a connection string from the debug.print

cheers, F

 

[Chelsea_indoors]

Hi, Fazza

Thanks for the reply/post.

My VBA/SQL knowledge is a bit on the rusty side (if not worse...).

Is there probably a more manual way of doing this - e.g. directly doing it within MS Query?

Thanks and regards
C_I

 

[Fazza]

I don't fully understand, sorry, Chelsea. You know how to do this in MS Query already and are seeking another approach using MS Query, without using VBA?

Personally I would use VBA. I'd avoid MS Query altogether if possible.

Perhaps someone else can help. Regards, F