Access Automation Question / HELP request

[analysis_paralysis]

It might be that my brain is just broken these days (haven't been using access in at least a few years so to say that I'm rusty is an undestatement) but I need help setting something up.
Here is what I am trying to do:
I have an excel file that is saved over (using same name) multiple times each day with a cut and paste from a .csv file. I want to have that excel file as a linked file in access and each time the file is saved over for access to run a query against another table to determine matches against a particular field. I then would like the output of that query exported out to excel (to a set folder) and, ideally, an email sent out with the query results as an attachment.
How incredibly difficult would this be? I am in a bit of a crunch and need to figure out how to get it done. Any help would be greatly appreciated and you your status of hero would be completely assured! I would also write an epic poem in your honor if you would like (joke, but I would do lots of stuff in exchange for this help).
Thanks!
A_P

 

[apr pillai]

Several procedures are required to answer your Questions. Linking Excel File to MS-Access database is not a problem (File - ->Get External Data - ->Link Tables), you can link the Worksheet as a table to your database or directly reference the worksheet data in a Query. Refer the link

http://www.msaccesstips.com/2008/08/opening-excel-database-directly.shtml

if this method is preferable.

The data change in Excel File can be detected by checking the FileLen(PathName) of the attached file with the value saved during earlier upload event and determine whether new data introduced in the external file or not. This can be either programmed through a Timer-Interval procedure and check every one hour or a preferable fixed interval or you may click a button to run a VBA routine to check, after overwriting the excel file.

If new data found in the external file then your Query linked to the Excel Table in Access can be exported as Output into another Excel file with the following statement:

DoCmd.OutputTo acQuery, "myQuery", "MicrosoftExcelBiff8(*.xls)", "c:\My Documents\myQuery.xls", True, "", 0

Both event can take place at one click, checking the file change and exporting the result.

For the E-Mail part take a look at the following link to get a general idea as how to go about it:

http://www.msaccesstips.com/2007/11/automated-email-alerts.shtml

VBA Code is available there. You can customize them to your needs.

Regards

 

[analysis_paralysis]

I know about linking tables, that isn't my problem.
I will check on the other stuff to see if it meets my needs.
Thanks,
A_P