0

Well... my problem is that I am running a macro in Excel where I take values from cells and insert them into a local MySQL Database using an ADODB connection.

The thing is that this famous error 91 in runtime appears in the line where I send the command to the database (I'm using Workbench to administrate that database). My code is:

Private Sub btnAgregarE1_Click()
    Sheets("EMPLEADO").Select
    Range("B4").EntireRow.Insert
    Range("B4").Value = Me.txtCodigoEmpleadoE1.Value
    Range("C4").Value = Me.txtNombreE1.Value
    Range("D4").Value = Me.txtFechaE1.Value
    Range("E4").Value = Me.txtReferenciaE1.Value
    Range("F4").Value = Me.txtDireccionE1.Value
    txtCodigoEmpleadoE1.Value = Range("J3").Value

    Me.txtNombreE1.Value = Empty
    Me.txtFechaE1.Value = Empty
    Me.txtReferenciaE1.Value = Empty
    Me.txtDireccionE1.Value = Empty

    Me.listaEmpleado.RowSource = "tEmpleado"
    Me.listaEmpleado.ColumnCount = 5


    Dim con As ADODB.Connection

    Set con = New ADODB.Connection
    con.Open "DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=localhost;DATABASE=bdferreteria;USER=PruebaUser;PASSWORD=Passw0rd;"
    Dim rng As Range
    Set rng = Application.Range("tEMPLEADO")
    Dim row As Range
    Dim instructsql As String
    'instructsql = "Insert into EMPLEADO(Cod_empleado, Nombre, Fecha_inicio, Referencia, Direccion) values ('" & row.Cells(1).Value & "', '" & row.Cells(2).Value & "'," & row.Cells(3).Value & "," & row.Cells(4).Value & "," & row.Cells(5).Value & ")"
    con.Execute "Insert into EMPLEADO(Cod_empleado, Nombre, Fecha_inicio, Referencia, Direccion) values ('" & row.Cells(1).Value & "', '" & row.Cells(2).Value & "'," & row.Cells(3).Value & "," & row.Cells(4).Value & "," & row.Cells(5).Value & ")"
    con.Close

And the error is in the line:

    con.Execute "Insert into EMPLEADO(Cod_empleado, Nombre, Fecha_inicio, Referencia, Direccion) values ('" & row.Cells(1).Value & "', '" & row.Cells(2).Value & "'," & row.Cells(3).Value & "," & row.Cells(4).Value & "," & row.Cells(5).Value & ")"

I've tried to declare and object, then an empty variable, then I created instructsql as a string and with that try to execute the con.Execute command, but the error always appears in that specific line no matter what I do.

Any help will be appreciated

Here is the error message:

Improve this question
5
  • 2
    You declare row as Range-Variable but don´t assign anything to it. Has nothing to do with ADODB. Commented May 30, 2020 at 16:52
  • @FunThomas Thanks, since this is my first time using ADODB connections, I though the error may be in that side. Thanks a lot Commented May 30, 2020 at 17:02
  • You should see stackoverflow.com/q/332365/11683 though. Commented May 30, 2020 at 17:06
  • It helps to split the command: you already declared a String variable and wrote the statement in it, but you commented that out. If you activate it again, you see that the runtime error already happens in that line. You can also check with the debugger the final statement before issuing it. Commented May 30, 2020 at 17:07
  • @GSerg: This (plus formatting) is why I always preach to use ADODB-Parameter, see stackoverflow.com/a/61886170/7599798 Commented May 30, 2020 at 17:12
Related questions
1
Excel-Vba Connect Mysql localhost failed
1
Interesting issue with ADODB connection: UDF using ADODB works fine when called from VBA code, but it returns error when called from EXCEL cell
4
excel vba mysql ado connection
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.