Should You Integrate Microsoft Excel and Access
From Small Data To BIG Data – This Is Your Beginner Solution
So here is the scenario: Your medium sized business has been using Microsoft Excel to manage your firm’s finances via a series of linked Excel workbooks for years. The problem is this, over the years the amount of data stored in these workbooks has increased substantially, and now the workbooks are too bloated with historical data that may or may not be needed, you really are not sure. Just opening the files takes over 30-minutes. When you update the workbooks with new data, changes to formulas, etc., the process has become far too slow as the workbook takes forever to calculate and the current system of workbooks no longer meets your needs.
A little background: Your business hired an independent Excel developer over a decade ago to build the system of financial workbooks in Excel 2007. Shortly after the workbooks were built the Excel programmer retired and all changes to the workbooks were done internally by a handful of Excel users, none of which are Excel experts. Each did the best that they could, but when changes were made in recent years, errors started to appear. Making matters worse, over the years the amount of data in the workbook has increased substantially as your business has grown, and now the workbooks are too bloated with historical data that may or may not be needed, and the files have become far too slow and problematic. Your companies recent upgrade from Microsoft Excel 2007 to 2016 has resulted in numerous errors, in addition to the slow processing time, and you have no choice but to hire an outside firm to provide an immediate solution.
Your options: You have several feasible options, from an immediate short-term fix to sizeable long-term solutions. The discussion below covers several of those options, their pros and cons are included.
Here is a list of your best options, though not all inconclusive.
- Revise your current set of Excel workbooks just enough to get by, your low cost option, short-term solution
- Revise your current set of Excel workbooks both in workbook design, and add automation via VBA, Intermediate Excel solution
- Build a completely new system in Microsoft Excel, with VBA, Long-term solution, average cost
- Build an integrated and automated solution incorporating a Microsoft Excel front-end, with a Microsoft Access back-end, optimal solution, revising current set of Excel workbooks, VBA leveraged
- Build an integrated and automated solution incorporating a Microsoft Excel front-end, with a Microsoft SQL Server back-end
- Build an advanced system using Microsoft Power BI, high-end system, high cost
- Build a system 100% within Microsoft Access, high cost
- Build a high-end system in SQL Server
- Build a cloud based system with Microsoft Azure
Solution – Integrate and Automate Microsoft Excel and Access
This post will cover a solution where we build an integrated and automated application incorporating a Microsoft Excel front-end, with a Microsoft Access back-end, using much of the existing Excel based system, to minimize costs while optimizing the performance of the application.
So this solution takes the existing set of Excel workbooks:
- Revise current set of Microsoft Excel workbooks as needed
- Possibly create one or more new workbooks
- Add Microsoft Visual Basic for Applications as needed to automate and to integrate workbooks &/or Access
- Create Access back-end application
- Use Access to house historical data
- Use Access to perform calculations
- Set Excel VBA to retrieve needed Access data
- Use Excel based VBA to create and to distribute revised workbooks
The image below shows you what a Microsoft Access query in design mode looks like. With this solution the user will never need to open the Access database nor will they need to make changes to it.
Solution – This Excel and Access solution is one of the more optimal ones. I say one of and not the most optimal solution as this project uses the existing Excel workbooks and much as possible, making revisions as needed, adding VBA code here and there, in order to keep costs down. Building a new, integrated and automated solution would be the optimal use of the Microsoft Excel and Access applications, but that could easily cost 75% more than this solution, based on the number of Excel workbooks, the number of worksheets in each workbook, and the number of cells with formulas.
How this solution works is very simple to understand. Basically you have moved much of the historical data out of one or more of your Excel workbooks, and you have now housed it in a newly created Microsoft Access database. There are several Access tables holding the data, and there are a good number of Access queries that contain many of the formulas that were originally in the numerous Excel workbooks. You are now having Access do the intensive data processing, using its calculation engine which is much stronger than the one in Excel.
The Excel workbooks will now have much less historical data in them as those data ranges now populate the Access tables. As such the Excel files are smaller in size, now less than 10mb each, versus the 75 to 100+ MB before. The files open in seconds now versus up to 30-minutes before. When the files run the calculations now complete in less than a minute versus 30- minutes or more in the old system. So in this system Excel is more of a reporting and analysis tool and less of a big data calculation engine.
To start the process you update a few of the input fields on the primary input sheet, and then you click the run button. Clicking that button will activate the VBA that sends Excel data to Access, that then runs the series of Access queries, and the macro will pull the required data back into Excel data tables (on hidden worksheets). Simple formulas such as VLookups and SumIfs will populate the cells in the worksheets. In the old system these same cells had very complex formulas which were a large drag on system resources. Once the Excel macro completes the data extraction, the code that distributes the files will run. Once this has completed the file will save and close. So the process that used to take you several days to run manually now runs in minutes with very little human intervention required.
Cons of solution – The cons are few, they are mostly centered around taking the set of Excel workbooks already in use, and revising them. Had you created the application from scratch, you could have a better design and layout of the workbooks, their worksheets, the number of cells with complex formulas, and perhaps even fewer workbooks in the solution. Others may see the complexity of integrating and automating the two applications as a con, but it is the complexity that creates the ease of use. Sure, there is more VBA code in this solution than in the one it replaces, and your internal staff may or may not know how to write or edit vba code, but that is why you hire an expert Excel consultant.
Pros of solution – The cost of the work is actually a very good value with a high ROI. Yes, doing less work would cost less money, but this new system will free-up your staff to do more meaningful work. So instead of spending several days each week manipulating data and workbooks, your staff can do thoughtful analysis that will lead to improved operations.
Summary – Should You Integrate Microsoft Excel and Access? Yes, under this scenario, integrating and automating Microsoft Excel as a front-end to a Microsoft Access back-end is a solid solution with a strong ROI.
If you want to checkout a detailed source on this subject, click here to visit how to “Send Excel Data to Access” on Debra’s Contextures.com website out of Canada. It is our favorite source on the web.
df
We understand that this is a complex solution for those that are not themselves Microsoft Excel consultants or programmers. But no worry, our international team of Microsoft Excel, Access, Office, SQL Server, and Power BI developers can explain the details as well as the pros and cons of this solution for you. And if so desired, we can build a custom solution like this for you in a few weeks.
Call us now and get started today 877-392-3539
Our Senior Microsoft Access, Word, Outlook, Excel Developer Helen Feddema wrote a really good book on this subject, Integrating Excel with Access.
Buy Helen’s Access Archon Working With Excel
