Hi all
I'm a fairly beginning VB6 developer, so please bear with me...
I'm tweaking a project that I did not create, and for some reason a connection that seemed to have worked fine in the past, doesn't seem to be working now. I get the error at 'debug.print rs.RecordCount' :
Run-time error '3704':
Operation is not allowed when the object is closed.
The SQL string shows 1000 results in SQL Analyzer, so I know that there should be records. The connection string is valid as well.
The error occurs in a separate class module called GLVDataExport (cGLVDataExport.cls).
The code:
Code:
Set xlApp = New Excel.Application
With xlApp
.Visible = True
On Error Resume Next
.Workbooks.Open (sTemplate)
If Err.Number = 0 Then
writeDebug "Main()", "Successful opening Excel template " & sTemplate
Else
writeDebug "Main()", "Error opening Excel template " & sTemplate & " " & Err.Number & " " & Err.Description
Err.Clear
End
End If
(more code.... formatting columns and pasting data...)
Code:
With myDataExport
.TSQL = ""
.TSQL = (build SQL string...)
.FileOutput = "c:\glvshiprec_detail.xls"
On Error Resume Next
.Export2AllString
...
Code:
Public Function Export2AllString() As Long
Dim rs As Object 'ADODB.Recordset
Dim FirstRow As Boolean, j As Integer
Dim a As Integer, b As Integer, c As Integer
Dim SheetName As String
Set goTextStream = gFso.OpenTextFile(mvarFileOutput, ForWriting, True)
Set rs = CreateObject("ADODB.Recordset") 'New ADODB.Recordset
If rs.State = adStateOpen Then rs.Close
With rs
.CursorLocation = adUseClient
.Open mvarTSQL, gAdoCn, adOpenKeyset, adLockOptimistic
.ActiveConnection = Nothing
End With
Debug.Print rs.RecordCount
This is where I get the error. In the past, this connection worked fine and returned results.
Any ideas? Thanks so much.