Not looking for a spoon-fed, "here's the code to do it" answer, just a pointer in the right direction...
I have a customer running a SQL Server 7 database with an Access 2000 front-end.
He's very happy with it but I like to be pro-active and am looking at ways to improve it.
One area that I think could / should be improved is a regular import of a text file containing fuel sales information which arrives as an email attachment every Friday morning (there are actual about 50 such files from various locations but for simplicity's sake let's say there's just the one).
Currently the user reads in his email using MS Exchange, saves the attachment to his LAN and then uses my Access front-end to instigate an import of the aforementioned file.
I would like to automate this process.
Would it be possible for SQL Server (running SQL Mail) to respond to the arrival of such an email and carry out the necessary processing (extracting the attachment, formating its content + storing the data in the appropriate table) without manual intervention?
It crossed my mind that the way to go may be to have SQL Server fire a VB routine which does all the actual work but as this is my first time dealing with email I am unsure of the best way to proceed.
If anyone has any ideas or experience of the above I'd be very grateful for any input.
Mark
I have a customer running a SQL Server 7 database with an Access 2000 front-end.
He's very happy with it but I like to be pro-active and am looking at ways to improve it.
One area that I think could / should be improved is a regular import of a text file containing fuel sales information which arrives as an email attachment every Friday morning (there are actual about 50 such files from various locations but for simplicity's sake let's say there's just the one).
Currently the user reads in his email using MS Exchange, saves the attachment to his LAN and then uses my Access front-end to instigate an import of the aforementioned file.
I would like to automate this process.
Would it be possible for SQL Server (running SQL Mail) to respond to the arrival of such an email and carry out the necessary processing (extracting the attachment, formating its content + storing the data in the appropriate table) without manual intervention?
It crossed my mind that the way to go may be to have SQL Server fire a VB routine which does all the actual work but as this is my first time dealing with email I am unsure of the best way to proceed.
If anyone has any ideas or experience of the above I'd be very grateful for any input.
Mark
