Import more than 65535 rows of data from excel to access?

LUIS FREUND
LUIS FREUND used Ask the Experts™
on
How do I Import data that has more than 65535 rows of data?  The follwoing code below is what I use to import data from excel to an access table , the problem I'm having is that I have 159000 rows of data and I'm being limited to import only 65535 rows in access.  Is there way I can import the entire data?

Sub ImportEA()
    Dim acc As New Access.Application
    acc.OpenCurrentDatabase "X:\Procurement\TTE\SAMS_v1.0.accdb"
    acc.CurrentDb.Execute "Delete * from tbl_EA"
    acc.DoCmd.TransferSpreadsheet _
            TransferType:=acImport, _
            SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _
            TableName:="tbl_EA", _
            Filename:=Application.ActiveWorkbook.FullName, _
            HasFieldNames:=True, _
            Range:="EA$B:S"
    acc.CloseCurrentDatabase
    acc.Quit
    Set acc = Nothing
End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
What version of Access/Excel is this?   I see your using .accdb, but the 65535 row limit was from the A2003 era and back.

Jim.
Hamed NasrRetired IT Professional

Commented:
Not sure of this limit.

One way is to link the excel sheet to access, and process the linked table within access.

Other way if you are happy with current import.
Create more sheets and link chunks of data from the 1st sheet.

Repeat import with different excel tables and access tables.
Then continue processing in access.

Author

Commented:
I'm using Access 2016 and Excel 2016
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Don' have that loaded at the moment, so I can't double check.   I thought that limit was removed after A2003.  Don't see anything wrong with the code....should be fine.

 Creating a table linked table and running a append query as Hamed suggested would definitely work....that was the work around for A2003 and back.

Jim.
Distinguished Expert 2017

Commented:
Are you sure that the range is correct?

What do you see if you link to the sheet?  If you see all the rows, you should be able to import them using transfer text.  I prefer to link to Excel and then use an append query to get the data into my permanent tables.  The query can select only the columns and rows I want and can also clean up certain types of data errors.  Simply importing a spreadsheet into a permanent table that already has data is dangerous at best.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial