Handling "Divide by Zero" errors in queries
Learn how to Handling "Divide by Zero" errors in queries in Access 2003.

---

Action queries: suppressing dialogs, while knowing results
How to use the Execute method to run action queries in Microsoft Access, avoiding unnecessary confirmation dialogs while still being notified of any errors and knowing if the query completed successfully.

---

Correct speech recognition errors
Dictate over errors, Spell over errors, Retype errors.

---

Handle Run-Time Errors in VBA
When you are programming an application, you need to consider what happens when an error occurs. An error can occur in your application for one of two of reasons. First, some condition at the time the application is running makes otherwise valid code fail. For example, if your code attempts to open a table that the user has deleted, an error occurs. Second, your code may contain improper logic that prevents it from doing what you intended. For example, an error occurs if your code attempts to divide a value by zero. Learn how to trap run-time errors.

---

Compact and Repair a Microsoft Access Database Files
As you work on your database, particularly when developing it, the Microsoft Access file will grow and grow. Microsoft Access files never automatically get smaller, even when you have deleted data from them. As a database continues to grow, there may be errors that spring up along the way. As the database administrator, it will be your responsibility to manage these. When errors do occur in your database, they will take up valuable space and this may lead to performance issues.

---

Common SQL Mistakes/Errors and Resolutions
In this discussion learn several typical errors and their resolutions, common logical shortcomings of SQL users and ways to prevent daily setbacks caused by errors.

---

Access 2007 reserved words and symbols
"Reserved words" are words and symbols that have a specific meaning to Microsoft Office Access 2007 or to the Access database engine. If you use a reserved word or symbol to name a field in a table, Access warns you that the word is reserved and that you might encounter errors when referring to the field. You might also encounter errors if you use a reserved word to name a control, an object, or a variable. The error messages you receive don't necessarily tell you that a reserved word is the cause of the problem. As a result, it can be difficult to identify what needs to be changed.

---

Surviving Subqueries
This article troubleshoots eight common errors and frustrations you encounter with subqueries. Learn techniques to avoid 8 common errors, frustrations, limitations, and bugs when using subqueries in Microsoft Access.

---

Trappable Microsoft Jet and DAO Errors
Trappable errors from the Microsoft Jet database engine can occur while an application is running, either within the development environment or as a stand-alone executable file. You can test and respond to trappable errors using the Error object.

---

Updating Records in Microsoft Access with an Update Query
A situation may arise where we need to update many records in a database table when certain information changes or needs to be modified. A Microsoft Access Update Query updates specified values in a table for all records or for those records that match a specified criteria. It is possible to update records manually in a database table, either via a form or through the tables datasheet, however this may take a very long time. Changing records manually is not only time consuming, it is also inefficient, and lends itself to errors as you update the records. The best way to handle this type of event, the updating of many records in a database table, is to use an Update Query to make the changes to data in one operation. You will save yourself time, and eliminate the possibility of manual errors.

---

Conditionally Formatting Microsoft Access Form Data
Most Microsoft Access database applications will contain user forms, both for data entry and for viewing the data. These database forms are what most of your users will see and work with every day. Whilst most of the form design is intended to allow the user to enter the correct information into the correct database tables, sometimes, you may wish to draw your user's attention to a particular control or value on the form. This article will address this topic, and will deal with changing the format of a form control based upon a condition that you set. As database users can make mistakes, and this number of errors can increase when the user is distracted or hurried, anything that you as the form designer can do to reduce the errors or draw the users attention to important information will help.

---

Guide to table relationships
One of the goals of good database design is to remove data redundancy (duplicate data). To achieve that goal, you divide your data into many subject-based tables so that each fact is represented only once. You then provide Microsoft Office Access 2007 with the means by which to bring the divided information back together - you do this by placing common fields in tables that are related. To do this step correctly, however, you must first understand the relationships between your tables, and then specify these relationships in your Office Access 2007 database.

---

Rounding in Access
To round numbers, Access 2000 and later has a Round() function built in. Learn: how to round numbers in Microsoft Access: to the nearest number, rounding up, rounding down, to the nearest 5 cents, rounding dates and times, bankers rounding, and handling floating point errors.

