(877) 519-4537 info@arkware.com

Microsoft Access Table Tips – Tricks & Guidelines Part 2

Continuing our series of Tips – Tricks and Guidelines with Microsoft Access shares some additional items for this month. The ongoing articles will continue to focus on the tables within an Access database. 

Using tables for drop down lists?

The tables within an Access database are used to store all the database information.  This is the fundamental purpose for all the data within the database.  But what other reasons would there be to store other components of the database?  

Throughout most databases, there are choices for drop down lists in forms.  For example, a customer table will store a field such as a state within the United States.  Another example would be a specific product that a company is selling or manufacturing or another example would be a table to store employees, etc..  There is an option in a form called a value list, but to better manage the choices, a separate table should be used to store the specific items that are contained in the list.  

To ensure that each record has the proper value, the table of options should be the primary source of the value.  You would not want the end user to “make up” values on the fly as this could lead to entering inconsistent values for whatever the reason.

The employee table as noted below may have the following design.

As noted, it makes sense to set the status of each employee to “Active” as these are the employees that will be included in the drop-down list.  By not deleting them, then they are still available for future use.

Tip – Naming the actual dropdown list table a proper way can be helpful.  For example, naming all tables for drop down lists starting with “TblDrp……” will store them all together in the database window as noted in the below figure:

Validation of data when entering data into the database at the table level

Along with drop down tables forcing accurate data into tables, another method within the table itself is setting a validation rule.  This is done in the design of the table inside the field properties itself.

For example, to make sure an accurate shipping date in the future is entered, you can use a validation rule to control this by doing the following.

Validation Rule:[ShippedDate] >=Date()

Validation Text: The shipping date must be the current date or a date in the future.

See the figure below

When the validation rule is “broken”, then the following message appears. See below figure.

These are just a few time saving tips when using Access tables.  If you are having trouble knowing how to get started with Microsoft Access, reach out to Arkware today for any database needs.

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!!

 

Benefits and Drawbacks of Using Stored Procedures

Stored procedures are prepared SQL codes that you can save, allowing you to use them many times. This helps you be more productive in less time while also reducing data errors. As beneficial as stored procedures can be, there are some concerns over portability and testing. 

Let’s cover the pros and cons to using stored procedures and whether or not you can benefit from this reusable SQL code. 

What is a Stored Procedure? 

A stored procedure is a type of code in SQL that can be stored for later use. Whenever you need to execute the query, you can call the stored procedure to save time. You can also pass parameters to a stored procedure so that it can act based on the parameters you passed. 

It’s also possible to set up multiple parameters. Just list each parameter and the data type, separated by a comma. Hopefully this has helped you better understand what stored procedures are and how they can be used for your SQL database. 

Benefits of Using Stored Procedures

If you’ve upgraded to an SQL database, here are some benefits to using stored procedures: 

  • Better performance. Procedure calls are fast and efficient. They’re compiled once and stored in executable form. This lowers memory requirements as well.
  • Greater productivity. Since the piece of code is used many times, you’re able to be more productive, more efficient and less redundant. 
  • Easy to use. Most of our clients have an easy time using stored procedures once they learn the ropes. All you need is Microsoft Access and .NET C#. 
  • Scalability. Stored procedures increase scalability by isolating application processing on the server. 
  • Security. It’s possible to restrict access to Microsoft SQL Server data by manipulating the data only through stored procedures that the user has access to. This way, they can use the procedures to update the database but not have access to the table itself. 

Concerns Over Using Stored Procedures 

While there are many perks to using stored procedures, there are a few things to be aware of: 

  • Testability. Business logic, which is encapsulated in stored procedures, is difficult to test. If there are data errors, you won’t know until runtime.
  • Debugging. Debugging stored procedures is challenging but possible. However, you’re at the mercy of a database profiler to track down an application issue or debug your database. 
  • Versioning. Stored procedures do not support versioning. However, you can work around this issue by putting stored procedures into a version control system. 

Should You Be Using Stored Procedures? 

Even though there are some limitations and drawbacks to using stored procedures, we generally find that the benefits far outweigh them. Thanks to these procedures, our clients are able to be productive and efficient with their time. To learn more about using stored procedures, contact Arkware today for a consultation. 

 

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 (Free Access CRM Template) 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

 

 

 

 

7 Good Tech Habits All Employers Should Adopt

