Run-time error '-2147217900 (80040e14)': Syntax error in INSERT INTO statement(What wrong with Insert statement:)

ca1358
ca1358 used Ask the Experts™
on
I loaded the Access but the Excel document would not upload(Insert into Acess _Part.xltm) so I Print scrren it and uploaded it.

What wrong with Insert statement:

Code:
Sub Macro4()

   Dim appAccess As Object
   
Set appAccess = CreateObject("Access.Application")

 
 Call appAccess.OpenCurrentDatabase( _
 "\\DTCHYB-ILSP001\C_MTG_Groups\Mandatory\Analysts - Working Files\Carol\Test\Expert\TestAccess.accdb")
   appAccess.Visible = True

   appAccess.UserControl = True

    ' open table("ExcelToAccess")
 
appAccess.DoCmd.OpenTable "ExcelToAccess"

  Range("A5:H5").Select
    Selection.Copy
   
    CopyToAccessTable
   
End Sub


Sub CopyToAccessTable()

Dim sql As String, accFilePath As String
Dim cnn As ADODB.Connection

        accFilePath = ActiveWorkbook.Path & "\TestAccess.accdb"

    sql = "INSERT INTO  ExcelToAccess ( [Test], [TestA], [TestB],[TestC],[TestD],[TestE], ) IN '" & "'" _
                & " Values(" & Range("A5") & ", '" & Range("B5") & "', '" & Range("C5") & "', '" & Range("D5") & "', '" & Range("E5") & "', '" & Range("F5") & "', '" & Range("G5") & "', '" & Range("H5") & "'  )"
               
        Set cnn = New ADODB.Connection
        cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & accFilePath & ";Persist Security Info=False;"

        If Not (cnn Is Nothing) Then
            'Execute Sql
            cnn.Execute (sql)
            'Close
            cnn.Close
        End If
        Set cnn = Nothing

End Sub
TestAccess.accdb
Excel.docx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Shaun KlineLead Software Engineer

Commented:
You have more values (8) than fields (6). You have Test through TestE, but your values go from Range("A5") to Range("H5"). Your field list also ends in a comma.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
It should be something like:

sql = "INSERT INTO  ExcelToAccess ( [Test], [TestA], [TestB], [TestC], [TestD], [TestE], [TestF], [TestG], [TestH] ) " &  _
    " Values(" & Range("A5") & ", '" & Range("B5") & "', '" & Range("C5") & "', '" & Range("D5") & "', '" & Range("E5") & "', '" & Range("F5") & "', '" & Range("G5") & "', '" & Range("H5") & "'  )"
                 
/gustav
Most Valuable Expert 2014

Commented:
This is a horror:
sql = "INSERT INTO  ExcelToAccess ( [Test], [TestA], [TestB],[TestC],[TestD],[TestE], ) IN '" & "'" _
                 & " Values(" & Range("A5") & ", '" & Range("B5") & "', '" & Range("C5") & "', '" & Range("D5") & "', '" & Range("E5") & "', '" & Range("F5") & "', '" & Range("G5") & "', '" & Range("H5") & "'  )"


Dim MyVal as String
Dim MyValA as String
Dim MyValB as String
Dim MyValC as String
Dim MyValD as String
Dim MyValE as String

MyVal = Range("A5")
MyValA = Range("B5")
MyValB = Range("C5")
MyValC = Range("D5")
MyValD= Range("E5")
MyValE = Range("F5")

'note that since you have 6 columns specified, only 6 values can go in
'maybe this was the problem to start

sql = "INSERT INTO  ExcelToAccess ( [Test], [TestA], [TestB],[TestC],[TestD],[TestE], ) "
sql = "Values " & MyVal & "," & MyValA & "," & MyValB & "," & MyValC & "," & MyValD & "," & MyValE

Why you have IN in your insert statement is also a mystery
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I am still getting error now Run-time error '-2147217900 (80040e14)':

 Number of query values and destination fields are not the same.
Most Valuable Expert 2014

Commented:
Your [test] and then [testa] tripped /gustav up.
He now has nine fields and 8 values.

If you insist on horror

sql = "INSERT INTO  ExcelToAccess ( [Test], [TestA], [TestB], [TestC], [TestD], [TestE], [TestF], [TestG] ) " &  _
     " Values(" & Range("A5") & ", '" & Range("B5") & "', '" & Range("C5") & "', '" & Range("D5") & "', '" & Range("E5") & "', '" & Range("F5") & "', '" & Range("G5") & "', '" & Range("H5") & "'  )"

