Pull date from Access source into Excel

[naiku]

I have an Access database (used for reporting) where the data comes from a different database (linked tables using OBDC). In the database I have a query that takes some of those tables, and ultimately gives me a report with dates on it. I don't think I can link Excel directly to the data source, as I need to combine various tables in order to get a job number with a date.

Currently, those dates are then populated manually in an Excel spreadsheet. Is there a way I can have Excel look at the query in Access, and pull the date from there? I have seen various methods described, but am not sure which is best to use. I have some VBA experience in Access, but none with Excel.

Ideally I want some way to perform a vlookup from Excel to the Access query (common fields would be a job number, that I would look up in the Access query and then populate the date).

I hope this makes sense, please let me know if more information is needed.

Thank you.

 

[Gary McMaster]

I don't think I can link Excel directly to the data source

If you can link to the source with ODBC in Access I believe you should also be able to do it from Excel.

You kind of lost me after that. It sounds like you're using Access as a "middle man" to get data from the source and then querying Access from Excel. Is that correct?

If you can't include multiple tables through the "Excel Query Wizard" you should be able to do it from within MS Query after the wizard returns the first table. In MS Query you can add other tables you need and link any keys you need.

Once you have the first table showing in Excel you should be able to "Edit Query" from somewhere under the Excel Data pulldown menu. Somewhere in one of the following dialog boxes you will be able to choose "Edit in MS Query" (or something close to that). Once you're in MS Query you can add tables and establish links.

Gary

 

[naiku]

If you can link to the source with ODBC in Access I believe you should also be able to do it from Excel.

You kind of lost me after that. It sounds like you're using Access as a "middle man" to get data from the source and then querying Access from Excel. Is that correct?

Kind of, Access is really just used for reporting, so has the linked tables and a bunch of queries.

If you can't include multiple tables through the "Excel Query Wizard" you should be able to do it from within MS Query after the wizard returns the first table. In MS Query you can add other tables you need and link any keys you need.

I have never heard of Excel Query Wizard or MS Query, but it sounds like if I can link to the same tables from within Excel (as I already have linked to Access) that I can simply build a query in Excel, and then use Vlookup to populate the dates that I need. Will give MS Query a look, thanks.

 

[Gary McMaster]

On the Excel main menu bar (before XL 2007)

Data > Get External Data > New Database Query ...

If the driver is installed your data source should be listed. Pick it and follow the wizard.

It's also available on XL 2007 & above. I just don't know the exact location on the ribbon

Gary

 

[naiku]

Great, Thank you!! that worked. Am I right in assuming that if I leave the query on a hidden worksheet, that it will update as the data in the original table updates?

 

[Gary McMaster]

You are correct. I hide them all the time.

You may also want to explore some of the options in the "Query Properties" (not sure of the name but it's somewhere in the Data > Get External Data ...) menu system. You can set the query to refresh automatically when the workbook opens, at specified time periods and some other options.

It can be tough to get them to refresh in a specific order if data depends on that. If you need a special order, I find it easiest to turn off "Background Query Refresh" and then record a macro while refreshing the individual queries manually. If you don't turn off "Background Query Refresh" they can still refresh asynchronously with the shorter ones finishing before the longer ones which may still be wrong.

In any case you can make them refresh in a specific order by making sure they are all set to refresh manually and running your recorded macro.

Gary