Last Update: 2017 - 07 - 09
Let external users review and edit data from your Access DB
by Philipp Stiefel, originally published December 27th, 2016
This article is about how to let external users review and edit data from your Microsoft Access database. The same applies to the entry of new data as well. An external user in this context is someone who is has absolutely no access to the database application itself and who should or cannot not have it either due to technical limitations or for security reasons.
I just write down my thoughts about the theory, but I’m not going to show implementation details of any of the approaches here.
I can think of three different approaches to solve this requirement.
1) By Email Text
You create an email in your Access application and send it to the external user. This email contains the data in a clear structure like line wise name-value-pairs (Name=Value).
The recipient of the email can reply to this email, quoting the original text. In his reply he changes the values of the data from the original email as he sees fit. He must pay attention not to mess up the name-value-structure of the data.
Your application reads all the reply-emails that are returned by the external users. The application then updates the data with the changes from the incoming emails. This is done by using string functions to parse the email text and extract the data and then updating the tables in the database.
Your code needs to handle slight changes to the format of the data during the email transport. It is particularly likely that line breaks are added or removed. So you should choose your Names of the name-value-pairs very well or enclose them in delimiters. That should enable your code to still recognize then if they became part of a longer line instead of being the beginning of a new line.
A slightly advanced data structure would be to put your data into an HTML table. However, this requires the external recipient to use HTML for his reply as well and again he needs to take care not to mess up the structure of the data.
I would not use this approach.
Due to the high potential for errors this approach is suitable for very simple data only and it requires manual checking of the final results.
For small amounts of data I would rather let the external user send them in an unstructured email and then copy them manually into the application. If this is not feasible due to the volume of the data I would rather use approach 2 or 3.
2) Sending a document for data entry
You email a document file that contains a form for data entry and that can store the data in a structure storage format as well.
This can be a simple document, like an Excel Workbook with protected areas or a Word-Form with prepared data entry fields. The recipient edits or enters the data, saves the document and emails that document back to you (your application).
If you need to display complex, structured data you might even send a little Access database for the recipient to enter the data into. This application could contain the functionality to mail the data back to you on the push of a button. - However, this requires an automatable email client on the computer of the external user.
Once again, your main application processes the incoming reply-email with the attached documents containing the data. Excel data can be imported very easily and data from a Word form can be exported as CSV and then easily imported as well.
This approach is particularly suited for company internal scenarios where you know the technical infrastructure of the recipient, but you cannot give the external user direct access to your main application.
2b) Using the Collect Data Feature of Microsoft Access
In Access 2007 and Access 2010 there was the Collect Data feature. This feature was pretty similar to the approaches 1 and 2 described above.
The data will be either emailed to the recipient as an HTML-Email, analogous to approach 1, or it will be put into a Microsoft Info Path Form and that will be emailed analogous to approach 2. The advantages and disadvantages of these approaches, as detailed above, apply here as well.
A massive advantage of the collect data feature is the fact that there is a wizard available in Access that can create the email to the external user and that can extract and process the data from the reply-emails and store the data in your application. This dramatically reduces the effort required to implement this solution.
You need to be aware of the fact that this feature was removed again in more recent versions of Microsoft Access (beginning with Access 2013).
I recently noticed that the collect data feature seems to require not only Access 2007 or 2010 but Outlook 2007 or 2010 as well. When I tried to use this on my current computer, the wizard to set up the data collection complained about requiring “Outlook 2007 or newer” and refused to work. - I currently have got Outlook 2013 installed.
3) Building your own web application
You create your own web application with ASP.net or PHP, or maybe an Access-Web-App. Then you upload this application to a server that is directly available for external connections from your external users via the internet.
You still send an email to the external users, but it only contains a link to the web application where the data can be reviewed and edited. You application can read the updated data directly from the web application or it can be transferred in a format suitable for the relevant data.
This sounds very simple from the usability point of view. And it is. This is the best solution to provide a good user experience for an unrestricted circle of recipients without requiring any infrastructure on the recipient’s side, except for a web browser of course.
The main drawback is the substantial effort required to create the web application. You should not underestimate that.
In my opinion only using a dedicated web application is a viable long-term solution for this while issue. Considering the limitations and the nevertheless significant effort required to implement any of the other more simple options, they are no real alternatives to me.
The collect data feature of Access 2007 and 2010 deserves a special places, though. As the effort to implement a solution based on this feature is much lower than with any of the other options, it can be sensible to just go with it and tolerate its limitations.
© 1999 - 2017 by Philipp Stiefel