One of the most important aspects of analyzing data with Access is keeping your database healthy. In this chapter, you will learn some of the best practices around building and maintaining your database, ensuring that it runs efficiently and error free. In addition, this chapter will teach you best ways to get help in Access when you need a push in the right direction.
When you are analyzing a few thousand records, query performance is not an issue. Analytical processes run quickly and smoothly with few problems. However, when you are moving and crunching hundreds of thousands of records, performance becomes a huge issue. There is no getting around the fact that the larger the volume of data, the slower your queries will run. Even so, there are steps you can take to optimize query performance and reduce the time it takes to run your large analytical processes.
Most relational database programs have a built-in optimizer to ensure efficient performance, even in the face of large volumes of data. Access also has a built-in query optimizer. Have you ever noticed that when you build a query, close it, and then open it again, Access sometimes shuffles your criteria and expressions? This is because of its built-in query optimizer.
The query optimizer is charged with the task of establishing a query execution strategy. The query execution strategy is a set of instructions given to the Microsoft Access database engine (ACE) that tells it how to run the query in the quickest, most cost-effective way possible. Access's query optimizer bases its query execution strategy on the following factors:
This execution strategy is created when the query is first run, and it is recompiled each time you save a query or compact your database. Once a query execution strategy has been established, the ACE database engine simply refers to it each time the query is run, effectively optimizing the execution of the query.
You've heard the phrase "garbage in, garbage out," referring to the fact that the results you get out of a database are only as good as the data you put in. This concept also applies to Access's query optimizer. Since Access's optimization functionality largely depends on the makeup and utility of your tables and queries, poorly designed tables and queries can limit the effectiveness of Access's query optimizer. To that end, there are actions you can take to help maximize query optimization.
Many users who are new to Access build one large flat table and call it a database. This structure seems attractive because you don't have to deal with joins and you only have to reference one table when you build your queries. However, as the volume of data grows in a structure such as this one, query performance will take a nosedive.
When you normalize your database to take on a relational structure, you break up your data into several smaller tables. This has two effects. First, you inherently remove redundant data, giving your query less data to scan. Second, you can query only the tables that contain the information you need, preventing the need to scan your entire database each time you run a query.
Imagine that you have a file cabinet that contains 1,000 records that are not alphabetized. How long do you think it would take to pull out all the records that start with "S"? You would definitely have an easier time pulling out records in an alphabetized filing system. Indexing fields in an Access table is analogous to alphabetizing records in a file cabinet.
When you run a query where you are sorting and filtering on a field that has not been indexed, Access has to scan and read the entire dataset before returning any results. As you can imagine, on large datasets, this can take a very long time. By contrast, queries that sort and filter on fields that have been indexed run much more quickly because Access uses the index to check positions and restrictions.
You can create an index on a field in a table by going into the table's design view and adjusting the Indexed property. Figure C-1 demonstrates this process.
Note
Fields tagged as primary keys are already indexed. You can index fields that have duplicate values by setting the Indexed property of the field to Yes (Duplicates OK). Each table in your database can have up to 32 separate indexes.
Now before you go out and start creating an index on every field in your database, there is one caveat to indexing. Although indexes do speed up select queries dramatically, they significantly slow down action queries such as Update, Delete, and Append. This is because when you run an action query on indexed fields, Access has to update each index in addition to the changing the actual table. To that end, it's important that you limit the fields that you index. A best practice is to limit your indexes to the following types of fields:
Fields where you will routinely filter values using criteria
Fields you anticipate using as joins on other tables
Fields where you anticipate sorting values regularly
Tip
Feel free to visit Chapter 2, to get a refresher on indexes.
You would be surprised how a few simple choices in query design can improve the performance of your queries. Take a moment to review some of the actions you can take to speed up your queries and optimize your analytical processes.
Avoid sorting or filtering fields that are not indexed.
Avoid building queries that select "*" from a table. For example, SELECT * FROM MyTable. This forces Access to look up the field names from the system tables every time the query is run.
When creating a totals query, include only the fields needed to achieve the query's goal. The more fields you include in the GROUP BY clause, the longer the query will take to execute.
Sometimes you need to include fields in your query design only to set criteria against them. Fields that are not needed in the final results should be set to "not shown." In other words, remove the check from the checkbox in the Show row of the query design grid.
Avoid using open-ended ranges such as > or <. Instead, use the Between...And statement.
Use smaller temporary tables in your analytical processes instead of your large core tables. For example, instead of joining two large tables together, consider creating smaller temporary tables limited only to the relevant records and then joining those two. You will often find that your processes will run faster even with the extra steps of creating and deleting temporary tables.
Use fixed column headings in Crosstab queries whenever possible. This way, Access does not have to take the extra step of establishing column headings in your Crosstab queries.
Avoid using calculated fields in subqueries or domain aggregate functions, because they already come with an inherent performance hit. Using calculated fields in them compounds your query's performance loss considerably.
Note
Subqueries and domain aggregate queries are discussed in detail in Chapter 7.
Over time, your database will change due to the rigors of daily operation. The number of tables may have increased or decreased; you may have added and removed several temporary tables and queries; you may have abnormally closed the database once or twice; and the list goes on. All this action may change your table statistics, leaving your previously compiled queries with inaccurate query execution plans. When you compact and repair your database, you force Access to regenerate table statistics and re-optimize your queries so that they will recompile the next time you execute the query. This ensures that Access will run your queries using the most accurate and efficient query execution plans.
Tip
You can set your database to automatically compact and repair each time you close it by doing the following:
Click the Office icon on the upper left-hand corner of the Ribbon.
Click the Access Options button. This will activate the Access Options dialog box.
Once in the Access Options dialog box, select Current Database to display the configuration settings for the current database. Here you will see the Compact on Close setting.
Place a check next to Compact on Close and click the OK button to confirm the change.
Corruption is a state where an error occurs in your Access database and causes unpredictable behavior or, in worst-case scenarios, renders your database unusable. To understand why corruption happens, you need to understand how the ACE database engine manages data.
ACE administers your data in a series of blocks, each consisting of 4,096 bytes of data. When you see a table in a database, you see it as a solid object, but it's actually made of blocks of data. Depending on the size the table, a table can be made of one block of data or many blocks that point to each other. Most corruption is caused by errors that occur when writing to one or more of these blocks. In fact, small-scale corruption happens all the time; you just don't know it, because ACE usually resolves these corruption issues during the course of reading and writing data. However, sometimes ACE cannot resolve issues on its own. In these cases, the database is corrupted.
There are many reasons why a database becomes corrupted. The database may have encountered errors while writing data, table definitions may have degraded over time, some VBA code or macro may have caused a fatal error, and the list goes on. The point is that because corruption can be caused by a wide range of nebulous issues, the signs and symptoms of a corrupted database are just as expansive and just as nebulous. You'll never see a message explicitly stating that your database is corrupt. So how do you know if your database is?
Databases that fall victim to corruption can generally be separated into two categories: those that you can open and work with and those that do not open at all.
The dangerous thing about corrupted databases that are still usable is that you may never know you are working with a corrupted database. It can be quite difficult to spot the signs of this type of corruption. There are, however, some reasonably clear indicators that strongly suggest corruption:
You get an error message stating "Invalid field data type" when trying to open a table in either data view or design view or when viewing the relationships window.
You get an error message stating "Could not find field Description" when trying to compact and repair the database.
When you try to open a table, a query, a form, a report, or a data access page, you get one of the following messages:
"MSAccess can't open the table in datasheet view"
"Record is deleted"
"Unable to carry out the command"
"There was an error executing the command"
You get an error message stating, "Table 'TempMSysAccessObjects' already exists" when trying to compact and repair the database.
Nothing happens when you try to open or delete a linked table.
Access unexpectedly closes and then tries to send an error report.
You get an error message falsely stating that "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship."
#DELETED# starts appearing in your tables.
Access starts to drop records randomly.
You get an error message stating "Invalid argument" when clicking on a record.
All fields for a specific record show #Error when you run a query against that record or view it in a form.
The problem with database corruption is that a wide range of nebulous issues can cause it. Therefore, you will rarely see a message explicitly stating that your database is corrupt. However, the errors listed here in Table C-1 are key indicators that point to the possibility that your database is corrupt.
Table C.1. Errors Commonly Associated with Database Corruption
ERROR | DESCRIPTION |
|---|---|
2239 | <Database Name> has detected that this database is in an inconsistent state, and cannot attempt to recover the database because the file is read-only. To allow Access to recover the database, close the database and set the file to read/write, and then open the database. |
2572 | This database is in an unexpected state and <Database Name> cannot open it. This database has been converted from a prior version of <Database Name> by using the DAO CompactDatabase method instead of the Convert Database command (click the Microsoft Office Button and then click Convert). Converting by using the DAO CompactDatabase method has left the database in a partially converted state. If you have a copy of the database in its original format, click the Microsoft Office button and then click Convert to convert it. If the original database is no longer available, create a new database and import your tables and queries to preserve your data and try again. Your other database objects cannot be recovered. |
3011 | The Microsoft Office Access database engine could not find the object <Object Name>. Make sure the object exists and that you spell its name and the path name correctly. |
3019 | Operation invalid without a current index. |
3033 | You do not have the necessary permissions to use the <Object Name> object. Have your system administrator or the person who created this object establish the appropriate permissions for you. |
3045 | Could not use <File Name>; file already in use. |
3049 | Cannot open database <Database Name>. It may not be a database that your application recognizes, or the file may be corrupt. |
3051 | The Microsoft Office Access database engine cannot open or write to the file <File Name>. It is already opened exclusively by another user, or you need permission to view and write its data. |
3078 | The Microsoft Office Access database engine cannot find the input table or query <Query Name>. Make sure it exists and that its name is spelled correctly. |
3197 | The Microsoft Office Access database engine stopped the process because you and another user are attempting to change the same data at the same time. |
3340 | Query <Query Name> is corrupt. |
3343 | Unrecognized database format <Object Name>. |
3428 | A problem occurred in your database. Correct the problem by repairing and compacting the database. |
3626 | The operation failed. There are too many indexes on table <Table Name>. Delete some of the indexes on the table and try the operation again. |
3734 | The database has been placed in a state by user <User Name> on machine <Machine Name> that prevents it from being opened or locked. |
3800 | <Name> is not an index in this table. |
7801 | This database is in an unrecognized format. The database may have been created with a later version of <Database Name> than the one you are using. Upgrade your version of <Database Name> to the current one, then open this database. |
29063 | The Visual Basic for Applications project in the database is corrupt. |
29072 | <Database Name> has detected corruption in this file. To try to repair the corruption, first make a backup copy of the file. Click the Microsoft Office Button, point to Manage and then click Compact and Repair Database. If you are currently trying to repair this corruption, you need to recreate this file or restore it from a previous backup. |
If you have determined that your database is indeed corrupt, there are actions you can take to attempt recovery. Keep in mind that your ability to fix a corrupted database depends on the nature and extent of the corruption. The idea is to follow these steps until your issue is resolved.
Make a backup copy of the corrupt database. Any recovery attempts come with the possibility of permanently disabling the database. You will definitely want a backup in case this happens.
Try working in another environment. Try opening and using the database on several local machines (especially if you are working with the database through a network). If this resolves your issue, the problem is probably not corruption. Look for other hardware or software issues.
Delete the .laccdb file associated with the database. When you open an Access database, an .laccdb file is created. This file is the mechanism that allows for multi-user operations. Deleting the associated .laccdb file will ensure that no rogue instances of the database are left hanging around. If you cannot delete the file, use the windows task manager and end all instances of MSAccess and/or any other process that could be logged into the database. In some cases, this action can actually resolve your issue.
Import your database into a fresh .accdb file. Start a new database and attempt to import your tables, queries, forms, reports, macros, data access pages, and modules from the corrupted database. In most cases, all of your data and code can be salvaged using this method.
Restore the database from a previously backed up version. If you have a backup of your database, you may want to use it to help restore some of the data you have lost.
Use an Access Repair Service. The last resort is to use an Access repair service. These services use specialized software to restore databases; with a success rate close to 99 percent. This will cost you between $50 and $200, depending on the company you use and the complexity of your issue. You can find a plethora of these services by entering corrupt Access database into any of the major search engines.
Unfortunately, there isn't a clear set of warnings alerting you that your database is on the verge of corruption. By the time you know that you have a corrupted database, it's too late. In that light, remember that preparation is a lot better that desperation. Get into the habit of taking a few simple measures that will minimize the chance of corruption and prepare you for the event of a corrupted database.
Having a backup of your database is like having a spare tire. There is no better safeguard against losing data than having a spare copy of it stored away. When you choose a backup plan, you will want to consider two things: when and where. When should you back up your database? You will want to choose a backup schedule that directly relates to your threshold of data loss. For example, if you cannot lose more than one day of data, make a backup of your database every day. If daily backups are excessive, make a weekly backup. Where should you back up you database? You will want to choose a location that is safe, accessible, and not in the same folder as your working database.
There are certain things that happen through the natural course of using a database. For example, the data blocks in the database become fragmented, the table statistics become outmoded, and the database grows. Although none of these occurrences directly lead to a corrupt database, they can contribute to one if left unchecked. Many Access users think that the compact and repair utility simply releases disk space, but several important actions are performed with a compact and repair procedure.
The compact and repair utility:
Reclaims disk space and ensures the prevention of database bloat.
Defragments the blocks of data that make up table pages, improving performance and making efficient use of the read ahead cache.
Resets AutoNumber fields, ensuring that the next value allocated will be one more than the highest value in the remaining records.
Regenerates table statistics used by the query optimizer to create query execution strategies.
Flags all queries, indicating a recompile the next time the query is executed.
These actions can play a big part in keeping your database streamlined and efficient. You can set your database to automatically compact and repair each time you close it. To do this, follow these steps:
Click the Office Icon in the upper left-hand corner of the Ribbon.
Click the Access Options button. This activates the Access Options dialog box.
Once in the Access Options dialog box, select Current Database to display the configuration settings for the current database. Here you see the Compact on Close setting.
Place a check next to Compact on Close and click the OK button to confirm the change.
The most common cause of corruption is interruption while writing to your database. Interrupted write processes can lead to a host of issues, from incomplete table definitions to lost indexes. In that vein, be sure to avoid any type of abnormal or abrupt termination of Access. Following these general guidelines will help you avoid corruption due to interrupted processes:
Always wait until all queries, macros, and procedures have completed execution before closing Access.
Avoid using the Task Manager to shut down Access.
Never place your Access database on a file server that is regularly shut down or rebooted.
Avoid power loss while working with your database. If your database is on a file server, make sure the server has protection against power surges or power outages.
When you work with an Access database, additional disk space is needed for the .laccdb file and for the normal database bloat that comes with using Access. If you open an Access database on removable media such as a memory stick or a ZIP disk, you run the risk of corruption due to disk space errors. Generally, a good practice is to copy the database to your hard drive, work with the database there, and then copy it back to the removable media when you are done.
As you experiment with new functions and tools in Access, you may sometimes need a little help or a simple push in the right direction. The first place you should look is Access's Help system. It is true that the Help system in Access has its flaws. To a new user, the Access Help system may seem like a clunky add-in that returns a perplexing list of topics that has nothing to do with the original search topic. The truth is, however, that once you learn how to use the Access Help system effectively, it is often the fastest and easiest way to get help on a topic. The following sections contain some tips that will help you get the most out of Access's help system.
You may remember the Help system in Access 97 being a lot more user-friendly and more effective than newer versions of Access. Rest assured that you are not just imagining it. The fact is that the Microsoft did fundamentally change the mechanics of the Access Help system. In Access 97, when you entered a key word into the search index, Access did a kind of global search, throwing your search criteria against all the topics within Access.
In the later versions of Access, however, there are actually two Help systems: one providing help on Access features and another on VBA programming topics. Instead of doing a global search with your criteria, Access throws your search criteria only against the Help system relevant to your current location. This means that the help you get is determined by the area of Access in which you are working. In that vein, if you require help on a topic that involves VBA programming, you will need to be in the VBA Editor while performing your search. On the other hand, if you need help on building a query, it's best to be in the query design view. This will ensure that your keyword search is performed on the correct Help system.
When you search for help on a topic, Access checks to see if there is an Internet connection available. If there is, Access returns help results based on online content from Microsoft's site. If no Internet connection is available, Access uses the help files locally stored with Microsoft Office. One way to maximize the help you are getting in Access is to use the online help. Online help is generally better than offline help because the content you find online is often more detailed, and it includes updated information as well as links to other resources not available offline.
Familiarize yourself with a handful of Web sites and forums dedicated to Access. These resources can serve as supplemental help, not only for basic Access topics, but for also giving you situation-specific tips and tricks. Table C-2 gives some sites that should get you started. These sites are free to use and are particularly helpful when you need an extra push in the right direction.
Access topics and general help |
|
Access tutorials and samples |
|
Access discussion groups and forums |
|