开发者

Error on Connection close when connecting VBA to Oracle

I'm encoun开发者_运维知识库tering an error when connecting to an Oracle DB from VBA code in an Excel Spreadsheet. The query that I run works fine, but when I try to close the connection, I get the following error:

Runtime Error 3265: Item cannot be found in the collection corresponding to the requested name or ordinal.

A copy of my code is below. The error occurs on the line "cn.close". Any help would be greatly appreciated!

Sub GetData()
Dim cn As New ADODB.Connection 
comm As New ADODB.Command
rs As New ADODB.Recordset
On Error GoTo errhandler:

    cn.ConnectionString = "DSN=XXX;Uid=XXX;Password=XXX;"    
    cn.Open

    comm.CommandType = adCmdText
    comm.CommandText = "Select * from XXX where rownum < 10;"
    Set comm.ActiveConnection = cn
    rs.ActiveConnection = cn
    rs.Open comm
    Sheets("Sheet1").Range("a1").Offset(1, 0).CopyFromRecordset rs  'copy the records
    rs.Close

    cn.Close

errhandler:
    Debug.Print (Err.Description)
    Debug.Print "Error# " & cn.Errors(0).NativeError & ": " & cn.Errors(0).Description
Stop

End Sub


There's nothing to stop execution from continuing into your error handler after the cn.Close line, so it's possible your error is coming from the error handler itself (because the handler is trying to reference a non-existant Err object).

...     
rs.Close
cn.Close
Exit Sub   ' don't run into your error handler

errhandler:
  Debug.Print (Err.Description)
  Debug.Print "Error# " & cn.Errors(0).NativeError & _
              ": " & cn.Errors(0).Description 
  'Stop  'delete this - not needed here

End Sub 
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