I have just started programming with VBA and probably this is a silly question, but I’ve been looking for a solution for too much time and I didn’t find anything yet. I hope you can help me with that.

I keep getting the Run-time error ‘-2147217900 (80040e14)’ every time I try to insert the text of an cell present in my Excel worksheet into a specific table of my access database.

The method is working with other kind of strings, for example the one of the TextBox aor the ComboBox that I have in my UserForm. However, when I try to insert the text from a specific cell I get the syntax error mentioned.

The error is generated within the insert_ECS_ITM statement. Specifically, by the lines which are in bold.

I have tried every possible combination of characters:

item
+item+
‘”+item+”‘
‘”&item&”‘
“item”
‘item’
&item&

I don’t know how to fix it… I kindly ask for your help.

Here there is my code:

Private Sub MI_ITM_AddItem_CommandButton_Click()

Dim cnt As ADODB.Connection
Dim db_path As String
Dim db_str As String

db_path = “X:SPET_DB.accdb”

Set cnt = New ADODB.Connection

db_str = “provider=Microsoft.ACE.OLEDB.12.0; data source=” & db_path

cnt.Open (db_str)

‘If (IsNull(DLookup(“Drawing”, “02_DRAWING”, “Drawing='” & MI_DWG_Drawing_TextBox1 & “‘”))) Then

‘insert_ITM1 = “insert into 05_REQUESTED_ITEM(” _
& “SPET_ID,” _
& “Description,” _
& “Manifacturer,” _
& “Application)” _
& “values(” _
& “‘” & MI_ID_CaseID_CUSTOMER_ComboBox & “‘ & ‘-‘ & ‘” & MI_ID_CaseID_YEAR_TextBox & “‘ & ‘-‘ & ‘” & MI_ID_CaseID_SEQUENTIAL_TextBox & “‘,” _
& “‘” & MI_ITM_Description_ComboBox & “‘,” _
& “‘” & MI_ITM_Manifacturer_ComboBox & “‘,” _
& “‘” & MI_ITM_Application_ComboBox & “‘)”

insert_ITM1 = “insert into 05_REQUESTED_ITEM(” _
& “SPET_ID,” _
& “Description,” _
& “Manifacturer,” _
& “Quantity,” _
& “Application)” _
& “values(” _
& “‘” & MI_ID_CaseID_CUSTOMER_ComboBox & “‘ & ‘-‘ & ‘” & MI_ID_CaseID_YEAR_TextBox & “‘ & ‘-‘ & ‘” & MI_ID_CaseID_SEQUENTIAL_TextBox & “‘,” _
& “‘” & MI_ITM_Description_ComboBox & “‘,” _
& “‘” & MI_ITM_Manifacturer_ComboBox & “‘,” _
& “‘” & MI_ITM_Quantity_TextBox.Value & “‘,” _
& “‘” & MI_ITM_Application_ComboBox & “‘)”

cnt.Execute (insert_ITM1)

insert_ITM2 = “insert into 06_ITEM_DETAILS(” _
& “SPET_ID,” _
& “Item,” _
& “Material_RFQ,” _
& “Dimensions,” _
& “Component, Part)” _
& “values(” _
& “‘” & MI_ID_CaseID_CUSTOMER_ComboBox & “‘ & ‘-‘ & ‘” & MI_ID_CaseID_YEAR_TextBox & “‘ & ‘-‘ & ‘” & MI_ID_CaseID_SEQUENTIAL_TextBox & “‘,” _
& “‘” & MI_ITM_Description_ComboBox & “‘,” _
& “‘” & MI_ITM_Material_ComboBox & “‘,” _
& “‘” & MI_ITM_Dimensions_TextBox & “‘,” _
& “” & MI_ITM_Category_COMPONENT_CheckBox.Value & “,” & MI_ITM_Category_PART_CheckBox.Value & “)”

cnt.Execute (insert_ITM2)

Dim item As String

For x = 0 To MI_ITM_SelectECS_ListBox.ListCount – 1

If MI_ITM_SelectECS_ListBox.Selected(x) = True Then
    
    item = ThisWorkbook.Worksheets("DWG_ECS").Cells(x + 1, 2).Text
            
    insert_ECS_ITM = "insert into 00_ECS_ITM(" _
    & "ECS," _
    & "Item," _
    & "values(" _

** & ” item ,” _**
& “‘” & MI_ITM_Description_ComboBox & “‘)”

** cnt.Execute (insert_ECS_ITM)**

End If

Next x

‘End If

MsgBox “ITEM INSERTED”

Set cnt = Nothing

End Sub