Microsoft Access – The Basics
Easy Development for Non-Developers
As is typical of the many applications developed by Microsoft, MS Access has been designed to be intuitive for the user. With many in-program wizards, help screens, drag and drop features, and even online training tools provided by Microsoft, it is easy to get started in MS Access.
Access Video Training
What you need to start: MS Office Versions with MS Access
Microsoft Access is a low-cost and easy-to-use database development application that is included with specific versions of Microsoft Office. Modern MS Office packages where MS Access is included are 2019, 2016, & 2013 in both the Professional and Professional Plus editions. For MS Office 365, the minimum licenses that include MS Access are Business Standard and Microsoft 365 Apps.
Free, Runtime Use
To do development, or to make alterations to an existing database, a full version of MS Access is required. However, the free, MS Access runtime package allows an MS Access database to be distributed to users who do not have the full version of Access installed. See Run Time Download 1; Run TIme Download 2
Start with a Template
To give you a jumpstart, Microsoft has many free MS Access database templates available for download so you can quickly begin customizing your first database to meet your needs. There are many options to pick from, but one of them is certain to help you hit the ground running.
Perhaps a few simple modifications could get you the outcome you desire.
Not seeing what you need? Arkware is your answer.
Arkware has provided MS Access services to many different industries. If you need a full, low-cost package from start to finish, or if you are stuck in one of the finer details of MS Access, please let us know and we would be glad to help. We are your MS Access experts!!
INDUSTRY: Plumbing and Septic Services
LOCATION: Minneapolis, Minnesota
COMPANY SIZE: 1-10 Employees
KEY REQUIREMENTS: A low-cost solution encompassing Contact Management, Service Records, Custom Fields, and Centralized Data.
DAN’S PLUMBING AND SEPTIC Implements Arkware’s MS Access CRM
DAN’S PLUMBING AND SEPTIC (DPS) specializes in providing quality plumbing and septic services in the Minneapolis area. For over 25 years, DPS has expertly managed everything from large-scale plumbing jobs to residential septic system emergencies. The DPS team includes fully licensed and friendly team members to accomplish projects on-time and within budget.
The DPS Team was managing staff and projects by utilizing many different tools including
- MS Outlook for contacts, interaction history, file storage, scheduling and reminders
- MS Excel Spreadsheets for project management reports and recordkeeping, and
- MS Word for document templates and other customer reporting.
Although a common method for small companies to conduct business, DPS had outgrown this strategy of recordkeeping. To avoid the possibility of project communication challenges, the business managers at DPS determined that proper management of internal data would likely lead to increased sales, improved project communication, a more professional image, greater customer satisfaction and, ultimately, increased profitability. The leadership team believed these factors to be a significant justification to invest in a CRM solution that could be further customized as to their unique needs as their business continues to grow.
Arkware’s MS Access CRM (Free Access CRM Template) featuring common project data, service history, contact management, and responsibility assignments.
DAN’S PLUMBING AND SEPTIC was able to quickly deploy Arkware’s MS Access CRM. DPS has seen a vast improvement in communication related to projects, increased team member accountability and an increase in sales due to timely interaction follow-ups. DPS implemented the CRM requiring no assistance from Arkware and is interested in some minor customizations in the next year to meet their service needs.
The DAN’S PLUMBING AND SEPTIC Testimonials
“DAN’S PLUMBING AND SEPTIC Mission Accomplished”
Aligning around a common approach
“The staff at DAN’S PLUMBING AND SEPTIC works a broad range of hours and from locations including the central office, project locations and, as needed, other locations where internet is publicly available. As such, our individual staff members were communicating project details via text, email, and phone. Due to multiple communication styles, communications were often delayed until a team member could resume working from their desk. When we evaluated MS Access, we found that it is a tried and true database solution(offered as part of the MS Office package), coupled with Arkware’s MS Access CRM, the cost barriers to utilizing a full working database were all but eliminated. It was an easy decision and we quickly jumped at the chance to make process improvements in our business. What we found was we got so much more! What we got was consistency in the form of organized processes. In the end, we have achieved important improvements to our communications, better cost control and savings, and customers that felt like we were more professional in handling our services for them.“
“..The transition to Arkware’s CRM was much easier then we ever imagined. We deployed it ourselves and were able to begin using the system immediately. I really wish we had done this years ago!” – Mark Helm – Manager
“One of the most comforting facts is our CRM is a Microsoft product. So we know that as our needs change over time, there will always be professional solutions and we will always have unlimited visibility to our own data.” – DPS Team Member
Where do I Start?
A general rule for creating a database (or a database feature) is to envision the minimum solution needed to satisfy the goal. Keeping it simple applies especially to beginners. If you want to build a system for Tracking Sales Leads, don’t add in building a Customer Service database or Billing System at the same time. Then, once your new application is working well, you can add new features and integrate other business aspects into the design.
Before you start, it is good practice to plan out your development so you can always envision your goals and avoid unnecessary hurdles. Getting your team members involved will also help to define the project as well as increase buy-in to use of a new system.
MS Access database designs should always begin with some simple concepts related to your organization:
1. Define the Goals
What challenge(s) will the database resolve?
- How will the organization benefit? i.e., streamlining process, enforcing strict paths of work, single source recordkeeping
- How will the users benefit? i.e., organized data, quicker retrieval of data, less redundant data entry
- How will the customers benefit? i.e., comprehensive/consistent reporting, faster customer service, better tracking of history
2. Define the Workflows on Paper
- Write down the steps necessary in your ideal workflow. For example, in a system for Tracking Sales Leads, write down the full list of the ideal steps from a sales lead all the way through to closing a sale. There may be exceptions to the ideal path that need to be addressed. It is often a good idea to address these exceptions once the new system is proven to work well for a majority of the cases.
3. Design Tables and Data Fields on paper
- Tables are the building blocks of the database and should include all the data you are hoping to manage in your new system. Typically, all the data points that are included in your forms and reports will be represented in a field within a table. In a Sales Lead Tracking database, you might include tables and fields such as:
- Organization – Including Organization Name, Address, Address2, City, State, Zip, Phone, etc.
- Contact – Including First Name, Last Name, Phone, Email, etc.
- Lead Source – Including Source Name, etc.
- Lead Status – Including Status, etc.
- Lead Details – Date of Lead, Product or Service of Interest, Notes, etc.
- Team Member – Initials, First Name, Last Name, etc.
4. Design Forms and Reports on Paper
- Your forms and reports are going to be your gateways to putting data into the system, managing the data that is in the system and pulling data out of the system. Gather all the applicable forms and reports that you already use or sketch up the ones you will need. Ensure that all your essential data is captured on these documents.
In our next article, we will discuss how to create your tables in MS Access.
Data corruption in MS Access is very common. (Part 1 of this article identified many possible causes.) In multi-user MS Access systems data corruption is even more common. For businesses that have come to rely heavily on the uninterrupted performance of their MS Access database, preventative practices and planning for a disaster is the best course of action. In addition, doing some forward thinking on the next stage of development for the business is prudent.
Best Practices for Prevention MS Access Corruption
- Train users to simply close the database when not in use
- Consider using a power conditioner or battery backup for each workstation
- Troubleshoot and replace bad components, computers, and network hardware
- Ensure the latest MS Office service packs and JET packs are installed
- Remove unnecessary software from each workstation
- Keep the computer operating environment at the ideal temperature and humidity
- Ensure the hard disk has plenty unused of storage space. Depending on your database size, this should minimally be 200 – 300 hundred megabytes of free space.
- Periodically run the Compact and Repair Database command
- There are two main reasons to run the Compact and Repair Database Command.
- First, as a database gets used the database file gets larger and becomes less efficient. Periodically running the Compact and Repair Database command will reclaim unused disk space that was held by deleted records or other temporary objects and optimize the use of the disk space so that performance does not continue to degrade.
- The second reason a user should run the Compact and Repair Database command is to repair the database due to corruption or if other problems are suspected. In the case of corruption, if the software detects a problem, MS Access will prompt the user to run the Compact and Repair Database command at start-up. Upon completion of the command, MS Access will provide feedback on the success of the operation. In the event, MS Access does not fully repair the database, contacting an expert that is familiar with repairing MS Access databases is your next step.
- Note: Before Running the Compact and Repair Database command:
- Always make a backup of the database
- Notify other users to log out of the database
- Consider splitting the database into front end and back end
- Splitting the database is a common practice that is built in to the MS Access software. Ultimately this process creates two linked .mdb files. The first file, the “back end”, contains all of the unseen details of the data in tables and their relationships as well as the data itself. The second file, the “front end”, includes all of the interface components that the user sees such as forms, queries, modules, macros and reports. By splitting the database each workstation will already have a copy of the front end on their system. Therefore, only the data is shared from a central network. This not only improves performance but, ultimately, significantly increases the security and reliability of the data.
Backup, Backup, Backup!!!
A backup copy of the full database is the fastest and safest way to recover from corruption or any other unintended changes to a database. Therefore a strict backup plan should be implemented and followed. How often a backup copy should be saved mostly depends on how often your database has major changes. Many businesses will perform a backup every 24 hours (at a time when there are no other users in the database). In general, it is more critical to perform regular backups when the database has design changes, large amounts of data changes, more than one user updating the data, or when an action query is performed to delete records or change data. In many cases, it makes sense to make a copy immediately prior to making any major design or large data changes.
Expert Tip: Be proactive!
The good news is… your database has served you very well. The bad news is…your database has become critical to your organization and you can’t live without it. Be proactive! The best time to be ready to move to a new database is before any disaster strikes. There are many choices for highly reliable and full featured databases from off-the-shelf to fully customized solutions. Each database option might manage e-commerce, online marketing applications, inventory, finances, record and image storage, online reporting security or any other feature in a more favorable manner than other options. The best fit for your business may not be obvious and the wrong choice can be costly. For these reasons, it is strongly advised to use a knowledgeable and experienced database expert to help with your selection.
One very common issue reported by the MS Access user community is data corruption. As every businesses relies heavily on their database, it is frightening to imagine operating a business for any period of time without it. When data corruption strikes, the solution may take an extended time to resolve. Often, a talented and experienced database solutions expert can quickly fix the database. At other times, the solution may be much more time consuming. In the most severe cases, a business may experience permanent loss of portions or all of their data. In this event, the only solution may be to manually recreate the lost portions of the database and continue forward. At this crossroad, a tough decision must be made. Do you rebuild using MS Access? Or do you take this opportunity to upgrade to a more robust and secure database application?
What Makes MS Access Vulnerable?
The most powerful feature of any database is that it can be utilized simultaneously by multiple users in different offices. Since MS Access is a “file system” relational database, every part of the database (such as the data tables, queries, forms, reports, etc.) is stored within as little as one .MDB file. Therefore, MS Access can often be utilized in a single user environment for a long time without any sign of corruption. However, in a multi-user environment the .MDB file(s) has to be shared by many users in different locations on the network. In simple terms, this means that every record within the entire database is temporarily utilized on a single workstation while other users are accessing the same records from other workstations. This is, unfortunately, the way MS Access works and creates many more opportunities for corruption as compared to other systems. With a database helping to manage mission critical activities such as record keeping, finances, scheduling or many other functions, it is prudent to take measures to prevent data corruption. In Part 2 of this post, we will discuss the best practices for preventing corruption in MS Access databases.
Common Causes of MS Access Corruption
- Any application crashing (MS Access or any other one) and bringing down others.
- Workstations on different MS Access service packs (particularly JET) causing inconsistent behavior.
Hardware / Systems
- Any time the file server is subject to a reboot, shutdown or failure.
- Power Supply Issues: inadequate power supply, spikes, dips, brown outs, outages
- Overheating, intermittent components, device conflicts.
- Intermittent cable connectors, hubs/switches, network cards
- Unstable networks such as WiFi.
- Insufficient hard disk space for the temporary folder and/or virtual memory.
- Switching computer off without closing MS Access.
- Leaving the database open when not in use
When Corruption Strikes
When a file system disaster occurs, MS Access may provide a warning and prompt the user to run the Compact and Repair Database command. Before running this process, make a copy of the corrupted .MDB file, ensure that you have the latest backup of the database and have all of the users log out of the database.
If running the Compact and Repair Database command does not repair the database, you may need to use an alternate method of recovery. There are other tools available that report to fix MS Access databases. However not all tools are the same. Therefore, it is highly recommended that you utilize a reputable expert to determine the best tool to recover your .MDB database files. An inferior repair tool or an inexperienced person may, in fact, make the situation worse instead of better. Be sure to select an expert that has experience in repairing MS Access databases.
—-In Part 2 of this Post we will discuss the best practices for preventing MS Access Database corruption.