执行SQL时未设置VBA对象变量

Question

我一直在处理这段代码，不知道代码出了什么问题。

在执行查询之前，一切都很正常。

这是代码：

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

当我运行此代码时，我得到一个错误对象变量或块变量未设置。当我添加时

Set oConn = New ADODB.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

为什么会出错？我很感激你们能提供帮助。

Answer 1

从FOR循环中移出Set oConn = Nothing。

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