Most efficient way to extract from Access

[Dakker221]

Hi Everyone,

Now I know this is probably a well asked question but I'm looking for the most efficient way to extract data from Access. In my scenario I use Access as an input tool in a multi user environment with an Excel report which needs to pull the data in. Does anyone have any ideas of which method I should be using?

Thanks

M

 

[Fazza]

As often, it depends.

With the Excel file having to pull the data in (as opposed to if it was pushed from Access), one simple & efficient way might be just a query or two in Excel. Without VBA these can be set to refresh on file open, and/or every n minutes. The idea being that - with no VBA - one can open the Excel file, or if it is open it can refresh the query/queries every n minutes to update tables reflecting the latest data from the Access file, & the Excel worksheets populate as required from these refreshed tables for the reports. Maybe there are charts, pivot tables, pivot charts. Refreshing of pivot tables/charts is just one line of VBA.

Perhaps the reports are printed to pdf for email distribution? Maybe not. Either way, this distribution step might be more efficient with a bit of VBA. Google will find many examples.