---

Gem Tips
Find out how to increase your productivity with Microsoft Access, prevent common errors, and help prevent database corruptions.

---

Microsoft Access Macros vs. VBA - Why Use Macros?
Macros have been used as development tool since the early days of the Microsoft Office product line. Microsoft Access macros incorporate generalized database functions using existing Microsoft Access capabilities. Errors in a macro can be easily resolved by using the Microsoft supplied Help function. The ease with which you can generate Macros makes Macro development seem easier to accomplish...

---

Access 2000 Data Validation
This article details how to create your own Access 2000 validation routines. While Access 2000 has its own validation rules that you can create for each control, I've found it easier to use Visual Basic techniques to validate data. You may wonder why these validation routines were created in Access when the data types and bound controls could prevent most of the data errors. In truth, I use these routines to validate using Active Server Pages by converting the validation routines to Javascript. The Javascript was converted to vbascript and run in this Access 2000 demonstration. You may find creating your own validation routines more flexible and functional than the built-in validation rules in Access.

---

Collect data by using e-mail messages in Microsoft Access 2007
This tutorial deals with a new feature introduced to Access 2007. Until now the possibility to retrieve information into your Access data base from infrequent users who are not connected to you application was limited to several options. So far the easiest way 'but also quite long' was to explore the data base as Excel file and then send it as attachment to the persons who needed to fill the information and when you get the info, someone would have to put it in the data base...long process not to mention that a lot of errors might occur

---

Table of operators
An operator is a sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators. Access supports a variety of operators, including arithmetic operators such as +, -, multiply (*), and divide (/), in addition to comparison operators for comparing values, text operators for concatenating text, and logical operators for determining true or false values. This article provides details about using these operators.

---

Best Practices When Using Microsoft Office Access 2003 in a Multi-user Environment
Learn techniques you can use to improve the performance and efficiency of working with your multi-user Access applications. This article looks at ways you can improve the way you work in a multi-user environment with Access applications. By utilizing some of these techniques, you can reduce the chance for errors and improve the performance of you databases.

---

Migrating a MS Access DB to MS SQL
This is a step by step guide to the procedure that I have used to successfully migrate four of my SkyPortal sites from their original MS Access database over to MS SQL. Previously I had tried the MS Access upgrade wizard, Direct Import to MS SQL, various 3rd party migration tools, but none of them were 100% successful, they all got the data into the MS SQL database, and the SkyPortal software ran ok until you tried to enter any data, many errors reporting "Null fields", "Auto Numbering" and "index not set" problems occurred leaving me scratching my head wondering where I went wrong.

---

Introducing Microsoft Office Access 2003
Microsoft Office Access 2003 is the database management program that gives you an improved user experience and an expanded ability to import, export, and work with XML data files. Working in Access 2003 is easier because common errors are identified and flagged for you with options to correct them. Additionally, a new feature to Access 2003 also helps database developers view information on dependencies between database objects. Learn: Extending databases, Importing, exporting, and linking data, Protecting information and Enhancing the user experience.

---

VBA Traps: Errors in Logic
This article is the third in a series discussing subtle bugs that lie unrecognized in your code until particular conditions are met. Common mistakes developers make when writing code in a Microsoft Access database.

---

Import XML data into an Access database
Sharing data across software applications is inevitable in any business environment. Knowing how to update your Microsoft Access 2007 database with data from various applications will facilitate easy management of data. There will be occasions when you need to exchange information with programs whose data is formatted in a different manner. Transferring data manually is cumbersome, time consuming, and often results in errors. Knowing how to use the Microsoft Access data conversion techniques can eliminate these problems and allow you to handle any conversion situation you encounter.

---

MS Access as a Documentation Tool: Display Object Dependencies
In this article, we will examine a feature that is new in MS Office Access 2003, and which offers great potential in our documentation, maintenance, and general upkeep efforts, from the perspective of the organizations MS Access databases. In MS Office Access 2003, we can directly and easily view information on dependencies between database objects. The capability to view a list of a database's objects, particularly within the context of how those objects relate to each other, can help us to maintain a database over time. The capability is perhaps most useful in helping us to avoid errors that emerge with the uninformed removal of record sources or other databases objects, but numerous other potential uses exist, as well.

