Thank you both for responding

I guess I wasn't clear enough in my question since you both think I am
looking for Access code.

I run Excel macros to clean up the data in the spreadsheet to get it ready
to import to Access. I do import into an existing table with the correct
field types. I perform this import on a daily basis and I can tell you from
experience that the import behaves differently if it doesn't find the defined
data type in the first 25 rows (and I read about this in Microsoft
documentation). Sorry, I don't remember what version of Excel we use at work
(I'm home now).

I think I can figure out how to loop through the rows and check the length
of the field I am interested in (thanks to Jim for suggesting the Len
function).

Thank you again for your responses,
Judy


"Barry" wrote:

Hi

If you change the MSAccess code so that it imports the data into an existing
table rather than a new one each time then it won't 'guess' what data type
the columns are but will use the field types already in the table.
So
1. Create a new table with the correct field types

2. Update your MSAccess code so that it
Imports the data into your new staging table
Copies the data from the staging table into the desired data table
Clears the data from the staging table so it's ready for next time.

For specific code suggest you post in the Access discussion group.

HTH
Barry





"Judy Ward" wrote:

I have code to import an Excel spreadsheet into an Access database. What I
am finding is that Access decides the data type for a field by looking in the
first 25 rows of the spreadsheet. If it sees more than 255 characters in a
cell it decides the column is "memo", and if it doesn't it decided the field
is "text" and truncates after 255 characters--this is not desired!

Right now I am manually looking at the rows to find one that has a large
amount of data, cutting and inserting it at the top of the spreadsheet. I
would really like to include this step in my code but have no idea how to go
about it.

Can anyone help?

Thank you,
Judy