excel to access import problems

[chr1stoper1]

Hi Guys

I have an issue that has arose in my database.My database has three main tables file 1 file 2 and file 1&2 joint.
The data for these tables is uploaded daily by two macro enabled excel sheets. both macros are exactly the same but for some reason the file 2 upload will not upload anything but numbers although it was originally formatted as text. It does not happen in file one .Ive checked the formatting in the tables and for some reason table two field keeps changing??? anyone have any ideas?

[JoeM]

When importing an Excel file into Access, Access tried to figure out (or more accurately "guess") the format of each field. I believe it does so by looking at the first ten records it is importing. Sometimes it guesses wrong, and the whole import fails. This can be a source of great frustration.

There are various options you can try:

- Try making sure that all possibilities for a field can appear in the first 10 rows. For example, if you have a column of US and Canadian zip codes, since Canadian zip codes have letters and US ones do not, make sure to include a Canadian in the first 10 rows so that Access knows this field cannot be formatted as numbers.

- Try linking the Excel tables to an Access table. You can then use Update Queries to write the data from these linked tables to your final tables, making sure to do any necessary format conversion.

- Export your Excel data to some sort of Text file (CSV, tab-delimited, space-delimited, etc), then import this Text file into Access where YOU have control over the format of every imported field.

I often end up using the last option myself.

[chr1stoper1]

Hi thanks for the reply what you say makes sense.... I already have the excel sheets linked to access and run append queries...... think I might give the last idea a shot