Different ways to extract data from Access to Excel

[xlcj]

I have been using Access 2003 and 2007 to read and do simple data (not
design) repairs to old Jet databases that underlie a legacy proprietary data
collection program my company uses. My latest challenge is to automate the
extraction of certain data from the tables and put it in an Excel
spreadsheet. Each time the extraction is done it would involve finding a
certain table, searching for a record that meets the new criteria in a
certain field, then moving over to another certain field and extracting the
text, number, or date therein.

I would really appreciate if anyone could point out which makes more sense:
(1) to work from Excel and write VBA code to open Access, search the
database tables, find the needed info, and copy it back to a spreadsheet,
(2) use Access VBA to do the same, or (3) use an Access query to find the
information and then export it to Excel? Better to pull or push? Like a lot
of things, I suspect there are a lot of ways to accomplish this task, but
not being an Access expert, maybe there is a good reason you know it would
be better to do it one way or the other.
Thanks a lot,
Carl

 

[Arvin Meyer [MVP]]

Use a query to find the record, then use either TansferSpreadsheet or
OutputTo to write the data over to Excel. Here's a sample line of code to
use TransferSpreadsheet:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryXTab_ItemCost", "S:\Customers\ItemCost.xls", True

 

[xlcj]

Arvin,
Thanks for your help.
Carl

Use a query to find the record, then use either TansferSpreadsheet or
OutputTo to write the data over to Excel. Here's a sample line of code to
use TransferSpreadsheet:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryXTab_ItemCost", "S:\Customers\ItemCost.xls", True
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com