Link to an Access table from Excel
Try this example in Access, which runs a query whose resulting table is viewable in Excel.
Start by opening Access and creating a table with this data:
tale of two cities,12/1/2009,$5.01
tale of two cities,12/3/2009,$4.98
the count of monte cristo,12/5/2009,$1.98
the count of monte cristo,12/6/2009,$0.98
Create the following queries, using the SQL View to create both:
SELECT books.* INTO selectoutput FROM books WHERE book like "*two*";
Name the query just given as "storedquery." Name the following query as "killtable."
DROP TABLE selectoutput;
Enter the code
Open the Visual Basic IDE, select "Insert>Module" and paste this code in the new module's code window:
Public Sub doquery()
On Error GoTo CONTINUE_QUERY
CONTINUE_QUERY: DoExcelQuery ("storedquery")
Public Sub DoExcelQuery(qry As String)
DoCmd.SetWarnings False: CurrentDb.Execute qry: DoCmd.SetWarnings True
Open Excel and select "Data>From Access," then navigate through the Excel "Select Data Source" wizard, providing it the name of the books database and table you just created. Save the Excel file with any name, and close it.
Return to Access and modify the "storedquery" query, replacing "*two*" with "*count*" in the "WHERE" clause.
Re-enter the Access' Visual Basic IDE and place the cursor anywhere in the "doquery" function. Press F5 to run the query, then open the Excel file you'd previously saved. Select "Data>Refresh all," and notice that the table now displays the "count of monte cristo" rows instead of the original "tale of two cities" rows.
Read more about linking from Access to Excel here.
Access Developer Center