Doing a Query - using Excel - changing source file

[Parker]

I have an Excel spreadsheet where I've done a Query from
an Access Database (Get external Data). I want to change
the name of the Access Database my data is being pulled
from. How do I do this?

 

[Dick Kusleika]

Parker

See here

http://www.dicks-clicks.com/excel/ExternalData5.htm#ChangeConn

There's no way to do that through the user interface, you have to use VBA.
If you don't know VBA, you can either delete the query and recreate it
pointing to the new location, or I can walk you through how to do it with
VBA. If the latter, follow these steps

Alt-F11 to open the VBE
Cntl+G to show the Immediate Window
In the Immediate Window type
?Sheet1.QueryTables(1).CommandText
?Sheet1.QueryTables(1).Connection

Change Sheet1 to the appropriate sheet codename and post back with what you
get. Also tell me the path to the new location and what version of Excel
you're using.

 

[Parker]

This is what was returned:

?Sheet6.QueryTables(1).CommandText
?sheet6.QueryTables(1).Connection
ODBC;DBQ=J:\XcomDatabase\TAKTPAVCMach.mdb;DefaultDir=J:\Xco
mDatabase;Driver={Microsoft Access Driver
(*.mdb)};DriverId=25;FIL=MS
Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeT
ransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;

 

[Parker]

?sheet6.QueryTables(1).CommandText
SELECT TAKT.Job, TAKT.Part, TAKT.ProdGroup, TAKT.ProdLn,
TAKT.PartDesc, TAKT.QtyDue, TAKT.JobSt, TAKT.ReqdDate,
TAKT.Planner, TAKT.Day, TAKT.Family, TAKT.Prgp,
TAKT.DropIn, TAKT.Operation, TAKT.SRQ, TAKT.LotQty,
TAKT.Reference, TAKT.WorkCenterDay, TAKT.RPCode,
TAKT.RPQty, TAKT.LeadTimeDays, TAKT.NoOfOpers,
TAKT.PartOpCombo
FROM `J:\XcomDatabase\TAKTPAVCMach`.TAKT TAKT
ORDER BY TAKT.Job, TAKT.ReqdDate, TAKT.PartOpCombo
?sheet6.QueryTables(1).Connection
ODBC;DBQ=J:\XcomDatabase\TAKTPAVCMach.mdb;DefaultDir=J:\Xco
mDatabase;Driver={Microsoft Access Driver
(*.mdb)};DriverId=25;FIL=MS
Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeT
ransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;

 

[Parker]

FROM `J:\XcomDatabase\TAKTPAVCMach`.TAKT TAKT
ORDER BY TAKT.Job, TAKT.ReqdDate, TAKT.PartOpCombo
?sheet6.QueryTables(1).connection
ODBC;DBQ=J:\XcomDatabase\TAKTPAVCMach.mdb;DefaultDir=J:\Xco
mDatabase;Driver={Microsoft Access Driver
(*.mdb)};DriverId=25;FIL=MS
Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeT
ransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;

 

[Dick Kusleika]

Parker

Are you going to want something to update all these workbooks at once? Or
would rather have something that the user can run when they encounter a
workbook that needs to be changed?

I also need to know what the new location is and what version of excel
you're using.

The DBQ part of this string holds the location, so that's what we'll be
changing.