Data with Longvarchar datatype is not retreived to recordset

[Archana_72]

Hi,

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

[Marsha_R]

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.

[Archana_72]

Hi,

I have even tried out in csv and had same problem.

recordset is getting cleared once we start accessing/ looping through data.

[Marsha_R]

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?