should do.

Author

Commented:
I change the code but now the reads Invalid SQL statement; expected 'DELETE', 'INSERT', 'Procedure', 'SELECT', or 'UPDATE'

at this line of code:    cnn.Execute (sql)

Code change is following:
ub CopyToAccessTable()

Dim sql As String, accFilePath As String
Dim cnn As ADODB.Connection
accFilePath = ("\\DTCHYB-ILSP001\C_MTG_Groups\Mandatory\Analysts - Working Files\Carol\Test\Expert\TestAccess.accdb")

     
sql = "INSERT INTO  ExcelToAccess ( [Test], [TestA], [TestB], [TestC], [TestD], [TestE], [TestF], [TestG], [TestH]"
 sql = "Values " & MyVal & "," & MyValA & "," & MyValB & "," & MyValC & "," & MyValD & "," & MyValE & "," & MyValF & "," & MyValG & "," & MyValH
               


        Set cnn = New ADODB.Connection
        cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & accFilePath & ";Persist Security Info=False;"

        If Not (cnn Is Nothing) Then
            'Execute Sql
            cnn.Execute (sql)
            'Close
            cnn.Close
        End If
        Set cnn = Nothing

Author

Commented:
I change the code but now the reads Invalid SQL statement; expected 'DELETE', 'INSERT', 'Procedure', 'SELECT', or 'UPDATE'
Most Valuable Expert 2014

Commented:
MsgBox sql
Check it for screwed up quotes.
It must contain quotes in pairs around each string value.
This is why I called your string a horror.
There's no good way to check for errors.
Build up the string element by element and you can debug the building of the string.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I change the code but now the reads Invalid SQL statement; expected 'DELETE', 'INSERT', 'Procedure', 'SELECT', or 'UPDATE'

Of course. You garbled the SQL beyond any valid syntax.

/gustav
Most Valuable Expert 2014

Commented:
You left out most of my code!
Let's fix it

Sub CopyToAccessTable()

 
Dim sql As String,
Dim accFilePath As String
Dim cnn As ADODB.Connection
Dim MyValA as String
Dim MyValB as String
Dim MyValC as String
Dim MyValD as String
Dim MyValE as String
Dim MyValF as String
Dim MyValG as String
Dim MyValH as String

 accFilePath = ("\\DTCHYB-ILSP001\C_MTG_Groups\Mandatory\Analysts - Working Files\Carol\Test\Expert\TestAccess.accdb")

 MyValA = Range("A5")
 MyValB = Range("B5")
 MyValC = Range("C5")
 MyValD = Range("D5")
 MyValE= Range("E5")
 MyValF = Range("F5")
 MyValG = Range("G5")
 MyValH = Range("H5")

       
 sql = "INSERT INTO  ExcelToAccess ([Test], [TestA], [TestB], [TestC], [TestD], [TestE], [TestF], [TestG]"
  sql = "Values " & MyValA & "," & MyValB & "," & MyValC & "," & MyValD & "," & MyValE & "," & MyValF & "," & MyValG & "," & MyValH
                 


         Set cnn = New ADODB.Connection
         cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & accFilePath & ";Persist Security Info=False;"

         If Not (cnn Is Nothing) Then
             'Execute Sql
             cnn.Execute (sql)
             'Close
             cnn.Close
         End If
         Set cnn = Nothing 
End Sub

Open in new window

Author

Commented:
Nick 67 ,   I copy your code in but still getting error  the reads Invalid SQL statement; expected 'DELETE', 'INSERT', 'Procedure', 'SELECT', or 'UPDATE

at this line        cnn.Execute (sql)
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You should be able to see that a closing parenthesis and a set of parenthesis are missing.

Study the syntax of my answer.

/gustav

Author

Commented:
Dont understand your comment
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Your sql must look similar to what I provided.

Insert a debug. command:

        If Not (cnn Is Nothing) Then
             'Execute Sql
             Debug.Print sql
             cnn.Execute (sql)
             'Close
             cnn.Close
        End If

and study the output. Then correct the code.

/gustav

Author

Commented:
Eorr now

Syntax errpt in INSERT INTO statement
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
The Debug command doesn't correct anything, it just prints in the immediate windows (Ctrl+G)  the sql that you will be about to execute.
Study the output, and then correct the code until the sql contains correct syntax.

/gustav

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