Troubleshoot importing and linking

Here are the most typical reasons for encountering errors:

• In a field, there is data that can't be stored in the data type that Microsoft Access has assigned to that field. For example, you might have accidentally included a text value in a field that should only contain numbers or dates. Or perhaps a row in your text file or spreadsheet contains summary information or extraneous characters.

  If you think Microsoft Access assigned the correct data type for this field, edit your text file or spreadsheet to correct errors, and then import again. Otherwise, import again and specify the appropriate data type.

• Microsoft Access has assigned an incorrect data type to a field. Microsoft Access assigns the data type for each field based on the data in the first row it imports. For example, if a field that contains mostly text values has a number in the first row, then, in an Access database, Access assigns the Number data type. In an Access project, Access assigns a data type for numeric data. In both cases, Access cannot import the rest of the records.

  Import again and specify the appropriate data type when importing.

• One or more rows in the text file or spreadsheet contain more fields than the first row. For example, the second row in a file might have an extra field delimiter character that is followed by a value that Microsoft Access can't fit in the new table.

  Edit your text file or spreadsheet so that each row has the same number of fields, and import again.

• The date fields that are imported from a Microsoft Excel spreadsheet are off by four years. Microsoft Excel for Windows uses the 1900 Date System, in which serial numbers range from 1 to 65,380, which correspond to the dates January 1, 1900 through December 31, 2078. However, Microsoft Excel for the Macintosh uses the 1904 Date System, in which serial numbers range from 0 to 63,918, which correspond to the dates January 1, 1904 through December 31, 2078.

  Before importing the data, change the date system for the Microsoft Excel spreadsheet, or, after importing the data, perform an update query by using the expression [date field name] + 1462 to correct the dates.

If importing a text file takes an unexpectedly long time, it might be because many errors are occurring. To cancel importing, press CTRL+BREAK. To view the list of errors that were encountered, open the Import Errors table from the Database window.

Appended records must be compatible with the existing table's structure: Each field must have the same data type as the corresponding field in the destination table, and the fields must be in the same order (unless you're using the first row of the file as field names, in which case the field names must match).

If you suspect that the problem is with the data that you're appending, edit your spreadsheet or text file, and then import again. Alternatively, you might need to change the destination table itself. You can reorder the fields, change data types, run an update query (to reformat or recalculate the data, for example), or run a make-table query (to split one field into two fields or combine several fields into one field, for example).

Here are the most typical reasons for encountering errors:

• The field names in the spreadsheet or text file do not match the field names in the table that you are appending to.
• The data in a field is inappropriate for the data type of the destination field. For example, the destination field has the Date/Time data type in an Access database— or the datetime data type in an Access project— but the data contains a text value that Microsoft Access can't recognize as a date or time.
• The data in a numeric field is too large for the field size of the destination field. For example, the destination field has the FieldSize property set to Byte in an Access database— or a data type of tinyint in an Access project— but the data contains a value greater than 255.
• Records that you are importing contain duplicate values that cannot be stored in the primary key of the destination table or in any field in the table that has the Indexed property set to Yes (No Duplicates) in an Access database or that has the option Ignore Duplicate Key in an Access project.
• One or more rows in the text file or spreadsheet contain more fields than the destination table.
• The date fields that are imported from a Microsoft Excel spreadsheet are off by four years. Microsoft Excel for Windows uses the 1900 Date System, in which serial numbers range from 1 to 65,380, which correspond to the dates January 1, 1900 through December 31, 2078. However, Microsoft Excel for the Macintosh uses the 1904 Date System, in which serial numbers range from 0 to 63,918, which correspond to the dates January 1, 1904 through December 31, 2078.

  Before importing the data, change the date system for the Microsoft Excel spreadsheet or, after appending the data, perform an update query using the expression [date field name] + 1462 to correct the dates.

