Export data from Excel to Access Database too slow

Hi,

This is my first post and I hope I can get some good feedback.

I am running a giant for loop with many excel calculations, where the finalized product will be a sheet to export, named ExportSheet. The range in this sheet is only changing with the amount of rows set by "LastRow", but the columns stays the same.

The export sheet is then set to be exported into an Access Table where it will be stored in the database. My problem is that this export code is taking very long time and I am now looking for ways to improve it. I hope this is within your boundaries even though its including Access and not only Excel. A normal ExportSheet would include a range of 35 x 2500 cells all filled with data. Why I want it to run faster is because its part of a really large for loop. The loop posted below is just an example to show you the logic behind it.

Dim acc As New Access.Application
acc.OpenCurrentDatabase "N:\blabla\MarketingCostDataBase.accdb"

'________________________________________________________
For x=1 to 10

acc.DoCmd.TransferSpreadsheet _
acImport, _
acSpreadsheetTypeExcel12Xml, _
ExportID, _
Application.ActiveWorkbook.FullName, _
True, _
"ExportSheet$A3:AH" & LastRow _

Next x
'________________________________________________________

acc.CloseCurrentDatabase
acc.Quit
Set acc = Nothing



So for my questions:
What way is the fastest to transfer data from Excel to Access?
Any improvements I can do to my VBA code to make the export process run faster?


Thank you in advance and let me know if I expressed myself too unclear.

Hi noboffinme,

Sorry I cannot post the complete file as its not working without the input data, which I cannot post here. However, the only code I have including the Access connection and export are mentioned in the post above.

I hope there is another way around it.

Yes I have, it takes a lot of time to transport the range from excel to access. All the calculations before are very quick.

Regarding the transport, I've also noted that the more data with in the range, the longer it takes (obviously), just that when I reach about the max size of 35 columns and 2500 rows, this exporting takes a long time. Any idea? Maybe it is the Access database that operates more slowly when it stores a lot of data?

edit: long as in maybe 20 mins for 2500 rows... Which is maybe a bit strange...

Before I stopped the calculation, It was 227 MB of data. All in the same Table.
Keep in mind when I started, the database was empty. I let it run over night and yet it still only managed to export the 226 MB of data...

Basically I am looping the calculation for every day the past 3 years, and the data per day will gradually become larger. The 226 MB would maybe be 20% of the total Data I want to collect, I just don't have the time for it to loop for a week...

FYI: When I export a heavy file into a blank database, the calculation goes very fast.