ACC1x: Importing Numeric Column Names in Spreadsheets

• Microsoft Access versions 1.0, 1.1
• Microsoft Excel for Windows, versions 4.0, 4.0a

SYMPTOMS

If you import a Microsoft Excel spreadsheet using the First Row Contains Field Names option, and the spreadsheet's first row contains numeric values, Microsoft Access will substitute sequential values starting with 1 for the values in the first row of the spreadsheet.

RESOLUTION

Before you import the spreadsheet, format the column names (the values in the first row) as text by typing a single quotation mark (') at the beginning of each value. Or, after you import the spreadsheet to a Microsoft Access table, switch the table to Design view and change the field names.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access versions 1.0 and 1.1. This problem no longer occurs in Microsoft Access version 2.0.

MORE INFORMATION

Numeric column names in imported spreadsheets are ignored, regardless of the spreadsheet format. Microsoft Access will replace any numeric column names with column numbers in the destination table.

Steps to Reproduce Problem

1. In Microsoft Excel, create a new spreadsheet with the following entries, and then save the spreadsheet as TEST.XLS:
   

   
                  Col 1  Col 2  Col 3
                  --------------------
          Row 1: |5000 | 6000 | 7000 |
                  -------------------
          Row 2: | 201 |  202 |  203 |
                  -------------------
          Row 3: | 301 |  302 |  303 |
                  -------------------- 




2. Start Microsoft Access and open any database.



3. From the File menu, choose Import.



4. In the Data Source box, select Microsoft Excel, and then choose OK.



5. In the File Name box, select TEST.XLS. Choose Import.



6. In the Import Spreadsheet Options dialog box, select the First Row Contains Field Names check box. Choose OK. The spreadsheet will be imported.



7. In the message box stating that 2 records were processed, choose OK.



8. Choose Close.



9. Open the new Test table in Datasheet view. Note that the field headings are 1, 2, and 3, rather than 5000, 6000, and 7000.

Keywords : kbinterop IsmExl4
Version : WINDOWS:1.0,1.1,4.0,4.0a
Platform : WINDOWS
Issue type : kbbug