Table import from Excel - only 52 columns??

[paul_mcc]

I'm having problems importing a spreadsheet from Excel for a client.

They are using Access 2003 and Excel 2007, 3.5GB RAM
It works perfectly on my machine - Excel 2010 and Access 2007-2010, 4 GB RAM.

The file is imported in Excel 97-2003 format via a macro using the transferspreadsheet function.

It returns error 3274: "external table is not in the expected format."

There are 1488 rows and 71 columns in the spreadsheet and resulting table - in future additional columns may need to be added representing new critical data.

The interesting thing was that it worked fine on my machine. Then as a trial and error process I cut the file down to 26 columns and it worked fine. 52 columns also imported. But it gave up when there were 71 columns.

if 52 columns * 1488 rows =77,376 record worked, that's more than the magic number of 65,536. but it doesn't like 71*1488 = 105648 records. Is there a limit at 100,000, or some other number in between? I would have thought Access could hold/import much more than this. Am I missing something?

Tearing my hair out and doing 20-hr days on this so please, any help would be massively appreciated!

Thanks,
Paul

 

[paul_mcc]

To clarify:
(1) when I cut the file down to 26 and then 52 columns, I tried it on my client's computers and it worked fine, but then it fell over at 71 columns on their computers.

(2) The reason for using Excel 97-2003 format is that Access 2003 didn't seem happy importing an Excel 2007 spreadsheet.