(Warning: Technology Spoilers Ahead–Proceed at Your Own Risk…)
(Second Warning: Your IT Department May Not Be Happy You Are Reading This. If you are censored in your IT environment, consider backing out now. IT Departments may not REALLY want your happiness…)
If you have Microsoft Office installed, chances are you have Microsoft Access available to you with your Excel, Outlook, and Word subscription. Access has been around since the ’90’s and is one of the most under-utilized tools available to leverage the data in your ERP system.
I recently sat down to interview the most accomplished Access developer I’ve personally known (Dan Jeppson of XS) and asked him a few questions. I was overwhelmingly impressed with his wit, charm, stunning use of puns, and overall charisma. It was intoxicating… If you would like to meet with Dan for a free consultation to discuss how he can help you implement Microsoft Access in your ERP environment, click the button below.
Question: What is Microsoft Access?
Access is a database tool. It has the capability to store its own data in collections called tables (like spreadsheets), and it can also link (virtually) to external databases and interact with them natively. This means that in addition to reporting from your ERP systems, you can also write data BACK as well. (Hint: Your IT Department might not be as happy about this part—we can help them see why it would be good to let you do it…)
Access has all the VBA tools of Excel, but with a LOT more power. While Excel can simulate a visual programming environment, it lacks real tools to build functional screens. Access has a robust Relational Database engine that works well with all SQL-compatible databases. Built-in wizards make building new screens and reports a simple activity (screens and reports can be customized in every detail by an experienced programmer.)
Question: I use Excel all the time—why would I need Access?
Spreadsheets are wonderful—they have a lot of power. Formulas, external data links, and VBA programming provide a LOT of power for intermediate-to-advanced users.
Imagine taking that spreadsheet out of your screen and placing it face up on your desk’s surface. Notice how it resembles a piece of paper–flat and two-dimensional. Even the formulas are based on a grid system.
Here’s where it gets interesting… Imagine that spreadsheet with an additional 3rd dimension–one that rises perpendicularly out of the desktop. While this isn’t (in reality) an exact metaphor for how Access behaves, it does describe the kind of power you get from it. It’s not two or three times as powerful as Excel–it’s exponentially more useful in giving you access to multiple cross-related areas of data simultaneously.
Imagine being able to (without using complex pivot tables) ask questions about your data like “what is the item with the most sales in January” or “which salesperson belongs to customer A” or “how many items have less quantity on hand than the minimum”—your data becomes an open book and you have authorship rights!
Access also allows multiple users to have the database open simultaneously on different machines. As each user enters data and saves records individually, that data is immediately available to all other users. Collaboration is possible! (Difficult with Excel…)
Question: Can you give an example of a situation where Access would provide more power than Excel?
Let’s assume that as a power-user of Excel, you’re able to set up a remote data connection to your ERP system, and you have two separate tabs. One tab contains your raw Customer data (addresses not included—they are stored separately), and another tab contains Sales Order header data (totals for orders, not the line-item detail data). By the clever use of reference formulas, pivot tables, and grouping, you built some basic sales report data from different angles. You’ll want to make sure to set your data connection settings to automatically refresh when the file is opened, or users might forget to refresh and have stale data.
By contrast, in Access you would establish data connections to your ERP data tables, but instead of snapshot copies of the database, you instead have linked virtual versions of the tables. Data is always live—refreshes are not necessary. Security layers are available, data can be linked into targeted data sets that can be reused in screens and reports. Complex object-oriented programming elements are available—including all Excel tools and objects—embedded in your screens and reports. Not only can we see data from multiple angles, but we can also build interactive screens that allow users to press buttons, modify filters, and use other familiar data entry techniques to dynamically manipulate the reports and data views. We can build rules and restrictions to enforce business rules. We can build bolt-on modules for use with our ERP system to enhance workflow and data accuracy.
Question: Isn’t .NET a better development tool for business?
.NET is great, but it’s WAY TOO MUCH for an average Microsoft Office user. The power of .NET goes WAY beyond what Access can accomplish, but it has an entirely different purpose. It’s designed to develop stand-alone applications in a stand-alone way. Its flexibility has very little to do with data. Data engines are layers that must be granularly added to the environment and manually configured. Plan on semesters of classes to learn .NET.
Access is a Microsoft Office tool that’s designed for the end user. It gives some of the main power of .NET to the average user without having to have the deep programming knowledge. Additionally, because it’s a native Database Management tool, its tools are designed for the end user—it already THINKS like an office user and doesn’t require object-oriented programming training to be successful. Every user can build simple applications to meet their specific needs.
Question: What about security? Can I really get to my ERP system data?
Depending on your organization, security can be a challenge. There SHOULD be security in place, but a well-designed data-access security paradigm should allow you to see the data you have a right to see.
If your ERP system lets you see data on the screen, your IT department should be able to provide you with data access through Excel or Access (the same security settings can govern both). {If your IT department says it’s not possible, perhaps you need an advocate… Contact us to have us interpret your needs for your IT department and help nudge them in the right direction (we speak their language)
Question: How easy is an Access database to deploy and back up?
Access normally uses a single file per database (other creative options are available, however). This file can be placed on a network file share location for everyone to use simultaneously. Each user has a shortcut on their individual machine. As new versions of the database are created for users, the common file is simply replaced, and users obtain new versions when they open it up the next time.
As long as a good backup system is in place on your network file share location, no additional effort is required. When no disaster-level backup is happening, you can copy the Access file to a thumb drive, external hard drive, OneDrive, DropBox, or other external storage location to create copies that can be stored away from your office location.
Question: It sounds like it takes a lot of skill to really use Access?
Not for users. Some of the development tasks will be more technical, but using the tool for your data needs is about as easy as using Excel…
Sometimes the hardest part is getting started. Access is a tool that makes sense when you see it in action. Contact us for a jump start with Microsoft Access.
Like other modern software technologies, there are many advanced techniques and skills. Most software can be used by regular users, but it takes some extra skill to set things up the first time or to customize with complex rules or behaviors.
Access is a fun learning environment because you can make a copy of your “live” database and play with the copy. Once you’ve experimented and come up with new functionality in the test copy, you can import your changes into the live database. It takes all the risk out of the process for a new learner. MAKE SURE to work with a power user (as a mentor) to get started—it will make all the difference.
Question: How do I get started?
Call us for a free discovery discussion. We can point you in the right direction, we can build you a custom solution, or we can help you negotiate with your IT department to get you effectively plugged in. We can help at whatever level you need.