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

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
ca1358Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
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.
Gustav BrockCIOCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nick67Commented:
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
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

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

 Number of query values and destination fields are not the same.
Nick67Commented:
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.
ca1358Author 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
ca1358Author Commented:
I change the code but now the reads Invalid SQL statement; expected 'DELETE', 'INSERT', 'Procedure', 'SELECT', or 'UPDATE'
Nick67Commented:
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.
Gustav BrockCIOCommented:
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
Nick67Commented:
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

ca1358Author 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)
Gustav BrockCIOCommented:
You should be able to see that a closing parenthesis and a set of parenthesis are missing.

Study the syntax of my answer.

/gustav
ca1358Author Commented:
Dont understand your comment
Gustav BrockCIOCommented:
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
ca1358Author Commented:
Eorr now

Syntax errpt in INSERT INTO statement
Gustav BrockCIOCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.