---

Connecting to a Microsoft Access database with ASP.NET
In this article, I shall discuss different methodologies for connecting to a Microsoft Access database using ASP.NET without any errors.

---

Common Errors with Null
How to avoid the most common mistakes people make in handling Nulls in a Microsoft Access database. Covers nulls in criteria, in expressions, in foreigh keys, in VBA, in comparisons and criteria.

---

Display Object Dependencies
In this article, we will examine a feature that is new in MS Office Access 2003, and which offers great potential in our documentation, maintenance, and general upkeep efforts, from the perspective of the organizations MS Access databases. In MS Office Access 2003, we can directly and easily view information on dependencies between database objects. The capability to view a list of a database's objects, particularly within the context of how those objects relate to each other, can help us to maintain a database over time. The capability is perhaps most useful in helping us to avoid errors that emerge with the uninformed removal of record sources or other databases objects, but numerous other potential uses exist, as well.

---

Database design basics
A properly designed database provides you with access to up-to-date, accurate information. Because a correct design is essential to achieving your goals in working with a database, investing the time required to learn the principles of good design makes sense. In the end, you are much more likely to end up with a database that meets your needs and can easily accommodate change. This article provides guidelines for planning a database. You will learn how to decide what information you need, how to divide that information into the appropriate tables and columns, and how those tables relate to each other. You should read this article before you create your first database.

---

Using Macros to Automate Data Entry
As you will have seen in the previous topic, we can use Macros to Require Data Entry and to inform users of any errors made in the data entry routine. We can also use Microsoft Access Macros to speed up the process of data entry. We can use a macro to remove the need for a user to enter the same data over and over for each record, we can use a macro to automate this process. A Microsoft Access macro is a database object that allows us to automate repetative tasks without having to write complex programming routines. In Access, these tasks that the macro performs are know as macro actions.

---

Restrict the Data in a Field
In a Microsoft Access 2003 table or database, you can restrict the data in a field to certain values. Controlling the types of data that someone can enter into a field prevents bookkeeping errors and incorrect database entries. Take these steps to restrict data in a field to certain values in Access 2003.

---

Access Help, Tips and Tutorials
If you are finding that you are getting unexplained errors with your applications as you develop or you have noticed that it seems to be running slower then it may be time to use the rarely mentioned decompile option on your application. When you compile VBA code various pieces of old code often get left behind. Decompile is a relatively unknown Access feature than can help drastically reduce the size of front end databases and also reduce bloating and decrease start-up times is the /decompile option. The /decompile command line option cleans out these bits of code and allows for a fresh start.

---

Designing Forms for Efficient and Accurate Data Entry
This excerpt from "Microsoft Office Access 2007 Forms, Reports, and Queries" introduces several techniques that serve to either make data entry less of a chore, or to reduce or eliminate data entry errors (or both).

---

How to Limit Data in a Microsoft Access Field to Certain Values
Limiting data in a field prevents entry errors. For instance, suppose you are selling towels in three colors - red, blue, and yellow. Limiting the data values to "red," "blue" or "yellow" will prevent people from mistakenly ordering pink towels. These instructions apply to MS Access 97.

---

Connecting to a Microsoft Access database with ASP.NET
In this article, I shall discuss different methodologies for connecting to a Microsoft Access database using ASP.NET without any errors. TOC: Connecting to a Microsoft Access database with ASP.NET; Connecting to a Microsoft Access database directly using ASP.NET; Manipulating a Microsoft Access database directly using ASP.NET; Connecting to a Microsoft Access database through ODBC using ASP.NET; Manipulating a Microsoft Access database through ODBC using ASP.NET; Connecting to a password protected Microsoft Access database directly using ASP.NET.

---

Troubleshoot importing and linking
The Get External Data command is disabled. I get an error message saying the wizard is not installed or is disabled. I started the wizard, but it disappeared. What do I do? I don't see Microsoft Excel listed in the Files of Type box. I get an error saying Access cannot import data from my file. The option to append data to an existing table is disabled when importing a spreadsheet. I am importing data from a spreadsheet, but I cannot specify field options for any field other than the first.I get errors when I import or link to a spreadsheet or text file, or the data doesn't get imported (or linked to) properly. Importing a text file is taking a long time. I get errors when I append data to a spreadsheet or text file. Some field values appear truncated in a linked table. I get the Numeric field overflow message when I query a linked table. I can't open or update a linked Microsoft Visual FoxPro or dBASE table. I can't open or update a linked Paradox table. I can't open or update a linked SQL table. I get a key violation message. I imported a file and Access says it created a Tablename$_ImportErrors table. I'm trying to import or link data from my security-enabled database. File extensions don't show up beside the file names in the Files Of Type dialog box. I get an "ODBC - connection to datasourcename failed" message when I open a table or form in a converted database.

---

Troubleshoot sorting
This topic explains how to troubleshoot sorting in Access. Problems: I cannot apply a sort order: The sort buttons are disabled, The field or column on which I want to sort is not visible, The OrderbyOn property is missing in the property sheet, Cannot find the DefaultSort property in the property sheet, I can't find the language I want in the New database sort order box. OR I applied a sort order, but: Some records are not getting sorted properly, Records are not displayed in the right order, Sorting seems to slow down the database, I get the message "The DefaultSort property is invalid.", Numbers are sorted based on the first character instead of on their numeric values, I created a report based on a query, but the query sort order is not reflected in the report, Sort order is not being saved, Sort order is not being applied, I don't want Access to apply the sort order automatically, The sort order is lost during an export operation, Access isn't using the sort order I specified in a linked table, I receive replication errors after I change the language sort order.

---

Troubleshoot spelling
Learn: The spelling checker didn't find specific errors. The spelling checker questions text that is correct. I can't select language-specific spelling options. I can't add a word to the custom dictionary. I added words to a custom dictionary, but the spelling checker still questions them. Microsoft Access prompts me to create a new custom dictionary when I try to add a word.

---

MS Access for the Business Environment: Analyze and Report from the Windows Event Log, Part I
The objective of this two-part article is to discuss the creation and loading of an MS Access database with Event Log data. Most of us have used the Event Viewer to view and manipulate the sometimes-critical messages that accumulate in the Event Log, regarding many aspects of our Windows 2000 network and machine operations. While the Viewer is suitable for online follow-up of a specific event, as well as a great starting point for troubleshooting of errors, system messages that take us unawares, and any number of other daily as well as infrequent, occurrences, it doesn't lend itself to easy analysis, or to the collection and reporting of statistics. In this article, we will examine one approach for moving the potentially valuable storehouse of data in the Event Log to a data source from which we can report upon it or perform in-depth analysis from a number of dimensions. As in all the articles within our series, our intent is to examine ways that we can use MS Access to provide sophisticated results to organizational information consumers. While we will be diverting from our typical focus of financial information, it is easy to understand how operational statistics can be useful to the organization as well.

---

Elements of Run-Time Error Handling
When you're programming an application, you need to consider what happens when an error occurs. An error can occur in your application for one of two of reasons. First, some condition at the time the application is running makes otherwise valid code fail. For example, if your code attempts to open a table that the user has deleted, an error occurs. Second, your code may contain improper logic that prevents it from doing what you intended. For example, an error occurs if your code attempts to divide a value by zero.

---

Microsoft Access Viewlets
Using the Blank Database Wizard in Access. Change Properties for Table Fields in Access. Using the Selection Filter in Access. Using the Report Wizard in Access. Creating an Input Mask in Access. Deleting and adding a Table Field in Access. Creating a Relationship Between Two Tables in Access. Sorting a Table Using Multiple Columns in Access. Freezing and Unfreezing Columns in Access. Sorting a Table in Access. Creating a Simple Query in Access. Navigating Through Table Data in Access. Hiding and Unhiding Columns in Access. Building and Applying a Sort Filter in Access. Searching For and Replacing Text in an Access Table. Automatically adding Date and Time to Access form

---