(877) 519-4537 info@arkware.com

CASE STUDY: ARKWARE MS ACCESS CRM

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. 

CHALLENGE

The DPS Team was managing staff and projects by utilizing many different tools including 

  1. MS Outlook for contacts, interaction history, file storage, scheduling and reminders  
  2. MS Excel Spreadsheets for project management reports and recordkeeping, and 
  3. 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.

SOLUTION

Arkware’s MS Access CRM featuring common project data, service history, contact management, and responsibility assignments.

OUTCOME

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

 

 

 

 

Building A Microsoft Access Database

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:
      1. Organization – Including Organization Name, Address, Address2, City, State, Zip, Phone, etc.
      2. Contact – Including First Name, Last Name, Phone, Email, etc.
      3. Lead Source – Including Source Name, etc.
      4. Lead Status – Including Status, etc.
      5. Lead Details – Date of Lead, Product or Service of Interest, Notes, etc.
      6. 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.

 

 

MS Access Corruption Part 2: Best Practices for Prevention and Recovery

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.

MS Access Corruption Part 1: Common Causes

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

Software

  • 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.

Users

  • 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.

MS Access: Advantages and Disadvantages

Most new organizations or projects quickly move from managing their workflow on paper to tracking their data on spreadsheets. The next logical step is to implement a relational database that properly links all your data and ultimately provides many benefits for your investment dollars.

MS Access Support

What can a database do for my business?

  • Organization: A database can help to provide structure to your business processes.
  • Data Validation: A database allows formatting rules for data entry (i.e. fields may be required, have strict formatting like a phone number or zip code, or have a maximum number of characters).
  • Efficiency: Databases eliminate the need for the redundant data entry that is typical of spreadsheets.
  • Data Security: Databases have more user security than a spreadsheet in the form of user access into the system and restrictions on access to the data that is viewable.
  • Reporting: Quite simply, reporting is much more robust from a database where ranges of records for a single customer or dataset can be quickly generated.
  • Searching: For the same reason as reporting, searching by multiple criteria is easier with a database and allows users to quickly hone in on specific records.
  • Sharing Data: Many database users can access the same file and data at one time. Therefore, there is no file duplication, individual user edits and formatting conflicts that are common with spreadsheets.
  • Greater Capacity: Databases accommodate a very large volume of data.  In comparison, spreadsheets are much more limited.
  • Historical Data: Older data can be archived in a database much easier or easily accessed for reporting. Keeping all historical data active would most likely push the limits of a worksheet to being too big and impractical.

Why choose MS Access over other databases?

  • Popularity: MS Access is the most popular desktop database and comes standard in many MS Office packages.
  • Inexpensive: The MS Access software is very inexpensive (not including development time).
  • Self-Starting: A novice user can achieve useful results.

What are some limitations of MS Access?

  • Lack of Support: It is common that the early MS Access development efforts are initiated by a team member that already has a full-time responsibility. When these team members are no longer available or have left the company there often is no one left with the experience to continue supporting the database.   Furthermore, many experienced consultants are currently choosing retirement or have moved on to more modern database solutions; leaving a void for legacy MS Access databases that need support.
  • Corruption: There are many reasons that a MS Access database file may become corrupted or damaged. Both hardware failures and software problems are to blame. Without adequate protection from these issues, these databases can become very unreliable.
  • Scalability: While multiple users are allowed in MS Access, the database gets quickly overburdened. Additionally, compared to other databases, MS Access has low limit for the maximum amount of data supported.
  • Lack of Developer Experience: Many do-it-yourself, non-programmer developers quickly get over their heads during development. Furthermore, early design decisions may heavily impact the usability and expandability of the database down the road.
  • Upgrades: Many challenges, such as version incompatibility, are often encountered when migrating current data to the updated software.
  • File Server: MS Access is a File Server application (not a Client Server Application).  That means that the entire database and software resides in the memory of each user’s computer. This is inefficient and can lead to errors and potential corruption.
  • Complexity is Predicted: If it is expected that the use of this database will become mission critical or serve a much greater need than originally planned, it is recommended to seek alternative databases such as one designed using SQL Server.
  • Compliance: Certain rules (such as the requirements of Sarbanes-Oxley Act – SOX, HIPAA & PCI – Payment Card Industry) that apply to many companies warrant the use of a higher-end, fully auditable database.

The database needs of an organization are usually unpredictable and change over time.  Healthy databases are created and eventually grow and modify over the life of a business.  When faced with these challenges, MS Access databases often continue to be used “as-is” and generally serve well-enough.  However, when an emergency comes up, reliable, experienced support may be hard to find. Therefore, it is highly recommended to plan early for the next stages of development and have an experienced team to help.  Whether a small database improvement or a full plan for the future is needed, Arkware is ready to be your guide!MS Access