ADO problem when accessing long oracle datatype

[lambertar]

Hi there,

It's my first time posting on a forum for help and as if it's not enough :? I'm posting in english which is not my native language :roll: s I'll do my best to write something understandable.

Here's my prob :

I'm developing an application in VBA for Word. I must automatiquely generate a document using data located in an oracle data base. My connection is working and I'm able to extract the data I need, no problem there.

BUT, when it's time to extract the data from a long datatyped column, the field of my recordSet only contains 100 characters when in reality, the cell contains more than 1500 characters.

What I really foud weird, is that :

Selection.TypeText rs.Fields.Item(1).DefinedSize

is equal to 2147483647 and whatever the size of the information in my cell, the actual size is always 100 in the field.

Here's the code I'm using in vba word as a macro. It's not completed since I've been doing a lot of testing during the last two days :hb: but it works fine for now... if not considering the 100 characters limitation bug... grrr :reallymad:

Public Sub CreerDefVues()

    'Application.ScreenUpdating = False

    Dim i As Integer
    Dim strSQL As String
    Dim doc As Document
    Dim rs As ADODB.Recordset
    Dim rs2 As ADODB.Recordset
    Dim rsCons As ADODB.Recordset
    Dim conn As ADODB.Connection

    Set doc = ActiveDocument
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    'Ouverture de la connection à la BD
    conn.Open "Provider=OraOLEDB.Oracle.1;" & _
    "Password=topsecret;" & _
    "Persist Security Info=True;" & _
    "User ID=apps;" & _
    "Data Source=GDEV00;"

    'Crée un record set contenant le résultat de la requête
    strSQL = "SELECT VIEW_NAME, TEXT, TEXT_LENGTH from DBA_VIEWS " & _
    "where VIEW_NAME = 'XXGIR_CAP_FND_FLEX_VALUES_V' order by VIEW_NAME"
    rs.Open strSQL, conn

    Selection.TypeText Text:="Définition des vues"
    Selection.TypeParagraph
    Selection.TypeParagraph
    
    Dim requete As String
    Dim sources As String
    Dim condition As String
    Dim colonnes As String
    
    Dim posFrom As Integer
    Dim posWhere As Integer
    Dim longueur As Integer
    
    'Pour chaque vue
    'Do Until rs.EOF******
        requete = rs.Fields(1)
        longueur = rs.Fields(2)
        
        posFrom = InStr(1, requete, "From", vbTextCompare)
        posWhere = InStr(1, requete, "Where", vbTextCompare)
        
        sources = Mid(requete, posFrom + 4, posWhere)
        condition = Right(requete, longueur - (posWhere + 5))
        colonnes = Left(requete, posFrom)
        
        'On indique le titre
        Selection.TypeText rs.Fields.Item(0).Value
        Selection.TypeParagraph
        Selection.TypeText requete
        Selection.TypeParagraph
        Selection.TypeText rs.Fields.Item(1).DefinedSize
        Selection.TypeParagraph
        
        'On crée le tableau
        ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=1, NumColumns:= _
        2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
        wdAutoFitFixed
        With Selection.Tables(1)
            .Columns.PreferredWidth = CentimetersToPoints(4)
            If .Style <> "Grille du tableau" Then
                .Style = "Grille du tableau"
            End If
            .ApplyStyleHeadingRows = True
            .ApplyStyleLastRow = True
            .ApplyStyleFirstColumn = True
            .ApplyStyleLastColumn = True
        End With
        
        Selection.TypeText "La requête"
        Selection.MoveRight Unit:=wdCell
        Selection.TypeText rs.Fields.Item(1)
        Selection.MoveRight Unit:=wdCell
        Call remplirSources(sources)
        Call remplirCondition(condition)
        Call remplirColonnes(colonnes)
        
        Selection.Rows.Delete
        Selection.TypeParagraph
        'rs.MoveNext******
    'Loop******

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

End Sub

Private Sub remplirSources(ByRef chaine As String)
    Selection.TypeText "Tables sources :"
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText chaine
    Selection.MoveRight Unit:=wdCell
End Sub

Private Sub remplirCondition(ByRef chaine As String)
    Selection.TypeText "Définir la condition :"
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText chaine
    Selection.MoveRight Unit:=wdCell
End Sub

Private Sub remplirColonnes(ByRef chaine As String)
    Selection.TypeText "Colonnes de la vue :"
    Selection.MoveRight Unit:=wdCell
    Selection.TypeText chaine
    Selection.MoveRight Unit:=wdCell
End Sub