TransferSpreadsheet very slow

[misterpox]

Im using an IBM Thinkpad T30 with 768MB RAM, a P4 1.8Ghz processor, a 7200RPM hard drive and running Win2Kpro with Access 2000.

I have an access module that uses the transferspreadsheet method to import sheets from an Excel workbook into an Access table. The sheets in each workbook are all nearly full, 63K+ records each, 29 fields in each.

When my module hits the transferspreadsheet execution, it's taking from 7-10 minutes to import just one spreadsheet from the workbook. Is this a normal length of time for the import to take? That seems like too long to me.

Is there another method besides the TransferSpreadsheet that would perform the import faster?

 

[Joe4]

Actually, in many cases it is unnecessary to import your data into Access at all, especially if it currently resides in Excel. You can simply link the data from Excel to Access without importing it. It will behave like an Access table for all query/form/report purposes.

To link the data, go into the Table object in Access, right click and select Link Tables.

 

[misterpox]

Thanks for the reply jmiskey. Linking to the spreadsheets isn't going to work for what I have though. There are multiple workbooks with multiple sheets in each and the formats of the fields in many of the sheets are hosed up, etc. I need to pull the data into tables before I can work with them.

I'm guessing there's really no faster/more efficient method of import besides transferspreadsheet that will allow speedier importing of this amount of data?

 

[Joe4]

I have no idea if it will be any faster, but you can try exporting the data from Excel to a text file, and then try importing the text file into Access.

That is definitely more work, but if saving time is your goal, it *may* be faster.