AboutPricingCommunityTeamsStart 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
Microsoft ExcelMicrosoft Access
Avatar of undefined
Last Comment
Gustav Brock
8/22/2022 - Mon
Shaun Kline
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
Gustav Brock
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Nick67
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
ca1358
ASKER
I am still getting error now Run-time error '-2147217900 (80040e14)':

 Number of query values and destination fields are not the same.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Nick67
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.
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
ca1358
ASKER
I change the code but now the reads Invalid SQL statement; expected 'DELETE', 'INSERT', 'Procedure', 'SELECT', or 'UPDATE'
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Nick67
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 Brock
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
Nick67
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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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)
Gustav Brock
You should be able to see that a closing parenthesis and a set of parenthesis are missing.

Study the syntax of my answer.

/gustav
ca1358
ASKER
Dont understand your comment
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Gustav Brock
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
ca1358
ASKER
Eorr now

Syntax errpt in INSERT INTO statement
Gustav Brock
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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent