Jun 9 2017

Should You Integrate Microsoft Excel With A Microsoft Database

This is a BIG topic on BIG Data, but these are Excel ‘hints‘. The challenge here is saying a little, pointing you to a few in-depth resources, to possibly spark an interest or even to pinpoint a possible solution. So here in this post we will point to three extended posts we did on this topic, in addition to one link to our favorite Access resource and one to our favorite Excel resource, both run by Microsoft MVP’s.

 

If you have used Microsoft Excel with BIG Data it is likely that you have encountered performance issues, and you have possibly experienced the worst problem, an Excel workbook that does not respond, it just sits there, dead in the water, looking at you. So imagine this, it is Friday afternoon on a three day holiday weekend. You have big plans for the weekend and at 2pm you need to grab your boat, a few friends, and you need to hit the highway to get ahead of the holiday traffic. Your CFO gave you permission to leave the office at 2pm, but in return you have promised your CFO that you would publish the monthly reports before you leave the office.

 

Microsoft Excel and Access integration

 

In this scenario, when you click the “Publish Reports” button in your Microsoft Excel workbook you watch in horror to see MS Excel freeze. You shut down Excel and you try several more times, but nothing, you even restart the computer, you try it on your buddy’s PC, but no matter what you do your Microsoft Excel solution has become unable to process what data scientists call BIG Data, the massive tables you store in this workbook. You have so much data that it has become unresponsive.

 

 Data Scientists

 

In this case you had to cancel the three-day weekend, and instead of drinking beers with friends as you relax in your boat you find yourself alone in the office, spending the weekend trying anything and everything you can to get your workbook to work. Everything you try points to the same solution, you need to integrate Microsoft Excel with Microsoft Access. Said another way, you need to use Excel as a front-end to an Access back-end.

 

Is it worth knowing how to do this, and to actually be very proficient in doing so? This is something we have been doing for our clients since 1994. This is one of the most common solutions we have been providing our clients for 23 years and counting. So yes, knowing how to do this at the advanced level can make you a good amount of money. As such we recommend that you give it a shot. Our second most common solution is integrating Microsoft Excel with a SQL Server back-end, something our competitors are just now starting to do, but we are getting ahead of ourselves, that is our second post in the series.

 

How common is this problem, having too much data in your Excel files that the performance suffers and the file takes 30 or more minutes to process, or worse, the file is 100% unresponsive and as such you are unable to complete your work? It is extremely common; BIG Data is nothing new, HUGE Data is now beginning to appear, we have several firms each week hire us to resolve their BIG Data performance issues. The client that wrote the testimonial below got lucky, we were able to provide a solution with just a handful of hours. For this client there was no need to build a new system, no need to spend all of that time and money. The current application works when the data-set is small, so all we needed to do was to move the data into an Access back-end, and use Excel based VBA to move the data between the two files, placing the results where they needed to be.

 

putman-excel-and-access-review

 

While we are focusing on an Excel front-end with an Access back-end for this post, it is important to note that Microsoft has not just one database, but three or four (Four if you count Power BI). So when it comes to BIG Data performance issues, select the Microsoft database that best houses and manipulates the size of your data. For example if your database has 3 gigs of data, Microsoft Access will be ill suited for your solution as Microsoft Access has a 2 gig limit. SQL Server comes in several versions, each with its own size limits.

 

Click here to compare the Microsoft Databases to each other and to Oracle.

  • Access
  • SQL Server
  • Azure
  • Power BI

 

Each of the Microsoft databases can and be integrated with all of the applications in the Microsoft Office Suite. This allows you to use VBA to create a fully automated solution. You can use any of the Microsoft databases as the back-end to an Excel front-end. For this post we are selecting Microsoft Access as the back-end database.

 

In this scenario you move massive amounts of data that was originally stored in Microsoft Excel to Microsoft Access. The data moved out of Excel will populate one or more large tables in Microsoft Access (Less than the 2 gig limit). Microsoft Excel VBA (Visual Basic for Applications – Marcos to some) will then move data between the two applications as needed. In this solution Access will not only store the data but Access will also run one or more queries on the data, possibly populating or updating additional Access tables, and Access will return filtered data sets back to Excel. This minimizes the amount of data that is stored in Excel, thus dramatically reducing the size of the Excel file, while optimizing performance.

 

That is it, it is that simple. Integrating Microsoft Excel with Microsoft Access will enhance performance and it will get you past those BIG Data blues.

 
 
 

Below you will find two links that will take you to our extended posts on the series. If you are interesting in taking your Excel applications to the next level, harnessing BIG Data, then splitting your application into an Excel front-end, to a Microsoft database back-end, read each of these posts to see what might work best for you and your organization.

 

Microsoft Excel MVP, Debra at Contextures.com did a nice post on this subject. click here to read how to “Send Excel Data to Access”. Her site is our favorite Excel resource on the web.

 
 

You can also “split” an Access database into two files:

According to Microsoft, Splitting a database can help improve its performance and reduce the chance of database file corruption.

When you split a database, you reorganize it into two files — a back-end database that contains the data tables, and a front-end database that contains all the other database objects such as queries, forms, and reports. Each user interacts with the data by using a local copy of the front-end database.

 
 
 

This site is about providing an Excel hint, not proving a complex solution. For that you can either take the time to learn how to do so, and build it yourself, or you can search the web for a firm to hire. At Excel hints we do both. If you would like to discuss your needs and if you would like to receive a quote on what the work would cost, please contact us to discuss your needs in detail..

 


Click Here To Contact Us Now

 

Call us now and get started today 877-392-3539

 

Random Posts

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives