Data: PHP form => email => Access 2007 database

Question

I have a form which is submitted to a PHP page. The data from the form needs to be added to an Access database, however the website is hosted and the Access database is on a network drive. So for now, the form just emails the data to someone who enters it by hand. We're trying to improve this process.

So there is no way for the PHP page to directly access the .mdb file.

My idea is to have the PHP page somehow create a block of encoded data, probably with a checksum hidden in it, and email it to our data entry person. This email would look something like this:

===== BEGIN FORM DATA =====
oipcxunrhunt5klejswtnlerj,m,nxupogxjidoh
jpos89760aq2y984hu5nw3jnm4tiopanj5kjny;s
ljknm;l5nkl;ksnm;linjtg89jx9070g89xc6dgh
890unysl;ne.lmsjo8r7g9n0cf9ogumhlkod9ysd
8hj7pc9pg8uopcfmity68od7r9pyh9pm9pfghpju
ypxdekopslp4mpsmkl;4m;slkmk;smjilenmuops
inuobnuiou5io5nuosiun5oin5u4oinu5oiwnu3o
in2oinounion0uonio76n598n109yb9705btbsnb
===== END FORM DATA =====

UUencoding comes to mind, though really I guess I just need some sort of encryption algorithm that would be easy to implement in both PHP and Access (VBScript).

The last part, of course, is on the Access input form there would be a button. This button would pop up a text box, the data entry person would just paste the above block of text into the box and click ok, and some macro code would handle unpacking the data and fill in the forms. Then the data entry person would just need to check that everything's fine and add the record to the database.

Does this process sound like the most efficient one? What algorithm would you recommend to create that block of text and easily unpack it into an Access form?

By the way, I'm very proficient at PHP and not so much at Access/VBScript. Please be verbose about the Access side of things. Thanks!

Answer 1

Maybe you can conquer the problem in a different way? Have the PHP dump its data to a MySQL database and then have the Access side pull new data from the database when it is loaded.

Answer 2

Thinking along the lines of what @theMike suggested, I would suggest entirely bypassing email.

Keep in mind that Access can directly interface with the MS XMLHTTP library so it can talk directly to a web page. Thus, you'd set up PHP scripts that Access can load that return the content that Access needs to process. One of my clients uploads inventory to their website from their Access database using this functionality. It can work in the opposite direction, too (i.e., downloading).

Whether or not you want to store the results from the form input in a database or just in text files depends on whether you have a database available to you. If you use a database, it's pretty easy. If you use text files, I'd suggest one per form result, rather than appending each form result as a row in a single text file, since it's a lot easier to process individual files than to work with the text file's multiple rows. However, if you already have a database abstraction layer sitting beneath your PHP pages, you might very well be able to use that to work with the text files via SQL (if your abstraction layer supports that).