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.
– anonimo
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.
– Natali
So I guess there’s a & left before the field name list.
– anonimo
About the quotation marks... I’ve tried without quotation marks, with " ' ' ", with just simple... everything. Gives the same error. About &...which is wrong?
– Natali
I took this & before (I hadn’t even seen it there but it actually doesn’t feel right) and still gives the same error...
– Natali
for some reason I took the functions that were calling each other and put everything together in sequence and worked...will understand. Thank you :)
– Natali
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).
– Paulo Kazumiti