Large Excel xls Import to Access

[ralmanza]

I am trying to import a 14 tab Excel xls document into Access and it is taking over two hours. Each tab has the same type data and only the first tab has headers, there are 15 columns and the Excel document is 253kb.

I need help to get the time to import dramatically reduced. Any suggestions?

I have tried a macro and also vba.

Thanks for any help you can give me,

Ralmanza

 

[bobsan42]

How do you import the file? VBA or Access import wizard?
Which operation is taking too long?
How is your database structured? Is it split? Do you have a separate back-end and where is it - local HDD or network? If network - how fast is the connection?

Give some more info.

 

[ralmanza]

Thanks for replying, in answer to your questions:
I have tried vba and macro using import excel workbook.
The actual import portion of the 14 tabs is taking too long. I isolated this portion into one macro and it ran for 2 - 2 1/2 hours.
In this database I only have the queries and tables needed for this one report. It is not split and I am pulling the data through our company network.
Sorry, I have not idea how fast our connection is here at work. I run a similar report with nine tabs and it takes about 10-15 minutes.

I hope this helps.

 

[bobsan42]

have you tried to import manually with the import wizard? sheet by sheet to see if there is a particular one that is slower than the others?
can you post the macro that imports the data?
is the data in the sheets static or is coming in from somewhere else?

 

[ralmanza]

I will try to import each sheet manually. I did put the report and the database on my C drive and it cut almost an hour. But this is still too long.

 

[Joe4]

Just curious, have you tried just linking the Excel tabs to Access tables instead of actually importing them?
It is often not necessary to physically import the Excel files into Access in order to work with them (espcially if you are not planning on updating the underlying data in the tables).

 

[ralmanza]

I like this plan. I will work on that. Doing one sheet at time did not work.