IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: Access Database Repair Service
An in-depth repair service for corrupt Microsoft Access files
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
IN THE SPOTLIGHT: Code Protector
Standard compilation to MDE/ACCDE format is flawed and reversible.
Provided by allenbrowne.com, June 2006
When you execute an append query, you may see a dialog giving reasons why some records were not inserted:
The dialog addresses four problem areas. This article explains each one, and how to solve them.
Access is having trouble putting the data into the fields because the field type does not match.
For example, if you have a Number or Date field, and the data you are importing contains:
-
Unknown
N/A
these are not valid numbers or dates, so produce a "type conversion" error.
In practice, Access has problems with any data that is is not in pure format. If the numbers have a Dollar sign at the front or contain commas or spaces between the thousands, the import can fail. Similarly, dates that are not in the standard US format are likely to fail.
Sometimes you can work around these issues by importing the data into a table that has all Text type fields, and then typecasting the fields, using Val(), CVDate(), or reconstructing the dates with Left(), Mid(), Right(), and DateSerial(). For more on typecasting, see Calculated fields misinterpreted.
The primary key must have a unique value. If you try to import a record where the primary key value is 9, and you already have a record where the primary key is 9, the import fails due to a violation of the primary key.
You can also violate a foreign key. For example, if you have a field that indicates which category a record belongs to, you will have created a table of categories, and established a relationship so only valid categories are allowed in this field. If the record you are importing has an invalid category, you have a violation of the foreign key.
You may have other unique indexes in your table as well. For example, an enrolment table might have a StudentID field (who is enrolled) and a ClassID field (what class they enrolled in), and you might create a unique index on the combination of StudentID + ClassID so you cannot have the same student enrolled twice in the one class. Now if the data you are importing has an existing combination of Student and Class, the import will fail with a violation of this unique index.
Lock violations occur when the data you are trying to import is already in use.
To solve this issue, make sure no other users have this database open, and close all other tables, queries, forms, and reports.
If the problem persists, Make sure you have set Default Record Locking to "No Locks" under:
Tools | Options | Advanced (Office Button | Access Options | Advanced in Access 2007.)
There are several places to look to solve for this one:
There is something in the Validation Rule of one of the fields, and the data you are trying to add does not meet this rule. The Validation Rule of each field is in the lower pane of table design window.
There is something in the Validation Rule of the table, and the data you are trying to add does not meet this rule. The Validation Rule of the table is in the Properties box.
The field has the Required property set to Yes, but the data has no value for that field.
The field has the Allow Zero Length property set to No (as it should), but the data contains zero-length-strings instead of nulls.
If none of these apply, double-check the key violations above.
If the problem data is not obvious, you might consider clicking Yes in the dialog shown at the beginning of this article. Access will create a table named Paste Errors or Import Errors or similar. Examining the specific records that failed should help to identify what went wrong.
After fixing the problems, you can then import the failed records, or restore a backup of the database and run the complete import again.
Home | Index of tips | Top |
Rate this article:
This is a cached tutorial, reproduced with permission.
iTech Masters | VAT: GB202994606 | Terms | Sitemap | Newsletter