by Jim Ebsen | Jul 25, 2016 | MS Access
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.
by Jim Ebsen | May 4, 2016 | MS Access
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.
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!
