I am using ADO connection to connect to database (ASA or SQL Server).
Everything was working until recently column with datatype LongVarchar was added.
Now data initially retrieved to record set but immediately is it cleared from recordset.
Can anyone let me know whats wrong in below script?
Set Conn = ADO.CreateADOCommand
Conn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=<Datasource name>"
Conn.CommandText = sQry
Set RS = Conn.Execute
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook1= objExcel.Workbooks.Open(<Excel Path>)
Set objWorksheet1= objWorkbook1.Worksheets(<Sheet Name>)
fldCount = rs.Fields.Count
For iCol = 1 To fldCount
objWorksheet1.Cells(iRownumber, iCol).Value = rs.Fields(iCol - 1).Name
objWorksheet1.Cells(iRownumber+1, 1).CopyFromRecordset RS
Set RS = Nothing
Set Conn = Nothing
As you can see, Excel doesn't always play well with others.
Are you copying from the database to Excel in order to use the data for testing? I would use a .csv file instead of Excel. There's no hidden functionality to worry about then. Your data is just there.
I have even tried out in csv and had same problem.
recordset is getting cleared once we start accessing/ looping through data.
Where exactly in the code do you see that that recordset is gone? Does it happen after once through the loop or some other time?
Are you sure that the recordset is even there in the first place? Have you tried outputting the values to make sure?