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
精彩评论