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

 

May 12 2017

Should You Integrate Microsoft Excel and Access

Read More

May 6 2017

Microsoft Excel Certifications

Here is an Excel hint for you, there are many Microsoft certifications available, and each can help to show the world that you are a Microsoft expert. This Excel Hint has a few links that will help you to explore this subject.

 

How many Microsoft certifications are you aware of?

 

How many Microsoft certifications do you have?

 

For example, Jacob Hilderbrand, one of the owners of this site is a long-time Microsoft Excel MVP. The Microsoft MVP certification is one of the top two Microsoft certifications available. In any given year there are only about 130 Microsoft Excel MVP’s in the world. Yes, that is correct, around 130 in the world. Being one can really set you apart. If you are not one already, don’t fuss, you can become one. Jacob’s Microsoft Excel MVP Certification page on the Microsoft website.

 

Below are a series of links that will help you to learn more about Microsoft certifications.

 

What does it take to be an MVP?

 

Nominate an Excel expert you know for the Microsoft Excel MVP award, or nominate yourself.

 

Visit the Microsoft MVP page Microsoft Most Valuable Professional (MVP)

 

https://www.microsoft.com/en-us/learning/office-certification.aspx


Here is the list of Microsoft Certification Exams.

 

microsoft-certifications

 

So there you have it, there are many Microsoft certifications that may benefit you by setting you apart from the millions of Excel users. Take your time to explore the links that take you to many useful pages on the Microsoft website.

 
 

Do you want to know why Microsoft Certification really matters for those of you that are looking to hire an Excel consultant?

 

Apr 12 2017

How Can Jacob Hilderbrand Help You?

How Can Jacob Hilderbrand Help You?

Who exactly is Jacob Hilderbrand, what does he do, and how can he help you with your Microsoft Excel needs?

Jacob

  • Jacob Hilderbrand is one of the owners of this site.
  • Jacob is a Microsoft Excel expert.
  • Jacob is an Excel VBA guru.
  • Jacob usually works behind the scenes on this site.
  • Jacob is also one of the owners of Vbaexpress.com as well as the lead Microsoft Excel consultant there.
  • Jacob manages the forums at Vbaexpress.com
  • Jacob is the Lead Microsoft Excel Consultant at ExcelConsultant.net. Here is Jacob’s profile.
  • Jacob has been a Microsoft Excel MVP since 2012.
  • Jacob can integrate and automate Microsoft Excel, Access, Word, SQL Server and VB.Net.

If you need help with your Microsoft applications and you want to hire an expert, Jacob can help you.  You can use the contact form on this site to reach him.

If you want to get answers to all of your question about how to do things in Microsoft Excel, Access, etc., use the free forums at Vbaexpress.com

How can you reach Jacob? E-Mail him at Jacob@VBAExpress.com

His LinkedIn profile is https://www.linkedin.com/in/jakehilderbrand/

That is Jacob Hilderbrand in a nutshell, that is who he is, what he does, and how he can help you.

Mar 26 2017

Our Microsoft Excel, Access and Office Training Services

If you or your organization would like to take your Microsoft Excel skills to the next level, give us a call. See why our unique approach to Microsoft Excel, Access and Office Training works so well for the client.

Our Micro-Group workshop format training (6 people or less), is the best format for small group Microsoft application training because the students are able to learn such a large amount in a small amount of time. Our “micro-group” training workshops are held in our Irvine Training Center in the heart of the Orange County California business center.

Our one-on-one Excel training is face-to-face training on steroids; there is no better way to learn Microsoft Excel or Access. This twist on our micro-workshop hyper-focus small-group training is this, it is literally one-on-one; just you and the trainer, sitting side by side, on one computer, as you work through each topic until you totally get it.

We provide training in all of the Microsoft applications listed below, both in the Microsoft Windows versions and the Apple Mac versions:
•Microsoft Excel
•Microsoft Access
•Word
•Outlook
•PowerPoint
•SQL Server
•Azure
•Power BI

If you would like to know more, please give us a call at 877-392.3539.

Page 1 of 25123451020...Last »

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives