Import Excel data to an MS Access Table too slow!

[Pau905]

Hi Member,

I got the code below to import data from excel to an MS Access table. The code works but is very very slow. It takes over 1.5 minute to import the data. Number of rows in the excel data file is 120000 and number of colums are 110. How can I make this faster?

//Paul

Dim objAccess As Object
Set objAccess = CreateObject("Access.Application")
objAccess.Visible = False
objAccess.OpenCurrentDatabase C:\TEMP\TEST.accdb, Exclusive:=True
objAccess.DoCmd.TransferSpreadsheet acImport, 10, "TEST", Environ("temp") & "\TEST.xlsx", True

 

[Joe4]

I got the code below to import data from excel to an MS Access table. The code works but is very very slow. It takes over 1.5 minute to import the data. Number of rows in the excel data file is 120000 and number of colums are 110. How can I make this faster?

For the size of your data, that doesn't sound unreasonable.
However, have you considered simply linking your Excel data to an Access table instead of physically importing it?
That would bypass the need to import, though it may affect database performance.
If you do import it, be sure to apply indexes and primary keys to your table to maximize performance.

 

[Pau905]

Thanks Joe!

You are a excellent member! Very helpful!

 

[Joe4]

You are welcome.