Let external users review and edit data from your Access DB

by Philipp Stiefel, originally published December 27^th, 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.

Advantages

• No additional infrastructure required
• Can be implemented fairly easily (compare to the other approaches)

Disadvantages

• The plain text representation of the data can only be used for simple, flat structured data.
• It’s not particularly user friendly
• There is absolutely no data validation during data entry
• Very error prone due to the missing data validation and the risk of unintended modifications during email transport

Assessment

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.

Advantages

• You can to a certain extend design a user friendly UI for data entry, including some levels of data validation.
• The data format is suited much better for complex data and it is very unlikely that the data gets mangled during email transport.

Disadvantages

• The recipient needs to have the appropriate application (Excel, Word, or Access) to open and edit the document installed on his computer.
• Emailed documents, especially if they contain VBA code, might be blocked or removed by security software.
• Manually saving and emailing the document back to you by the external users is a potential source of error. Especially if the users are not very tech savvy.

Assessment

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.

Advantages

• No system requirements for the external users except an internet connection and a web browser
• There are very few limitations on the user interface you create. The UI can include all sorts of data validation and can present fairly complex data.

Disadvantages

• Unless you actually use an Access Web App, this cannot be implemented using Access.
• To create a whole web application from scratch requires substantial effort and it requires some knowledge about the used technology.
• You need the infrastructure to host the web application. This might particularly be a problem, if you companies data governance rules limit the options where to host the data.

Conclusion

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.

Share this article: