Update tables via email?

[Bill Rivers]

Hello and thanks in advance for help and patience. The not-for-profit corp. I work with does not have a network and cannot afford one in the near future. Our problem is this: We have 4 remote sites (all have high speed internet access) that need to exchange data with an access database application in our office. The data is relatively routine client data, adds and changes. My thinking is to have the front ends in the remote sites transfer updates from a temp table to a spreadsheet and automatically email that to the office. The office front end would then download the spreadsheet attached to the generic email and import it into a transaction table for further processing.
I have seen lots of code and advice for sending emails with attachments but none for the receiving end. I have written lots of VBA code for the office application; but, this is a new venture for me.
If my basic concept is good, I could use some help with the code to receive the emails and download the attached spreadsheet.
If the concept is wrong, I could use a better idea.
Thanks again. This looks like a great forum.

[stanl]

Sounds like an ideal situation for 'disconnected recordsets'. Data is queried from the Access Table(s) persisted as XML and sent as an attachment to each remote site. They make mods, send back the file, you re-connect to the Access DB and execute updatebatch() - it handles the record via LIFO. If you have Excel 2003, you can use the XML with a worksheet, probably involves more error-handling. .02 Stan

[mdmackillop]

Not something I've ever used, but would Google Spreadsheets help in this sharing/distribution of data.

[Bill Rivers]

I may have understated my inexperience with automating email. My search on "disconnected recordsets" did not produce the code I would need.
Until I can detect the received, email and download the attachment, I am not sure adding another mail server into my mental mix would be a good idea. What I am asking may be so basic that it is being overlooked by you masters: I want to automate the detection, opening, and attachment download from a predetermined email account (hotmail for example) with a known subject line and known body text. (The subject line and body text would be hardened by my code and never change.)

[stanl]

what is your email client? If it is Outlook you can set up rules to have received emails directed to a specific folder, then write necessary code to deal with attachments. Stan

see: http://www.fontstuff.com/outlook/oltut01.htm

[Bill Rivers]

Thanks Stan. I have tested code I downloaded that automates attaching a spreadsheet and emailing it using outlook. That code works great; although, it does not start up outlook.
I have no clue where to start with Outlook scripting. It does sound like that is the way to "close the loop". I am wiling to learn given a nudge in the right direction.

[Bill Rivers]

Thanks again Stan. I downloaded code from the link you included. I have not digested it all yet; but, it looks like what I need. It does have some user interface that I will try to eliminate.

[Bill Rivers]

Thanks again. After I put together a small application to test and develope, I will provide some feedback.

[Sjefke]

High speed internet access: does that mean permanent connections? If so, why not separate tables and distribute an application (front-end) to the 4 sites, that connect to the table-mdb (back-end)?

If not connected all the time: what about replication (look in the help and KB)? MS included it for scenarios like yours - I have seen it working for dozens of laptops connecting to the back-end-mdb and synchronize their data (over dial-up).

Email attachments is going to be a mess because of duplicate indexes and related tables (like clients and their orders\dossiers - how do you create unique client-IDs if sites are not connected - a solution like Site-Id+Client-Id? So S1-123 is some completely different client than S2-123? Now what if more sites deal with the same client? Then S1-123 could be the same client as S3-746...) And that from 5 sites? Plus redundant information. Plus a risk of errors and omissions - which record becomes leading?
And, if edited at HQ, is it returned to the site that supplied the data? Or to all sites?

Recipe for disaster and mental asylum after weeks of all-nighters of coding and cleaning records...

[Bill Rivers]

Thank you sjefke. Just to clarify: the application is already split into FE/BE; client ID has always been facility ID concatenated with resident number for that facility.
A client may indeed leave one facility and appear later at another. My new record entry process includes a module that does a name search. (We track the number of times we have served a particular client.)
Where I need help is in implementing (code) a multi-user system like you described, without a server, linking sites 70 miles apart, 2 with DSL (home office is one), one with cable modem, and 1 with dial-up.

[Sjefke]

Hi Bill,

Try to connect the sites with DSL\Cable to the back end - see if it is reliable and secure (firewall). If that works, get the dial up on DSL as well. Maybe you can find an older PC (PII or PIII) that you can set up as a 'file-server' (Windows installed only), with the BE mdb and a static IP address - all 4 sites and HQ link to it, so it runs 'independently'?

That way you only have to invest in 'hardware', instead of coding.

If that is no solution, I'm afraid you have to look into replication - Access has this built-in, there is not much coding needed from your side (as I remember), but obviously, it has to be tested.
Before you do so, I would recommend clean up of your data, to get rid of redundancy.

Hope this helps.