ADO and Large Varchar truncated to 255 chars   

Hi there, 

I am trying to retrieve data from an SQL Server 2000 db. The data is 
Varchar(4000).  Using the following VBA the data gets truncated to 255 chars. 
 How do I get data from large Varchar fields using ADO?

Many thanks

Tim


    Do Until rs.EOF
        i = 0
        For Each f In rs.Fields
            Debug.Print f.Value, Len(f.Value)
            .ActiveCell.Offset(lRow + 1, i) = f.Value
            i = i + 1
        Next f
        lRow = lRow + 1
        rs.MoveNext
    Loop

date: Mon, 16 Jun 2008 17:58:01 -0700   author:   Tim

Re: ADO and Large Varchar truncated to 255 chars   

hi Tim,

Tim write:
> I am trying to retrieve data from an SQL Server 2000 db. The data is 
> Varchar(4000).  Using the following VBA the data gets truncated to 255 chars. 
>  How do I get data from large Varchar fields using ADO?
Post the SQL statement used for opening rs.

> Debug.Print f.Value, Len(f.Value)
What does f.Type return?


mfG
--> stefan <--

date: Tue, 17 Jun 2008 11:40:04 +0200   author:   Stefan Hoffmann

Re: ADO and Large Varchar truncated to 255 chars   

This newsgroup is for questions about Access, the database product that's 
part of Office Professional. It looks as though you're using Excel, so you 
might want to as in a newsgroup related to Excel.

When you do repost, it would probably help the responder to know what 
version of Excel you're using, since different versions have different 
limitations on how much data can be put into a single cell.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Tim"  wrote in message 
news:89FC0351-AACF-4AB7-B4C8-892999D447B5@microsoft.com...
> Hi there,
>
> I am trying to retrieve data from an SQL Server 2000 db. The data is
> Varchar(4000).  Using the following VBA the data gets truncated to 255 
> chars.
> How do I get data from large Varchar fields using ADO?
>
> Many thanks
>
> Tim
>
>
>    Do Until rs.EOF
>        i = 0
>        For Each f In rs.Fields
>            Debug.Print f.Value, Len(f.Value)
>            .ActiveCell.Offset(lRow + 1, i) = f.Value
>            i = i + 1
>        Next f
>        lRow = lRow + 1
>        rs.MoveNext
>    Loop

date: Tue, 17 Jun 2008 09:15:49 -0400   author:   Douglas J. Steele

Re: ADO and Large Varchar truncated to 255 chars   

Hi Tim,
if you are using an aggregate query string (using Group By)  then the string 
fields will be truncated to 255 characters. Use a simple select query 
instead.
Regards,
Ed.
"Tim"  wrote in message 
news:89FC0351-AACF-4AB7-B4C8-892999D447B5@microsoft.com...
> Hi there,
>
> I am trying to retrieve data from an SQL Server 2000 db. The data is
> Varchar(4000).  Using the following VBA the data gets truncated to 255 
> chars.
> How do I get data from large Varchar fields using ADO?
>
> Many thanks
>
> Tim
>
>
>    Do Until rs.EOF
>        i = 0
>        For Each f In rs.Fields
>            Debug.Print f.Value, Len(f.Value)
>            .ActiveCell.Offset(lRow + 1, i) = f.Value
>            i = i + 1
>        Next f
>        lRow = lRow + 1
>        rs.MoveNext
>    Loop

date: Fri, 27 Jun 2008 15:55:12 +1000   author:   Ed Adamthwaite