Most users of Microsoft Access have, at some point, encountered the message that their database has been corrupted or placed in an “Inconsistent State”. You will see a dialog with the message: Microsoft Access has detected that this database is in an inconsistent state, and the software will attempt to recover the database.
Since my start with Microsoft Access 25 years ago, the frustrating aspect of this error is that there isn’t a definitive reason why the database became corrupted. Error messages in a variety of other software programs are much more descriptive on what to do to prevent violating the software rules. In Access, for example, there are characters that are not allowed when naming fields in a table. If you choose a character that is not aligned to the rules, the message that follows is clear. Select the Help button and then follow the rules displayed regarding table name rules.
So, what does one do regarding database corruption in Access?
First, it’s important to know that most Access databases become corrupt at some point in time. I’ve found the most proven methods to deal with corruption are not to waste time trying to determine why the database got corrupted, but rather focus on methods that prevent the database from getting corrupted in the first place.
Listed below are some things that Arkware consultants do that have helped reduce database corruption significantly:
Split your database into a front-end and back-end (two separate files). All the database tables are in the back-end database and each user has their own copy of the front-end database on the desktop or local hard-drive. The front-end database contains all the database objects (queries, forms, reports, and macros/VBA). Each PC has the same folder structure such as (C:\WorkingDatabase\DatabaseName). Using the Linked Table Manager, link the tables from the back-end database to the front-end database. Access has a database splitting wizard that can assist with splitting the original database into the front-end and back-end databases. Look for a future Blog entry on using this wizard.
Force the database to be shut down at close of business or other specific downtimes. This is done because users sometimes leave the system open and the unattended Access connection may get disconnected from the network, which can cause corruption. The code can be placed on the “OnTimer” event of any database form that remains open. The best database form to use is a main switchboard form. Simply add DoCmd.Quit to the VBA code to close the system down at a certain time of the day (ie 2:00 am).
Make sure all users are using the same version of Access on all computers.
Create a MS-DOS command batch file that copies a new front-end database to the local C:\ drive of the user’s computer. This is not mandatory, but it does ensure that everyone receives a clean copy of the local front-end database.
Do not have tables from other Access databases linked to your main database that are using different versions of Access.
Disable Access auto-correct feature. This can be done by selecting File > Options > Current Database. Under “Name Autocorrect Features”, deselect “Track Name Autocorrect”
These are some of the main preventive measures to guard against database corruption. If you are struggling with database corruption, reach out to Arkware and we can assist you in achieving a more stable environment for your Microsoft Access database.
How well your database performs depends largely on its efficiency. Many factors affect efficiency, including how data is modeled and how queries are structured. If your database isn’t as efficient as it could be, a few small tweaks can make all the difference. Below we’re going to cover the best ways to improve database performance for improved efficiency, accuracy and productivity.
Check Your Server
Start by checking your database server to ensure it has the proper hardware available. Evaluate the following:
CPU. If you think your database is underperforming, it’s possible that you need to upgrade your CPU unit. Measure your CPU performance, monitor CPU ready times and make sure you are using two CPU cores.
Memory. Check the page faults per second. If you’re having thousands of page faults, you’re running out of memory and need to expand.
Disk space. Databases require a lot of disk space so make sure you have enough space at all times. Run your database on dedicated hard disks for the best performance.
Most database issues come from poor queries. For the best results, use a query optimizer to optimize your queries. This will eliminate coding loops that can generate hundreds of unnecessary requests that will slow down your database. Query optimizers also save plenty of time, as you don’t have to go through and optimize them manually.
We typically don’t recommend storing images in your database because they’re large files that slow down performance. Instead, store references and metadata so that you can easily access the image files when you need them.
Know When to Use Indexing
Indexes are used to quickly and efficiently locate data without having to search through every row in a database. As your database grows larger, you’ll find yourself indexing more often. Because indexes dramatically speed up data retrieval, it’s important to assign the correct index for each table as well as understand the differences in index types – clustered and non-clustered.
Remove Unused Tables
Over time, it’s common to have unused tables that you don’t need anymore. Eliminate them for improved efficiency. Having unused tables puts more stress on your database, as it has to run through all the tables to find the answer to the query. By removing unused tables, you can expect faster queries.
These are some of the best ways to improve database efficiency for your business. If you need help with any of these steps, or you are considering upgrading to a new database program, contact Arkware today. We have customized solutions for all businesses and organizations.
If your business has a lot of remote workers, you’re probably embracing all the benefits: happier employees, improved retention, lower operating costs and a better use of technology. However, there are some downsides to remote working, including an increased risk for data breaches.
Data breaches are a huge problem in the U.S., with more than 100,000 serious breaches reported since 2005 according to the Identity Theft Resource Center (ITRC). Of all the data breaches reported in 2019, 40 percent were due to hackers, which included scams like phishing, skimming and ransomware.
Unfortunately, remote employees are most at risk because they are susceptible to hackers. Not all employees have access to secure connections, company laptops and security training, which means they could compromise your sensitive data.
To protect your data and reputation, as well as prevent a breach that could cost millions, let’s look at some of the ways you can keep your data safe while employees work remotely.
Update your data breach response plan.
Update your data breach response plan to reflect your changing work environment. Your plan should address all new and potential risks that could occur as a result of working from home. Document the response steps your employees should follow if these risks happen.
Reassess data collection policies.
Update your IT infrastructure and reassess data collection policies. You should only collect the essential information you need – no more. Perhaps it’s time to cut down on the information you’re gathering from customers or secure this information in a different way.
Have containment strategies in place.
When a breach first occurs, you may want to delete everything, but that could hurt you in the long run. Instead, it’s best to contain the breach so it doesn’t spread and cause further damage. Containment strategies may include open communication, isolating systems, disconnecting breached accounts and blacklisting IP addresses.
Ensure employee software is up to date.
Another important step is to make sure your employees have what they need to be safe and secure. They should always use firewalls, keep their databases up to date and encrypt data using one of the main types of encryption. Also, limit permissions – only give employees the access they need to do their jobs.
Working remotely is becoming the new normal. While there are many benefits to this arrangement, you want to be sure that employees are following the rules and keeping all data secure. The best way to mitigate a breach is to ensure your data breach response plan and IT systems are up to date. For more assistance with keeping your database secure, contact Arkware today.
Good database design is essential for reliable, complete and accurate data. Certain principles lead the way in database design. These principles include not having redundant data and ensuring all information is complete and correct. When you know that your database is properly designed, you can trust it to make strategic decisions.
Let’s learn more about the importance of good database design and seven steps to achieve it.
The first principle addresses duplicate information. Redundant data is considered bad because it wastes space and increases the chances of making errors. This is understandable, as repeating data can show up in reports when it doesn’t need to be there.
The second principle is that all information within the database must be correct and complete. If your database contains inaccurate information, any reports pulled from the database will be misleading.
To have good database design, your database should be able to do the following:
Divide information into subject-based tables
Support the accuracy and integrity of the data
Give Access the information it needs to join information together
Accommodate data processing and reporting needs
Seven Steps to Achieving Good Database Design
To ensure good database design, here are seven steps to follow.
1. Determine your database’s purpose. There are multiple types of database management systems, such as relational databases, object databases and graph databases. Determine what database will best fit your needs.
2. Find and organize information. Gather all types of information you might want to enter into the database like product names and order numbers.
3. Divide the information into tables. Organize your information based on subjects or categories, such as Products or Orders. Each subject becomes its own table.
4. Place information items into columns. Decide what information you want to store in each table. Each item becomes a field and is displayed as a column in a table. For example, a Customers table might include First Name, Last Name and Address.
5. Specify primary keys. Choose a primary key for each table. The primary key is used to identify each row, such as Product ID or Order ID.
6. Establish relationships. Decide how the information is related to the information in other tables. Add fields to tables or create new tables if you need to reinforce the relationships.
7. Refine your design. Review your design for errors. Create tables and see if you can get accurate data. Also, apply normalization rules to make sure your tables are structured properly.
While you don’t need to be a coding expert to use Microsoft Access, good database design requires some experience. To have a database built or optimized for your company, contact Arkware today.
A large part of being in the construction industry is being able to deliver projects on time and on budget. Rather than using limited software to keep track of projects, project managers use databases to define custom projects, create workflows and keep everyone in the loop.
In the meantime, here are some of the ways that project managers use database software to run safer and more efficient operations.
Field & Site Management
Having a central database allows all partners to communicate with each other and manage documents in real time. Plus, critical business information can be accessed at any time, allowing partners to make informed decisions. This helps projects to be completed on time and to the highest standards. A single platform where everyone is kept in the loop also provides peace of mind for project managers who can’t be on site all of the time.
Estimating & Bidding
Custom estimation and bidding software makes it easy to stay on track with your budget and ensure everything is accounted for. After all, projects that go over budget can have your company losing money. Estimation software lets project managers run accurate projections for what each part of the project will cost. There are also options to prepare and manage bids.
Equipment & Inventory Tracking
To ensure your project has everything it needs, it’s important to keep tabs on equipment and inventory. Database solutions help project managers stay up to date on the statuses of their parts and materials by sharing real-time updates. Also included is fleet management for dispatch, scheduling and maintenance tracking. This ensures that all fleet are accounted for and properly managed.
Thankfully, today’s database solutions aren’t limited to your desktop computer. After all, you’re probably on the go and away from your desk most of the time! Thanks to mobile platforms, project managers can talk to vendors and field teams, review project plans, approve time cards, access project contacts and more. Everything can be easily accessed on any mobile device from anywhere in the world.
These are just a few examples of how database software can be used to streamline operations, improve efficiency and protect customer data all from a single platform. For more information on how Arkware can work with your construction company, contact us today.
One of the first steps in building a database is to decide whether you want to build a web app or a desktop database. There are benefits for both, so you’ll need to consider your needs, the people you’re looking to share the database with and your purpose for the database. Luckily, Microsoft Access offers a gallery of templates to make your job easier, regardless of which option you choose.
Let’s discuss the differences between a web app and a desktop database and how to choose the right one for your needs.
What is a Web App?
An Access web app is a web database that runs on most browsers. Everyone on your team can open and use this database in their browsers, no matter where they are or the device they are on.
Microsoft recommends using Microsoft PowerApps to build web apps without using any code. You can even use this program if you don’t have Microsoft Access. The fastest way to get started is by using a template. All web app templates have a picture of a globe in the background. You can also customize a web app from scratch, though this takes longer.
What is a Desktop Database?
A desktop database is a database system that runs on a single computer. Everyone can connect to the computer that the database is stored on, though each user will need to have Access installed.
Microsoft has a number of templates that can be used to build a database. All Access desktop database templates have a table icon in the background. You can also create one from scratch by making your own forms, tables, reports and other database objects.
Deciding Whether to Use a Web App or Desktop Database
IT requirements. Use an Access app if you want the reliability and scalability of storing data in SQL Server. Also, you can benefit from the latest features as soon as they become available. Though you will have to wait longer for these features to come to desktop databases, use this option if you need to consolidate data from different data sources.
Data and design. Web apps are best if you want to share the data with people from inside and outside of your organization. Anyone who has permission can view and edit the data, even if they don’t have Access. Choose a desktop database if everyone will be using the database from a networked computer and has an Access account.
Business goals. We recommend web apps for straightforward business needs and simplified solutions. Also, any changes made in a web app are immediate. Databases are more complex but also more customizable. You have full control of the user experience and access to complex functions like Visual Basic and linking to external data.
If you need to build a web app or a new database for your business or organization, contact Arkware today. Even though you don’t need to be a coding expert to use Microsoft Access, it helps to know what you are doing. We can set up your web app or database properly and make sure it provides your business with consistent, accurate and trustworthy information. Contact us today for your free consultation.