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:

GSerg
  • 76,472
  • 17
  • 159
  • 346
DarkNight598
  • 3
  • 4
  • 2
    You declare `row` as `Range`-Variable but don´t assign anything to it. Has nothing to do with `ADODB`. – FunThomas May 30 '20 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 – DarkNight598 May 30 '20 at 17:02
  • You should see https://stackoverflow.com/q/332365/11683 though. – GSerg May 30 '20 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. – FunThomas May 30 '20 at 17:07
  • @GSerg: This (plus formatting) is why I always preach to use ADODB-Parameter, see https://stackoverflow.com/a/61886170/7599798 – FunThomas May 30 '20 at 17:12

0 Answers0