VBA ADODB Automation - Truncated Memo Fields

[Dave Mac]

Hi there,

Am hoping someone can help me here because I've exhausted most
avenues.

Am retrieving data from my MSAccess db using ADODB recordset. However
the Memo fields are truncated.

I understand that memo fields using the Groupby clause do get
truncated, but am using First() to aggregate.

This works, however some of my Memo fields can be Null so I have been
using an IIF(isNull(),,) to test and thereby is my problem. This ends
up truncating the field even though I use first() as shown below...

First( iif( isNull([Memo]),'',[Memo])))

It works using the IIF function, but I get errors through automation
when Null fields are retrieved.

It seems to be a catch22 problem. Has anyone experienced this or have
any workarounds?

Kind regards,
David

 

[michdenis]

Hi,

Have you tried something like This ?


Sub ImporterAccessVersExcel()

Dim C As Integer, Nb As Long
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim MyRange As Range, Requete As String

With Worksheets("Feuil4")
Set MyRange = .Range("A1")
End With

cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Mes Documents\Comptoir.mdb;" & _
"Jet OLEDBatabase Password=", "admin", ""

Requete = "Select * from Employés"

rst.Open Requete, cnt, adOpenStatic, adLockReadOnly
Nb = rst.RecordCount

Do
MyRange.Offset(, C) = rst.Fields(C).Name
C = C + 1
x = x + 1
Loop Until x = rst.Fields.Count

MyRange.Offset(1).Resize(nb, rst.Fields.Count) = _
TransposeSpecial2(rst.GetRows)

End Sub

'-------------------------------------------------
Function TransposeSpecial2(ByRef Arr As Variant) As Variant
Dim A As Long, B As Long, Arr1() As Variant
Dim C As Long, D As Long
A = UBound(Arr, 1): B = UBound(Arr, 2)
ReDim Arr1(B, A)
For C = 0 To A
For D = 0 To B
Arr1(D, C) = Arr(C, D)
Next
Next
TransposeSpecial2 = (Arr1)
End Function
'-------------------------------------------------


"Dave Mac" <[email protected]> a écrit dans le message de groupe de discussion :
(e-mail address removed)...
Hi there,

Am hoping someone can help me here because I've exhausted most
avenues.

Am retrieving data from my MSAccess db using ADODB recordset. However
the Memo fields are truncated.

I understand that memo fields using the Groupby clause do get
truncated, but am using First() to aggregate.

This works, however some of my Memo fields can be Null so I have been
using an IIF(isNull(),,) to test and thereby is my problem. This ends
up truncating the field even though I use first() as shown below...

First( iif( isNull([Memo]),'',[Memo])))

It works using the IIF function, but I get errors through automation
when Null fields are retrieved.

It seems to be a catch22 problem. Has anyone experienced this or have
any workarounds?

Kind regards,
David

 

[Dave Mac]

Hi,

Have you tried something like This ?

Sub ImporterAccessVersExcel()

 Dim C As Integer, Nb As Long
 Dim cnt As New ADODB.Connection
 Dim rst As New ADODB.Recordset
 Dim MyRange As Range, Requete As String

With Worksheets("Feuil4")
     Set MyRange = .Range("A1")
 End With

cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=C:\Mes Documents\Comptoir.mdb;" & _
            "Jet OLEDBatabase Password=", "admin", ""

Requete = "Select * from Employés"

rst.Open Requete, cnt, adOpenStatic, adLockReadOnly
Nb = rst.RecordCount

 Do
    MyRange.Offset(, C) = rst.Fields(C).Name
        C = C + 1
        x = x + 1
 Loop Until x = rst.Fields.Count

MyRange.Offset(1).Resize(nb, rst.Fields.Count) = _
                         TransposeSpecial2(rst.GetRows)

 End Sub

'-------------------------------------------------
Function TransposeSpecial2(ByRef Arr As Variant) As Variant
Dim A As Long, B As Long, Arr1() As Variant
Dim C As Long, D As Long
    A = UBound(Arr, 1): B = UBound(Arr, 2)
    ReDim Arr1(B, A)
    For C = 0 To A
        For D = 0 To B
            Arr1(D, C) = Arr(C, D)
       Next
    Next
    TransposeSpecial2 = (Arr1)
End Function
'-------------------------------------------------