February 9th is Safer Internet Day, a day to recognize the importance of internet safety. It’s easy to take the internet for granted because it’s such an integral part of our everyday lives and embedded in almost everything we do. But it’s still important to adopt good tech habits that will keep your information safe from hackers. 

Below are seven good tech habits that you and your colleagues should practice daily. 

1. Backup your computer. 

We probably sound like a broken record, but we can’t stress enough the importance of backing up your computer. No one is exempt from data loss, after all. Plus, backing up your files is incredibly easy. You can set it up to have your backups run automatically. This way, if there’s ever a problem, you can revert to your last save. 

2. Keep hard drives and desktops clean. 

A cluttered desktop makes things hard to find and slows down your productivity. It can also slow down your computer. Go through your desktop and get rid of files and folders you no longer need. Or at least condense your folders so that you can stay organized and find information quickly.

3. Avoid getting malware. 

Be sure to install a good antivirus software program on your computer to get rid of viruses and protect yourself from hackers. Even if you’re not getting viruses, you could still be spreading them to others. Here’s a good article on protecting your database from hackers. Aside from antivirus software, also encrypt all data. 

4. Stay safe on public WiFi.

If you have employees working remotely, make sure they stay safe on public WiFi. Chances are, some of your employees will be working from places like Starbucks, but using public WiFi can open up your data to all types of attacks. And, just because your employees use a password doesn’t mean it’s safe. Anyone who is on that network can access your data.

5. Be smart about internet scams.

Make sure that your employees are aware of hoaxes, scams and myths. Scammers are a lot more creative these days, so internet scams are not always hard to fall for. Educate employees on these hoaxes and how to avoid them. If something looks out of place, it probably is. 

6. Know the maintenance your computers need. 

All computers need routine maintenance to keep them in good working condition. Your IT team is responsible for this, but you can do your part as well. Some of the best things you can do include updating software, running antivirus software and backing up your hard drive. 

7. Use secure passwords. 

Even the most secure passwords aren’t that secure. Hackers are much more sophisticated these days so it’s much easier for them to uncover passwords. Also, saving your passwords to your browser isn’t that safe so choose a safe password manager instead. 

Practicing these habits will keep your sensitive information protected. If you’re concerned about how secure your database is, contact Arkware today. We can take a look at your database and make sure that all information is encrypted and secure.

How to Troubleshoot These 3 Common Access Problems

Even though Microsoft Access is easy to use and understand, it still gives users a fair share of problems. Fortunately, most of these problems have a simple solution. And because Access is such a widely used database program, it’s easy to find advice from people who have experienced the same problems. 

Let’s look at three common Access problems and the best ways to solve them. 

1. Normalizing Empty Access Tables 

One of the most important things to do when building a database is building the table structures properly. This process is known as normalization and it prevents databases from having tables with redundant information. Redundancy isn’t a good idea for databases because it can lead to inconsistent data. 

To normalize your database, follow these steps: 

  • Examine each table. Are you repeating information unnecessarily? 
  • Identify duplicate information. Why are you repeating it? 
  • Break the table into two. Where there is redundant information, split that table into two tables. 
  • Repeat these steps until all redundancy is eliminated. 

2. Automatic Rounding 

Automatic rounding can be frustrating, but it’s an easy fix. By default, Access sets all number fields to accept long integers (negative or positive whole numbers). If you want to input numbers with decimals, you have to change the field-size setting so it can accept them. Here’s how to do it. 

  • Open the table in Design view and click the field that’s giving you trouble. 
  • On the General tab of the Properties area at the bottom of the screen, click the Field Size box. 
  • Click the down arrow at the end of the box and select Single, Double or Decimal from the drop-down menu. 

3. Autocorrect 

Autocorrect is meant to be a useful tool, but sometimes, it can be an aggravating one. Databases contain a lot of acronyms, part numbers and unique names that you won’t want changed. In fact, you might not even realize that the Autocorrect feature is changing these words until it’s too late. 

There are two ways to solve this problem: 

  • Turn off Autocorrect entirely. Click the File tab, click the Options button and select Proofing. Click the Autocorrect Options button and uncheck some or all of the boxes. 
  • Undo Autocorrect as it happens. Press Ctrl+Z right after Autocorrect changes your data entry. This will return the entry to the way you entered it. 

Whether you’re facing these common issues or others, it helps to have a team of database experts on your side. Arkware offers Access database programming, development and support. Contact us today to learn more about how we can help your database run smoothly!