Import Spreadsheet data into Access2007 using ADO

Hi,

I am trying to upload data from an Excel spreadsheet into an Access 2007 database file, using a comnination of ADO/SQL.

I have compiled the following code (please see below), which seems to work if my spreadsheet range is less than 65336 rows

However, if more than 65336 I receive the following error message:

Run-Time error '-2147217865 (80040e37)':
The Microsoft Office Access database engine could not find the object 'SapEp1DB$T3:AU70000'.
Make sure the object exists and that you spell its name and the path name correctly.


Any help/recommendations would be much appreciated.

Please Login or Register  to view this content.

Mod. edit: CODE tags please (per Forum Rules) - corrected on OP behalf (1st post)

hi Poolie,

Welcome to the Forum

Is this code being run from inside Access or Excel?

I don't know the answer & I haven't used this approach before so the below may or may not work but it's worth a try...
(I'm not sure if it will help with your apparent 65k row limitation or not.)

Please Login or Register  to view this content.

However, I have used code like the below from within Access to pull data from Excel...
(again I'm not sure about the impact of >65k rows)

Please Login or Register  to view this content.

Here are some links which may help out:
http://www.eggheadcafe.com/software/...7sql-2005.aspx (links to the next one...)
http://www.*****-blog.com/archives/2...ed-data-types/ (lots of reading)
http://bytes.com/topic/visual-basic/...-using-ado-vba (may be useful to check the syntax of your sql string)

hth
Rob