Demonstrate how data can be cleaned up and shared across multiple users while allowing reports and visualization in well-known and common tools.
- Microsoft SQL Server (Express)
- Microsoft Access & The Access Runtime
- Microsoft Excel
- Add-on: Microsoft Power BI
Provide a data aggregation and cleanup tool with customized and shared Excel reports.
This has come up for a couple client projects.
Excel does a horrible job of allowing shared data, particularly across company networks. This is compounded by a company network with data across multiple locations – even countries. Using Excel to store data in Workbooks or Worksheets does not allow multiple users to import and correct data or make it available to other users efficiently.
A couple variations on a theme. Both start with a SQL Server or SQL Server Express database.
Solution 1: Excel as SQL Server / Database Client
In one solution, we used Excel as the client. Excel forms to read the SQL Server data and provided the complete user-interface for importing and cleaning data.
In this solution, Excel connects directly to the SQL Server using ADO or some other connection method. Excel can also connect directly to Microsoft Access to retrieve and update data.
As an example, I provide a downloadable file. It is a zip file with an Excel macro sheet and a Microsoft Access databases. I demonstrate reading data from the database and writing that data into a Worksheet. This same methodology can, of course, be easily modified to use a SQL Server database.
This example demonstrates an Excel ADO connection string to Microsoft Access and using the Range.CopyFromRecordset method to write data from either SQL or MS Access to a worksheet.
The benefit of this solution is that it only requires Excel as your client. The app can be written completely in Excel VBA with user forms.
The downside is that Excel, while powerful, does not have the same level of integration or interface tools as Microsoft Access (see Solution 2).
Solution 2: Microsoft Access as SQL Server / Database Client
In this solution, Microsoft Access was used as the client application. It’s rapid development interface and simple form creation made building the data management a bit simpler than using Excel natively.
However, it does require that users have MS Access (Microsoft Office Professional) or that we deploy the Microsoft Access Runtime. This isn’t that complicated or time-consuming but it does add some complexity in order to get the simpler/faster development environment.
Microsoft Access is then used to pull the data from SQL Server and update the Excel Workbooks and Reports.
Basic Solution Diagram
The graphic below shows a very simple diagram as to how such a solution might look. The SQL Server or SQL Server Express (FREE) can be installed on a workstation, though an actual server is nice – just for standardization. I also show a shared folder residing on the same server. This is used for Excel templates and other files that might be part of the solution.
What about Power BI?
I’ve been using both Microsoft’s Power BI and Tableau to give users clearer graphical analysis of their data. The upside is that with VBA – whether an Access or Excel client – you can prepare data for either tool. When it comes to distributing reports that tell a story, Power BI and Tableau add a LOT of functionality and graphical clarity to your reports.