Troubleshoot importing and linking

ShowThe Get External Data command is disabled.

This command is available only when a database is open and you have permissions to add data to the database.

If you don't want to bring data into an existing database, create a blank database. For more information about how to create a blank database, see the section "Create an empty database without using the Database Wizard" in the topic Create an Access database.

ShowI get an error message saying the wizard is not installed or is disabled.

This could be due to the fact that Access is running in sandbox mode but Microsoft Jet 4.0 SP8 or later is not installed on your computer. Jet 4.0 SP8 or later is required for Access to be fully functional when sandbox mode is enabled.

For more information about installing the Jet upgrade, see the Office Online article About Microsoft Jet 4.0 SP8 or later.

For more information about sandbox mode, see the topic About Microsoft Jet Expression Service sandbox mode.

ShowI started the wizard, but it disappeared. What do I do?

This behavior can occur when you do not have the correct version of Accwiz.dll registered on your computer.

For more information about registering the correct version of this file, see Unable to import Excel spreadsheets and HTML files.

ShowI don't see Microsoft Excel listed in the Files of Type box

If you do not see Microsoft Excel in the Files of type box, it is because the path to the required driver in the registry is invalid. For more information about how to correct this problem, see Could not find installable ISAM error message.

ShowI get an error saying Access cannot import data from my file

Make sure the file is not corrupt, or the data is not in a format that Access can read. Open the file in Excel and make sure the data is intact, and is in a format that is suitable for importing.

ShowThe option to append data to an existing table is disabled when importing a spreadsheet

When you try to import a Microsoft Excel spreadsheet that has undefined field names for the records that you are importing, the Import into an Existing Table option is not available in Access. For more information about how to troubleshoot this problem, see Access option to import into an existing table is not available for Excel spreadsheet.

ShowI am importing data from a spreadsheet, but I cannot specify field options for any field other than the first

This is a known issue. You need to obtain an update to correct this issue. Visit Access 2003 hotfix package for more information about how to obtain this update.

ShowI get errors when I import or link to a spreadsheet or text file, or the data doesn't get imported (or linked to) properly.

Here are the most typical reasons for encountering errors or not getting the desired results:

  • Graphical elements are missing.     Logos, charts, and pictures in the spreadsheet will not get imported or linked to.
  • Some fields are blank or display a string, such as #NUM.     Access encountered a value that is not compatible with the data type it assigned the field. By default, Access scans the first eight rows to guess the data type of the column. If Access encounters values beyond the eightth row that are not compatible with the chosen data type, it will simply ignore those values and not import (or link to) them.

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.

  • Columns that contain date values in the spreadsheet are imported into columns of type Number.     This could be because the spreadsheet was open and not shared at the time of the import operation. For more information about how to avoid this, see Date is displayed as a number when you import an open Excel workbook.
  • Long and medium dates appear as short dates.    Dates in long date format, such as January 1, 2000; or medium date format, such as 1-Jan-2000, lose formatting and are displayed as short dates, such as 01-01-2000.

For more information on how to fix the formatting of the field, see Wizard does not preserve Long or Medium date formats.

  • Access does not preserve the formatting of columns in the spreadsheet during importing or linking    . For more information about how to work around this issue, see Column format in a worksheet is lost or changed importing or linking from Excel.
  • 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 (update query: An action query (SQL statement) that changes a set of records according to criteria (search conditions) that you specify.) by using the expression [date field name] + 1462 to correct the dates.

ShowImporting a text file is taking a long time.

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 error log table from the Database window.

ShowI get errors when I append data to a spreadsheet or text file.

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 (update query: An action query (SQL statement) that changes a set of records according to criteria (search conditions) that you specify.) (to reformat or recalculate the data, for example), or run a make-table query (make-table query: A query (SQL statement) that creates a new table and then creates records (rows) in that table by copying records from an existing table or query result.) (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 (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) 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.

ShowSome field values appear truncated in a linked table

During linking, if Access encounters values in Excel that are longer than 255 characters, they are stored in a memo field that displays only the first 255 characters. The workaround for this limitation is to import the worksheet or named range, instead of linking to it.

ShowI get the Numeric field overflow message when I query a linked table.

You get this message due to a conflict between the data type of a field in the linked table and the type of data that is stored in that field.

For information on how to troubleshoot this message, see "Numeric Field Overflow" error message occurs when you query a table that is linked to Excel spreadsheet.

ShowI can't open or update a linked Microsoft Visual FoxPro or dBASE table.

  • 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.

ShowI can't open or update a linked Paradox table.

ShowI can't open or update a linked SQL table.

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 (data-definition query: An SQL-specific query that contains data definition language (DDL) statements. These statements allow you to create or alter objects in the database.). 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.

ShowI get a key violation message.

A key violation occurs if:

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.

ShowI imported a file and Access says it created a Tablename$_ImportErrors table.

This 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 error log 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.

Error Description
Field Truncation A value in the file is too large for the FieldSize property setting for this field.
Type Conversion Failure A value in the text file or spreadsheet is the wrong data type for this field.
Key Violation This record's primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) value is a duplicate — it already exists in the table.
Validation Rule Failure A value breaks the rule set by using the ValidationRule property for this field or for the table.
Null in Required Field A Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain a Null value.) value isn't allowed in this field because the Required property for the field is set to Yes.
Null value in AutoNumber field The data that you're importing contains a Null value that you attempted to append to an AutoNumber field.
Unparsable Record

A text value contains the text delimiter character (usually double quotation marks). Whenever a value contains the delimiter character, the character must be repeated twice in the text file; for example:

10 - 3 1/2"" disks/box

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.

ShowI'm trying to import or link data from my security-enabled database.

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.

ShowFile extensions don't show up beside the file names in the Files Of Type dialog box.

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

ShowI get an "ODBC – connection to datasourcename failed" message when I open a table or form in a converted database.

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 (ODBC data source: Data and the information needed to access that data from programs and databases that support the Open Database Connectivity (ODBC) protocol.) 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 (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.), 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.

 
 
Applies to:
Access 2003