Thread: Excel -> Access Importing Connection Error - Invalid SQL statement; expected 'DEL....

Hello-

I've recently upgraded to Excel 2010 and my firm has decided not to purchase Access with the 2010 Office suite. Because of this I need a new connection method to an Access database (from xls) that doesn't require the user to have Access 2010 on their machine. I'm now trying to tweak my access importing function to use an ADODB connection and I'm getting the error above. If anyone has any sample code that achieves an import of an xls spreadsheet into an Access table from xls 2010 it would be my Christmas miracle. Here's the function I'm working with. I've taken numerous stabs in the dark so I apologize if it's not even close to what I ultimately need to make this happen.

I have one line of code commented out below, when I switch back and forth I do get a different error. Not sure which one is closer to achieving the import.

Public Function ImportXlsFileNew(ByVal dbFilePath As String, ByVal dbFileName As String, ByVal dbTableName As String, ByVal xlsFilePath As String, ByVal xlsFileName As String)
    Dim cnt As ADODB.Connection
    Dim stSQL As String, stCon As String
    Dim stSQL2 As String
     
    'stCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFilePath & dbFileName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
     stCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFilePath & dbFileName & ";"
    
 ' SQL code for Insert to Access    
    stSQL = "'INSERT INTO' '" & dbTableName & "' " & """" & "SELECT * FROM [UploadData$] IN " & xlsFilePath & "\" & xlsFileName & """"
        
      'set connection  variable
    Set cnt = New ADODB.Connection
     'open connection to Access db and run the SQL
    With cnt
        .Provider = "Microsoft.ACE.OLEDB.12.0;Data"
        .Open stCon
        .CursorLocation = adUseClient
        .Execute (stSQL)
    End With
     'close connection
    cnt.Close
     
     'release object from memory
    Set cnt = Nothing
End Function

Any assistance is appreciated.

Thanks,

At first view, the issue appears to be the single quotes around the INSERT INTO statement - they do no belong there. You also do not need to specify the provider twice, although I do not believe that will actually cause a problem.

Here is a very simplistic snippet of code to load a workbook into a database

Sub AddDataFromWorkbookToAccess()
' Sample demonstrating how to update data in a closed 2007 format workbook
   Dim cn                As Object
   Set cn = CreateObject("ADODB.Connection")
   With cn
      .Provider = "Microsoft.ACE.OLEDB.12.0"
      .ConnectionString = "Data Source=C:\Development\TestDB.accdb"
      .Open
      .Execute "INSERT INTO tblTest (C1,C2,C3) SELECT * FROM [Excel 12.0;HDR=YES;Database=C:\Development\MyImport.xlsm].[Sheet1$]"
      .Close
   End With

   Set cn = Nothing
End Sub