Importing Data into Access

A lot of my access applications involve loading data from an external source, executing some transformation and applying business rules. When I first started using Access I would have written an import specification for each file, and then added queries or code to load the data and apply business rules.

This approach involves a lot of code maintenance as file formats change or business rules were updated making it very difficult to pass the application to the business users to maintain.

To resolve this issue, I created a generic import routine to allow the users to specify what files to expect and what rules to apply once the file was imported. The import routine allows for 3 options: Excel, CSV files up to 255 columns and CSV files with greater than 255 columns which I called G255. 

In access the maximum columns in a table is 255, I have written the “G255” to read the text file and extract the columns the user requires. By adding a user interface form for this table, the business users are then able to adjust the file import add new files and change the business rules without having to change the vba code behind the application.

The transformations and business rules are applied as SQL commands linked to the import routine or can be called as a separate function. The rules are defined and documented by the user and the sequence of execution can be altered as required.

For documentation I added a memo field for the user to document the reasons for the rule or transformation and added a report which details all the steps in execution order.

The sample application, which you can download here, is written in O365. If you are using an older version, you will need to adjust the code to work with your version. I have added a simple ribbon menu to the application, the application is designed to hide the navigation option. You can press F11 to display the navigation bar or open the application is design mode by holding the shift key while opening the applications.

In the sample application, from the ribbon select Process Imports. This will open the process imports screen. The screen has several buttons including: Import Data, Apply Rules, Export Data. The code behind the Import Data has 3 lines, Set the status on the screen to change the message code to run , when the process is complete this is returned to green, Call the Import routine Call ImportGeneric(“Folder Location”,”Process Name”).

In this sample the Process Name is “Import Invoices”, this will run the import routine for any section tagged to that name. This imports the data from different sources into a normalised table where the business rules can be executed.