TransferSpreadSheet doesn't work properly after conversion to 2002

[Guest]

I have an access database in 97. I have recently converted that to Access
2002 with Access 2002 file format. After doing that I am having problems with
the TransferSpreadsheet functionality which we use to Import data from an
Excel worksheet. At times it imports certain rows and leaves the remaining.
Some times it imports a few rows which are some thousands of rows below
ignoring the first ones. The same spreadsheet, I checked with Access 97 and
everything works fine and Imports the entire data. I am not able to find out
the cause for this strange behaviour.

Please Suggest.

Thanks,

Pradeep

 

[Guest]

You should look vary closely at your import specification to see if there any
changes

 

[Allen Browne]

Do you get any kind of error message regarding the rows that cannot be
imported? (You may need to turn SetWarnings on to get this message.)

Or does Access create a table with a name such as ImportErrors?

What data type are these fields that fail the import. If you create a table
with the same names, but all fields of type Text, does the import complete
successfully? (JET 4 is less capable of sorting out some of these issues
than JET 3.5 was.)

Are you using an import spec?

Presumably the file name is not the problem, since you are getting some
results. (Since A97 SR2, only registered file types work for the import.)

 

[Guest]

it gives a message saying that 0 rows could not be imported etc. It is a huge
message. I can understand when it creates a table with Import Errors. I dont
specify any Import Specification as such and just use the TransferSpreadSheet
statement and with the has column headers true because of which the first row
is Ignored.

I will try to check if Import Specification has got something to do with this.

Thanks,

Pradeep

 

[Allen Browne]

That "huge message" contains the answer to your question.

Read the message to understand exactly what is the reason the data cannot be
imported.

An import spec may or may not solve the problem, depending on what the
problem actually is.

 

[Guest]

Hello Allen,

I did go through the message and it did not give me any idea as to what was
the actual problem. It says:

"XYZ Database was unable to append all the data to the table

The contents of fields in 0 record(s) and 0 record(s) were lost dues to key
violations.

* If data was deleted, the data that you pasted or imported does not match
the field data types of the field size property in the destination table.

* If records were lost, either the records you pasted contain primary key
values that already exist in the destination table, or they violate
referential integrity rules for a relationship defined between tables.

Do you want to proceed any way? "

I checked the table design to see if there are any changes but it is the
same when compared to the access 97 one.

I imported the data into a new table(using get External data) and all the
rows were imported from the Excel file. I checked the design and it is the
same as the one that I am trying to bring data into.

Please suggest.

Thanks,

Pradeep

 

[Guest]

It Gives the same message if it try to import into an existing table.
Interestingly the data types for the new table and the existing table is
still the same (design is 100% identical in terms of field size, datatypes
etc).

Thanks,

Pradeep

 

[Allen Browne]

What I usually do is to import into a temp table that has all the fields of
type Text.

Then create an Update query that coerces the data into the correct data
type, and appends to the true table, once you have establshed that
everything is okay. See if that strategy works for you.

 

[Guest]

Hello Allen,

I was trying different things and was playing around with the
SpreadSheetType parameter. The parameter was set to 5 when access 97 with
which Access 97 was importing data perfectly. But Access 2002 was not
accepting it. To test the same I started giving more than 5 but it did not
accept that too. To try the other way, I used acSpreadsheetTypeExcel3 and it
started working perfectly. The entire data in the Excel sheet was imported
and it was faster too. I have been checking to see I will be having any
issues but everything seems to look good.

I appreciate your patience and you have been very helpful.

Thanks,

Pradeep

 

[Guest]

Allen,

I would appreciate if you can throw some light on the reason for this
strange behaviour.

Thanks,

Pradeep

 

[Ron2006]

Probably another case of a program being too smart for our own good?

The excel spreadsheet type is going to tell Access which internal
conversion routine to use. Not all spreadsheets are equal.

We had a situation where we were downloading a document and the web
page gave us the choice of text or spreadsheet. We said spreadsheet,
and told our access system to import the spreadsheet. Everything worked
fine for months.
Then they made some "enhancements" to the webpage. On the surface
everything looked the same but suddenly we could no longer import the
data. We tried all of the different import options but still nothing
would work. We ended up having to use automation to open the downloaded
"spreadsheet" with excell and then turn around and save it and then go
through with the import process. With that extra step everything worked
fine, AGAIN.

All this to simply say that Access 97 had a different internal
conversion routine for spreadsheets than Access 2002 resulting in the
degraded import process until you, by changing the excell spreadsheet
type, hit upon an import conversion process that would handle the data.

 

[Allen Browne]

No idea.

Perhaps someone else can explain why the older Excel version would be
interpreted better than the more recent one.

What I can say is that Microsoft is aware that the import from Excel has
been less than ideal in the last couple of versions, and they are working
hard at making it better in A2007. While I have not tested it yet, I expect
that this work will be beneficial for us all.