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.

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" <(E-Mail Removed)> wrote in message
news:rcLHj.5658$(E-Mail Removed)...
>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.

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



Ron Coderre wrote:
> 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" <(E-Mail Removed)> wrote in message
> news:rcLHj.5658$(E-Mail Removed)...
>> 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.
>
>

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.