Increase Character Limit in ADO Recodset

[berlinhammer]

Hello All,

I am using the following code to establish an ODBC connection from Excel 2003 to an Oracle database and return an ADO recordset to the workbook.

My connection string is a DSN as you can see.

I am having trouble because one of the fields contains a long text entry and googling leads me to believe that the character limit for ADO recordsets is 255, way short of what I need. Limiting the string length to < 255 is stopping my macro from crashing but I would prefer to increase the limit and receive all off my text as I am losing a lot of information at the moment.

Very grateful for any help!

Jon

Option Explicit

Private Function GenerateSQLString() As String

Dim l As Long
Dim rng As Range
Dim str As String

Set rng = Sheets("SQL").Range("rngSQL")

For l = 1 To rng.Cells.Count
If Len(rng.Cells(l).Value) > 0 Then
    str = str & rng.Cells(l).Value & Chr(10)
End If
Next l

GenerateSQLString = str

End Function



Public Sub GetData()
    
    On Error GoTo ErrHandler:
    
    Dim myFile, fnum
    
    Dim conn As New ADODB.Connection
    Dim rsRecords As New ADODB.Recordset
    Dim connString As String
    Dim sqlString As String
    Dim iCols As Long
    Dim iRows As Long

    connString = "DSN=intra;Uid=*****;Pwd=******
    sqlString = GenerateSQLString
    
    conn.ConnectionTimeout = 30
    conn.CommandTimeout = 0
    conn.Open connString
    
    rsRecords.CursorLocation = adUseClient
    rsRecords.CursorType = adOpenStatic
    rsRecords.Open sqlString, conn


    If conn.State = adStateOpen Then
        On Error Resume Next
        ThisWorkbook.Names("rngPivotData").Delete
        On Error GoTo 0
        Sheets("Data").Cells(10, 2).CurrentRegion.ClearContents
        iRows = rsRecords.RecordCount
        Worksheets("Data").Range("K2").Value = Now()
        Worksheets("Data").Range("L2").Value = iRows
        Worksheets("Data").Range("B10").CopyFromRecordset rsRecords
      
        For iCols = 0 To rsRecords.Fields.Count - 1
            Worksheets("Data").Range("B9").Cells(1, iCols + 1).Value = rsRecords.Fields(iCols).Name
        Next
        ThisWorkbook.Names.Add "rngPivotData", Sheets("Data").Cells(10, 2).CurrentRegion
    Else
        MsgBox "no connection"
    End If

    rsRecords.Close
    Set rsRecords = Nothing
    conn.Close
    Set conn = Nothing

ErrHandler:
     If conn.Errors.Count > 0 Then
          MsgBox conn.Errors(0).Description
     End If

End Sub