"Dave Mac" <[email protected]> a écrit dans le message de groupede discussion :
(e-mail address removed)...
Hi there,

Am hoping someone can help me here because I've exhausted most
avenues.

Am retrieving data from my MSAccess db using ADODB recordset. However
the Memo fields are truncated.

I understand that memo fields using the Groupby clause do get
truncated, but am using First() to aggregate.

This works, however some of my Memo fields can be Null so I have been
using an IIF(isNull(),,) to test and thereby is my problem.  This ends
up truncating the field even though I use first() as shown below...

First( iif( isNull([Memo]),'',[Memo])))

It works using the IIF function, but I get errors through automation
when Null fields are retrieved.

It seems to be a catch22 problem. Has anyone experienced this or have
any workarounds?

Kind regards,
David

Thanks for the input, but no I hadn't. Although am not sure what
transposing my data will achieve though?

Its the query that fails when it finds Nulls, so I have to check for
Null, but then it truncates Memo fields.

Does .Getrows somehow perform differently to .Copyfromrecordset with
the Jet engine ?

rgds,
David

 

[michdenis]

I did some test and i got this :
It apparently works !

Rst -> recordSet
Tblo -> array

And MyRange :
'-------------------------
With Worksheets("Sheet1")
Set MyRange = .Range("A1")
End With
'-------------------------

Write name of the fields of the database
Do
MyRange.Offset(, C) = rst.Fields(C).Name
C = C + 1
X = X + 1
Loop Until X = rst.Fields.Count


to transfer the recordset to a range

Tblo = rst.GetRows
Set MyRange = MyRange.Offset(1).Resize(Nb, rst.Fields.Count)
For A = 0 To UBound(Tblo, 1)
For B = 0 To UBound(Tblo, 2)
MyRange.Offset(B, A).Value = Tblo(A, B)
Next
Next



"Dave Mac" <[email protected]> a écrit dans le message de groupe de discussion :
(e-mail address removed)...

Hi,

Have you tried something like This ?

Sub ImporterAccessVersExcel()

Dim C As Integer, Nb As Long
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim MyRange As Range, Requete As String

With Worksheets("Feuil4")
Set MyRange = .Range("A1")
End With

cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Mes Documents\Comptoir.mdb;" & _
"Jet OLEDBatabase Password=", "admin", ""

Requete = "Select * from Employés"

rst.Open Requete, cnt, adOpenStatic, adLockReadOnly
Nb = rst.RecordCount

Do
MyRange.Offset(, C) = rst.Fields(C).Name
C = C + 1
x = x + 1
Loop Until x = rst.Fields.Count

MyRange.Offset(1).Resize(nb, rst.Fields.Count) = _
TransposeSpecial2(rst.GetRows)

End Sub

'-------------------------------------------------
Function TransposeSpecial2(ByRef Arr As Variant) As Variant
Dim A As Long, B As Long, Arr1() As Variant
Dim C As Long, D As Long
A = UBound(Arr, 1): B = UBound(Arr, 2)
ReDim Arr1(B, A)
For C = 0 To A
For D = 0 To B
Arr1(D, C) = Arr(C, D)
Next
Next
TransposeSpecial2 = (Arr1)
End Function
'-------------------------------------------------

"Dave Mac" <[email protected]> a écrit dans le message de groupe de discussion :
(e-mail address removed)...
Hi there,

Am hoping someone can help me here because I've exhausted most
avenues.

Am retrieving data from my MSAccess db using ADODB recordset. However
the Memo fields are truncated.

I understand that memo fields using the Groupby clause do get
truncated, but am using First() to aggregate.

This works, however some of my Memo fields can be Null so I have been
using an IIF(isNull(),,) to test and thereby is my problem. This ends
up truncating the field even though I use first() as shown below...

First( iif( isNull([Memo]),'',[Memo])))

It works using the IIF function, but I get errors through automation
when Null fields are retrieved.

It seems to be a catch22 problem. Has anyone experienced this or have
any workarounds?

Kind regards,
David

Thanks for the input, but no I hadn't. Although am not sure what
transposing my data will achieve though?

Its the query that fails when it finds Nulls, so I have to check for
Null, but then it truncates Memo fields.

Does .Getrows somehow perform differently to .Copyfromrecordset with
the Jet engine ?

rgds,
David