When you query a table that is linked to a Microsoft Excel
spreadsheet, you may receive the following error message:
Access assigns a data type for each field of the Excel
spreadsheet. This assignment is based on the data that is contained in the
first eight rows. For example, if a field has a
Number data type that is in the first eight rows and then has text
values in some of the remaining rows, Access assigns the
Number data type to the link table field. This causes Access to fail to
link the records that have text data values. When you query this field,
Microsoft Jet Database Engine encounters text where a number is expected. The
query fails with the error message in the "Symptoms" section.
To work around this problem, you must make sure that the
data values in each field of the source Excel spreadsheet are exactly the same
data type. Or, if the fields of the Excel spreadsheet contains data values of a
mixed data type, then format the field as
Text. Use the following code to reenter the values in the cells. Then,
link the Excel spreadsheet to Access. To do this, follow these steps:
Note Before you start these steps, you must backup your Excel
spreadsheet.
- Open the source spreadsheet in Excel.
- In the spreadsheet, identify the fields that have data
values of mixed data types.
- Select the identified columns and then format the cells in
the spreadsheet as Text.
- Create a macro in Excel that contains the following
procedure:
Sub Addspace()
Dim cell As Object
For Each cell In Selection
cell.Value = " " & cell.Value
cell.Value = Right(cell.Value, Len(cell.Value) - 1)
Next
End Sub
- Select the fields in the spreadsheet that contain data
values of mixed data types. Run the macro that you created in step 4.
Note If an error occurs when you run the macro on the problem cells,
ignore the error. - Save the spreadsheet.
- Open your database in Access.
- From your database, link to the source spreadsheet in
Excel.
When you import, do not link an Excel spreadsheet. This is
because the
Text data type has a priority in the import algorithm. For example, if
the predominant data type that is based on a sampling of data is numeric, but
there is at least one text value in that sample, Access imports the whole field
as
Text.
For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
282263Â
(http://kbalertz.com/Feedback.aspx?kbNumber=282263/
)
ACC2002: Ignored MaxScanRows Setting May Cause Improper Data Types in
Linked Tables
208414Â
(http://kbalertz.com/Feedback.aspx?kbNumber=208414/EN-US/
)
ACC2000: #Num Appears in Linked Microsoft Excel Spreadsheet
For more information about how to create a
macro in Microsoft Excel, click
Microsoft Excel Help on the
Help menu, type
Create a macro in the
Office Assistant or the Answer Wizard, and then click
Search
to view the topic.
For more information about how to run a macro in Microsoft
Excel, click
Microsoft Excel Help on the
Help
menu, type
Run a macro in the Office Assistant or the
Answer Wizard, and then click
Search to view the topic.