Does there exist a straightforward method of reaching inside of an Access
database file, from an Excel worksheet, to grab a live Access query? (If
there isn't, I wish that Microsoft would develop it!)

Right now, I can and do export a query from my Access database, which is
saved as a static Excel worksheet file. But, I have to repeat this exercise
every time I want to get the latest versions of the Access database's data.
So, I want a dynamic link instead.

I'm using Office 2003.

(My data will be in Access, because that gives me the flexibility of
ordering it in several different ways using compound keys based on about five
different fields [columns], some of which are non-numeric. My reporting will
be via Excel, because (1) that gives me the flexibility of changing the
details of the report layout "on the fly", and because (2) most of my
colleagues who will use the report interface are comfortable with Excel but
not with Access.)
--
Christopher

Hi Christopher,

You can use Data - Import External Data - New Database Query
to download the results of an Access query to Excel.

Or, you can use a Pivot Table with data linked to the Access query.

See my Pivot Table tutorial for steps on how to do this.
http://www.edferrero.com/ExcelTutori...9/Default.aspx

Ed Ferrero
www.edferrero.com

Ed,

Thank you for your two suggestions. Unfortunately, neither quite provides
the DYNAMIC Excel-into-Access link that I'm looking for, although the second
suggestion comes close, and I may implement it.

I was familiar with the concept of "Import External Data", but this
operation must be repeated every time one uses the worksheet, if one wants to
always have the current version of the Access data. In my test just now, I
also found that "Import External Data" keeps the Access database read-only
locked until the Excel file is closed (Office 2003 ).

I didn't know about using pivot tables to get at Access queries. This is
closer to being a dynamic link, as it only requires refreshing the pivot
table to get the current version of the Access data. And, it didn't lock up
the Access database in my test.

My ideal, which may not exist in Office, would be a true automatic live
link, which would require no user action to have the Excel worksheet "see"
the most current Access data.

Thanks again.
--
Christopher


"Ed Ferrero" wrote:

Hi Christopher,

You can use Data - Import External Data - New Database Query
to download the results of an Access query to Excel.

Or, you can use a Pivot Table with data linked to the Access query.

See my Pivot Table tutorial for steps on how to do this.
http://www.edferrero.com/ExcelTutori...9/Default.aspx

Ed Ferrero
www.edferrero.com

Hi Christopher,

I was familiar with the concept of "Import External Data", but this
operation must be repeated every time one uses the worksheet, if one wants
to
always have the current version of the Access data. In my test just now,
I
also found that "Import External Data" keeps the Access database read-only
locked until the Excel file is closed (Office 2003 ).

Right-click on the imported data, choose Data Range Properties...
Then check the 'Refresh data on file open' button.

Note that there is also a checkbox to refresh the data after a set time
interval. I tend not to use this as it is confusing for the user.

Does this come closer to what you want to do?

You can do the same thing with Pivot Tables. Look in Options.

Ed Ferrero
www.edferrero.com