• If you link a .dbf file and associate an index (.idx, .cdx, .ndx, or .mdx) file, then Microsoft Access needs the index file to open the linked table. If you delete or move Microsoft Visual FoxPro or dBASE index files or the information (.inf) file Microsoft Access creates, you won't be able to open the linked table.
• If you link a Visual FoxPro or dBASE table with a memo field, then Microsoft Access needs the associated memo (.dbt) file to open the linked table. If you delete or move this file, you won't be able to open the linked table.
• When you use Microsoft Access to update the data in your .dbf file, it updates the index files to reflect your changes. If you use Visual FoxPro or dBASE to update data, you must update the associated indexes within Visual FoxPro or dBASE as well. Microsoft Access can't use a linked table if the indexes you specified aren't current.
• You can't update a Visual FoxPro or dBASE table if its data (.dbf) or index (.idx, .cdx, .ndx, or .mdx) files are set to read-only. To check this, use Microsoft Windows Explorer to display the folder where the files are located, right-click the data or index file, click Properties, and then click the General tab. If the Read-only check box is selected, clear it and then click OK.
• If your Visual FoxPro or dBASE tables are stored on a read-only drive or CD-ROM, Microsoft Access can't create an .inf file in the same folder as the .dbf files, and you won't be able to link the tables. To link a table on a read-only drive, you must specify in the Windows Registry the path to a read/write drive where you want Microsoft Access to create the .inf file.
• You may not have the latest dBASE ISAM drivers. Microsoft has created updated dBASE ISAM drivers for Microsoft Jet 4.0 that do not require the installation of the Borland Database Engine (BDE) to provide full read/write access to dBASE files. The default Jet 4.0-based dBase ISAM drivers that ship with Microsoft Data Access Component (MDAC) 2.1 and later, allow read-only access to dBASE files unless the BDE is installed. To obtain these ISAM drivers, you must contact Microsoft Technical Support, and request that they be sent to you by a Support Professional.

• If you link a Paradox table that has a primary key, then Microsoft Access needs the associated index (.px) file in order to open the linked table. If you link a Paradox table with a Memo field, then Microsoft Access needs the associated memo (.mb) file in order to open the linked table. If you delete or move these files, then you won't be able to open the linked table.
• If you link a Paradox table that doesn't have a primary key, then you won't be able to update data in the table using Microsoft Access. If you want the table to be updatable, define a primary key for the table in Paradox.
• OLE objects stored in a linked or imported Paradox table can't be opened from within Microsoft Access.
• You can't update a Paradox table if its data (.db) or index file (.px) is set to read-only. To check this, use Microsoft Windows Explorer to display the folder where the files are located, right-click the data or index file, click Properties, and then click the General tab. If the Read-only check box is selected, clear it and then click OK.
• You may not have the latest drivers. Microsoft has created updated Paradox ISAM drivers for Microsoft Jet 4.0 that do not require the installation of the Borland Database Engine (BDE) to provide full read/write access to Paradox files. The default Jet 4.0-based Paradox ISAM drivers that ship with Microsoft Data Access Component (MDAC) 2.1 and later allow read-only access to Paradox files unless the BDE is installed. To obtain these ISAM drivers, you must contact Microsoft Technical Support, and request that they be sent to you by a Support Professional.
• When you link a Paradox table that resides on a server and is shared by multiple users, you must set the ParadoxNetPath key in the Windows Registry to the path to the Paradox.net or Pdoxusrs.net file. Additionally, if you use Paradox version 4.x for sharing data in your workgroup, you must set the ParadoxNetStyle key to "4.x."
• If you are using Microsoft Access to link Paradox data on a network while users of Paradox are using the data, you must be sure to define all three ParadoxUserName, ParadoxNetPath, and ParadoxNetStyle settings in the registry. Use the ParadoxUserName setting to specify a unique user name for accessing the shared Paradox data. Use the ParadoxNetPath setting to specify the full path to the folder containing the Paradox.net file (for Paradox 3.x) or the Pdoxusrs.net file (for Paradox 4.x and 5.0). The ParadoxNetPath setting (including the drive letter) must be consistent for all users sharing a particular database. Use the ParadoxNetStyle setting to specify the network locking style to use when accessing Paradox data. Possible values are "3.x" for Paradox 3.x and "4.x" for Paradox 4.x and "5.0" for Paradox 5.0.

