Excel file linked to access database

**simon pitt** · 07-10-2009, 07:49 AM

Hi there

I have created a 'price list' database in ACCESS. Then in EXCEL I created a pivot table which retrieves data from one of the database queries (the query was saved as a .dqy file).

I emailed the file containing the pivot table to a colleague who is on the same server. He saved the excel file on he desktop & renamed it.

When I update the databse file on a shared public drive on the server, he is able to 'refresh' his desktop file successfully !!

How does this work??? How does it keep the link???

Many thanks - a response is greatly appreciated
Si

**Paul** · 07-10-2009, 05:13 PM

Why would the name of the Excel file make any difference to the Access query or pivot table source data/path?

**przemke** · 07-13-2009, 04:20 AM

I also think the file name shouldn't matter. It would when when you would change the name of the source file (access database)
And it works because the link in the excel pivot table, is to the location where you put the database file on shared public drive, right ? (you were not clear with the steps how you did it)

**simon pitt** · 07-15-2009, 08:21 AM

Hi there

thanks to you both for the replies.

yes you're right the name of the excel file won't make any difference.

Let me rephrase things:

i created an access database (product price list) on a shared drive on our server. I created a query in the same access database which gives me product prices. in excel again on the shared public drive (same folder as the access databse) i created a pivot table which uses the access query which was saved as a .dqy file (ODBC query file).

I copied the same excel file onto my desktop (not a short cut). Every time I make changes to the access databse (add new product prices) on the shared public drive - these changes feed through to my desktop excel file when refreshed.

How does this work? Is it because everytime the desktop file is refreshed, the ODBC query file retrieves the changes from the public drive database (as this is where the database sits??) And will this only work for files on the same server??? If I emailed the file to someone externally, surely it wouldn't retrieve the updates when refreshed???

thanks again for your help
Simon

**przemke** · 07-15-2009, 08:41 AM

Yes so it looks like you answered yourself: the ODBC query knows the exact location of the file. And it won't work if not on the same network then.

So it's smarter then excel which does not keep the location. I noticed if you have the linked files in the same folder and move/copy them together it will work then.