My problem.

I have the task of creating a database where the user is going to have
the ability to simply upload spreadsheets from another database to
refresh the data. I am using the Transfer Spreadsheet Macro Function
to upload the data into a blank table where the fields in that table
have already been defined.

So essentially what I created were just several macro functions in
order to do this and its pretty much working minus a small hangup. One
of the problems is that the data that this person will be uploading is
not particularly the best data in the world. For example, we are using
an Identification number that is similar to a social security number
as the number has dashes but for example sake we can pretend its a
social security number.

The social security numbers being entered are being entered as

Example 1: XXX-XX-XXXX

And

Example 2: XXXXXXXXX

Now while I do have these set up as Text fields, Example 1 is giving
me data type conversion errors for these. Now if I were to delete all
the records with Example 2 then Example 1 does work correctly and I do
not get any conversion errors. It seems to me that what Access is
doing is reading some of the records and making an assumption as to
the format of those fields and when a record doesn't match the format
of the fields before it then it kicks it out. Because the data is not
MY data I cannot go back and fix the data so that it is correct. Is
there a command in Access that I can bypass this problem or fix this?
I have checked the fields of both the Access and Excel spreadsheets to
make sure that they are correctly formatted as Text Fields rather than
Numeric Fields but still no success.

I am using Access 97 BTW and Office 97.

Thanks so much for your help!

Dave:
Access finds a numeric in the first row and assumes the rest is
numeric. Access XP fixes this problem. I don't remember if Access
2000 does.
You can write code to read the Sheet and create the tables.
If you have any experience with VBA it is more tedious than difficult.
Send me an e-mail if you want to see a sample, but there is no point
if you haven't done any coding.

Good luck
Ira Solomon

On 1 Jun 2004 07:41:52 -0700, pariscarters (AT) yahoo (DOT) com (Dave Sanchez)
wrote:

Thanks for the response. No I have not done VBA which is unfortunate
but I will get around the problem somehow.

At any rate the user is moving to XP soon so that should hopefully
solve these problems.

Ira Solomon <isolomon (AT) solomonltd (DOT) com> wrote