• Microsoft Access requires a unique index to update, delete, or insert data in a linked SQL table or view. When you link a Microsoft SQL Server updatable view, or an SQL database table that doesn't have a unique index, Microsoft Access asks you to select one or more fields to generate a unique index. However, Microsoft Access doesn't test to make sure that the fields you selected uniquely identify each record. If there are duplicates in the index Microsoft Access builds, you won't be able to update those records.

  If you think that the fields you selected did not create a unique index, delete the link to the table, link it again, and then select the appropriate field or fields to establish a unique index.

  Alternatively, you can create an index within Microsoft Access by using the Create Index statement to create a data-definition query. Again, be sure to select a field or combination of fields that will uniquely identify each record. If the index that is generated contains any duplicate values, all updates to the table will fail. To delete the index, use another data-definition query.

• If you link an SQL table that has its own unique index, and that index is defined for a field that is a floating-point number, the table might not be updatable. Because servers vary in how precisely they handle floating-point data, precision loss can sometimes occur. The actual difference is usually slight enough to be inconsequential, but if the data forms part of the SQL table's bookmark, affected records might appear to Microsoft Access to be deleted.
• If you encounter other errors while importing, linking, or using an SQL database table, there might be a problem with your account on the SQL database server or with the database itself. If you can't access an SQL database table, contact the SQL database administrator.

A key violation occurs if:

• You attempt to run a query that appends, deletes, or updates records in a way that violates the rules of referential integrity for related tables.
• Your query attempts to append or update records that contain primary key values that already exist in the destination table.

If you run such a query, it will not modify the records that cause the key violation.

To modify records in a way that will violate referential integrity, you can break the relationship between the affected tables or turn referential integrity off, and then modify the affected tables one at a time. However, the resulting data in the two tables may then conflict.

The Import Errors table contains descriptions of most error messages Microsoft Access encounters when trying to import your text file or spreadsheet in a Microsoft Access database. The table includes field names and row numbers that indicate which data has caused errors.

If Microsoft Access reports errors, open the Import Errors table and try to determine why Microsoft Access can't import all of the records. The following table lists possible import errors and describes their causes.

If you suspect that the problem is with your data, edit your text file or spreadsheet. If you're appending records to an existing table, you might need to change the table itself (reorder the fields or change property settings, for example). When you have solved the problem, import the data again.

If the database that you want to import or link has a database password, then you must provide the password before you can continue. Linking tables from such a database may have unforeseen consequences.

You have not selected the Show hidden files and folders option in Windows Explorer. See Windows Help for information on selecting the option.

The message "ODBC – connection to datasourcename failed" occurs when a table in your converted Microsoft Access version 1.x or 2.0 database is linked to an ODBC data source that uses a 16-bit driver manager and driver. Microsoft Access 2002 or later can open only linked ODBC data sources that use the 32-bit versions of the ODBC Driver Manager (Odbc32.dll) and the appropriate ODBC driver— for example, the 32-bit version of Microsoft SQL Server ODBC Driver (Sqlsrv32.dll).

To correct this problem, you must create a new, identically-named 32-bit data source name (DSN) for each ODBC data source that is linked to the original Access 1.x or 2.0 database.

1. In Microsoft Windows Control Panel, double-click Administrative Tools, and then double-click Data Sources (ODBC).
2. Create the new DSN by entering values identical to the original DSN. For example, for Microsoft SQL Server, you need to define at least Data Source Name and Server, but you may also need to define additional values such as the Database Name.

If you are uncertain what the original name of the DSN was, open the original database in the version of Microsoft Access that it was created with, open the linked table in Design view, and then display the Table Properties sheet. The table's Description property contains the definition for the ODBC connection string. The parameter following DSN= in the connection string is the name of the DSN.