Import Spreadsheet data into Access2007 using ADO/SQL

[Poolie]

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.

Option Explicit
 
Sub AccessImportNEW()
   
    Dim FirstR As Double
    Dim LastR As Double
    Dim FrameworkRange As Range
    Dim FrameworkAddress As String
   
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
   
    Dim SQL As String
   
    
    'MsgBox Application.Version
   
    'Create named range that is equal to the area you want to upload to access
    '-----------------------------------------------------------------------------
        LastR = Range("T1048576").End(xlUp).Row
       
        Select Case LastR
            Case 3
                MsgBox "NO DATA IN FRAMEWORK"
                Exit Sub
            Case Else
           
        End Select
       
        
        Set FrameworkRange = Sheets("Sapep1db").Range("T3:AU" & LastR & "")
        FrameworkAddress = FrameworkRange.Address(False, False)
        'MsgBox FrameworkAddress
       
        
    'Activate workbook
    '-----------------
        Workbooks("SapEp1DB1.xlsm").Activate
       
        'MsgBox ThisWorkbook.FullName
        Dim test As String
        'test = Sheets("sheet1").Range("A1:AB10").Address(False, False)
        'MsgBox test
       
        
    'Set SQL statement
    '-----------------
       
        
        SQL = "INSERT INTO sapdb SELECT * FROM [SapEp1DB$T3:AU70000] IN '" _
                    & ThisWorkbook.FullName & "' 'Excel 12.0 Macro;'"
       
     
    'connect to the Access database & execute SQL
    '--------------------------------------------
          
        
        Set cn = New ADODB.Connection
        With cn
            .Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data [INDENT][/INDENT]Source=\\Wfcntdfs1.wfc.chevrontexaco.net\share\CTGD\GST\Finance\Comptrollers\Trading\2012\Macros\Portfolio Analyzer\SapDB.accdb;" ' & _
                "Extended Properties=""Excel 12.0 XML;HDR=Yes"";"
            .CursorLocation = adUseClient
            .Execute (SQL)
        End With
 
    
        
    'Reset variables/ranges/connections
    '-----------------------------------
        Set FrameworkRange = Nothing
        Set rs = Nothing
        cn.Close
        Set cn = Nothing
       
           
        
 End Sub