Changing External Data Location Link in MSQuery

[Jeff Harbin]

I have a fairly complex workbook that I used on a regular basis. The
first thing it does is get a data extraction from an ACCESS database
that is located on a different computer over the LAN using MSQuery.

Recently, my IT department swapped out the computer that stored the
ACCESS database with a different computer (new name, new IP address,
etc.). So now even though the database structure is the same it's
stored on a new machine.

How can I change the location that the link uses to retrieve the data.

 

[Ron Coderre]

Try the free Pivot Play PLUS add-in, located at
Debra Dalgleish's website:
http://www.contextures.com/xlPivotPlayPLUS01.html

It allows you to view/edit the Data Source and SQL code
for Pivot Tables and Query Tables that use external data sources.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Jeff Harbin]

Thanks. This certainly seems to do exactly what I need. I'll try it
first thing in the morning.

 

[Lori]

I find it easier to use the Import Data command from the Import External Data
submenu. This allows you to bypass MS Query, and define your own
connection/sql text, in the last step you can output to a table or pivot
table. Having set it up, you can just choose Edit Query from the same menu to
easily update the connection parameters.