VBA Object Variable Not Set when execute SQL

Question

I've been working in this code and I don't know whats wrong with the code.

Everything works fine until it get to execute the query.

Here's the code:

Dim i As Integer

ConnectDB
lastrow = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "H").End(xlUp).Offset(0, 0).Row
MsgBox (lastrow)

For i = 2 To lastrow
    get_id = Sheet1.Cells(i, 8).Value
    col1 = Sheet1.Cells(i, 9).Value
    col2 = Sheet1.Cells(i, 10).Value
    col3 = Sheet1.Cells(i, 11).Value
    mySQL = "UPDATE pegawai SET nama = '" & col1 & "', alamat = '" & col2 & "', tlp = " & col3 & " where id = " & get_id & ""
    MsgBox (mySQL)
    oConn.Execute (mySQL)  <---- ERROR HERE
    Set oConn = Nothing
Next i
oConn.Close

When I run this code I get an error object variable or with block variable not set. And when I add

Set oConn = New ADODB.Connection

it produces an error

Operation is not allowed when the object is closed

and this the code for my connection

Dim oConn As ADODB.Connection
Dim rs As ADODB.Recordset

Public Sub ConnectDB()
    Set oConn = New ADODB.Connection
    oConn.Open "DRIVER={MySQL ODBC 3.51 Driver};" & _
    "SERVER=localhost;" & _
    "DATABASE=belajar;" & _
    "USER=root;" & _
    "PASSWORD=;"
End Sub

Why does it get wrong? I appreciate when you all could help.

Yep. Agree with @MikeCheel. Also later in your code, you should close it first before you set it to nothing. — L42
– L42, Commented Sep 4, 2014 at 3:32
Okay I got it know! Thank you very much. But why I should move set oConn = nothing ? could someone explain it to me? — Then Theresia
– Then Theresia, Commented Sep 4, 2014 at 7:10
ConnectDB creates an instance of ADODB.Connection as OConn. when i=2, you terminate the oConn object by setting it to nothing. When next time, i=3, VBA fails to run the execute method of oConn as it has got 'nothing'. For the sake of understanding, if you call ConnectDB after set OConn=nothing, your code will run fine. But its a very bad practice to create and terminate instances in loop. Hope it helps. — cyboashu
– cyboashu, Commented Sep 4, 2014 at 9:10

cyboashu · Accepted Answer · 2014-09-04 03:32:26Z

2

Move out Set oConn = Nothing from FOR loop.

Dim i As Integer

ConnectDB
lastrow = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "H").End(xlUp).Offset(0, 0).Row
MsgBox (lastrow)

For i = 2 To lastrow
    get_id = Sheet1.Cells(i, 8).Value
    col1 = Sheet1.Cells(i, 9).Value
    col2 = Sheet1.Cells(i, 10).Value
    col3 = Sheet1.Cells(i, 11).Value
    mySQL = "UPDATE pegawai SET nama = '" & col1 & "', alamat = '" & col2 & "', tlp = " & col3 & " where id = " & get_id & ""
    MsgBox (mySQL)
    oConn.Execute (mySQL)  <---- ERROR HERE

Next i
oConn.Close

answered Sep 4, 2014 at 3:32

cyboashu

10.5k2 gold badges32 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Object Variable Not Set when execute SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related