Link to home
Start Free TrialLog in
Avatar of ca1358
ca1358

asked on

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
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

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.
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of ca1358
ca1358

ASKER

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

 Number of query values and destination fields are not the same.
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.
Avatar of ca1358

ASKER

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
Avatar of ca1358

ASKER

I change the code but now the reads Invalid SQL statement; expected 'DELETE', 'INSERT', 'Procedure', 'SELECT', or 'UPDATE'
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.
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
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

Avatar of ca1358

ASKER

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

Study the syntax of my answer.

/gustav
Avatar of ca1358

ASKER

Dont understand your comment
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
Avatar of ca1358

ASKER

Eorr now

Syntax errpt in INSERT INTO statement
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