Is THere a Way to Control How Access IMports Excel Spreadsheets ?

[Mike Thomas]

In Access 2000, we are importing Excel spreadsheets into a temporary table
for processing into a permanent table.

One of the columns is a part number which is typically, but not always, made
up of 8 digits. There are a few part numbers containing letters.

The problem is that when there are only numbers in each row of the part #
column, Access imports the column as text on one computer and as a number on
another. The makes for problems in running subsequent queries.

The code for the import is as follows:

DoCmd.TransferSpreadsheet acImport, , "XReceiptLine", strFileName, True

Is there a setting somewhere in Access which can be manipulated, or is there
a better way to assert control over the import?

Thanks
Mike Thomas

 

[Guest]

If the temporary table that you are importing into has this field type
defined as text then I don't understand what your problem is

 

[Mike Thomas]

Thanks for your reply Dennis,

The problem is that the temp table does not have any fields defined - they
are defined when the line

DoCmd.TransferSpreadsheet acImport, , "XReceiptLine", strFileName, True

is executed. In other words, its is Access which is defining the columns.
I am looking for a way to force Access to define them in a specific way. I
think I will have to programatically defing the temp table first.

Thanks
Mike Thomas

 

[Rob Oldfield]

You can control how the import works if you grabbing data from a text file
by using a specification name, but that option isn't available with
TransferSpreadsheet (though you could get people to save as csv and then use
transfertext). You can, however, import the data and THEN change the field
type. The example below assumes you know what the field is going to be
called.

Set db = CurrentDb
strsql = "Alter table Xtest alter column YourFieldName string;"
db.Execute strsql, dbFailOnError
Set db = Nothing

If you don't know what the field is called then you'd have to loop through
the fields and come up with some method of checking.

Thinks this only works in 2K onwards though.

 

[John Nurick]

Hi Mike,

1) If you know the field names and types in advance, create the Access
table first and then import the Excel data into it. The Access field
names must exactly match the column headings in Excel.

2) This article
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/
contains detailed information on how Access imports Excel sheets and how
to control what it does. See also
http://support.microsoft.com/?id=257819 .

3) An alternative approach would be to prefix all the part numbers in
Excel with an apostrophe, e.g.
'9876543
instead of
9876543
This forces Excel and Access to treat them as strings of characters and
not as numbers. The apostrophe is not displayed in Excel or imported
into Access. You can use VBA code running under Access to insert the
apostrophes into the Excel sheets if required.