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 Dim objExcel Dim objWorkbook1 Set objExcel = CreateObject("Excel.Application") Set objWorkbook1= objExcel.Workbooks.Open(<Excel Path>) objExcel.visible =true 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 Next objWorksheet1.Cells(iRownumber+1, 1).CopyFromRecordset RS RS.Close Set RS = Nothing Set Conn = Nothing Call objWorkbook1.Save Call objWorkbook1.Close Set objExcel=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?