Runtime error '424' when running SQL

Asked

Viewed 264 times

0

I’m trying to send data from excel to access through a macro. I edited on top of a ready and it seemed to me that I was doing the same thing, but it gives "Error at runtime 424 - the object is mandatory" when it arrives in the "Execute SQL" part. I can’t find my mistake.

Sub Insere()
Dim gConexao As ADODB.Connection
a2019 = Cells(2, 2).Value
a2020 = Cells(2, 3).Value
a2021 = Cells(2, 4).Value
a2022 = Cells(2, 5).Value
a2023 = Cells(2, 6).Value
a2024 = Cells(2, 7).Value
lsInserir a2019, a2020, a2021, a2022, a2023, a2024
End Sub


Sub lsInserir(a2019, a2020, a2021, _
                      a2022, a2023, a2024)
    Dim lSQL As String

    lsConectar
 lSQL = "INSERT INTO Tabela1 &(a2019, a2020, a2021, a2022, a2023, a2024)" & _
    "VALUES (" & a2019 & "," & a2020 & "," & a2021 & "," & a2022 & "," & a2023 & "," & a2024 & ")"

    gConexao.Execute lSQL
    lsDesconectar
End Sub

Sub lsConectar()
    Dim strConexao As String
    Set gConexao = New ADODB.Connection

    strConexao = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\2005866\Documents\testeleituradados.accdb;Persist Security Info=False"

    gConexao.Open strConexao
End Sub

Sub lsDesconectar()
    If Not gConexao Is Nothing Then
        gConexao.Close
        Set gConexao = Nothing
    End If
End Sub
  • Are you sure the quotes from the lSQL string are correct? If they are then there is at least one & wrong. Somehow it seems strange to me that the same cell contains both the name of the field in the database and its value.

  • is that the name of the field there in access calls "a2019","a2020", etc and gave the same name to the variable that takes the value in the cell... is not the same thing.

  • So I guess there’s a & left before the field name list.

  • About the quotation marks... I’ve tried without quotation marks, with " ' ' ", with just simple... everything. Gives the same error. About &...which is wrong?

  • I took this & before (I hadn’t even seen it there but it actually doesn’t feel right) and still gives the same error...

  • for some reason I took the functions that were calling each other and put everything together in sequence and worked...will understand. Thank you :)

  • At the beginning of your code, switch to: Dim gConexado As New ADODB.Connection and test. If this does not work, put a break point in the line of . Run SQL to see if the statement is being mounted correctly (use debug.print lSQL).

Show 2 more comments
No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.