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

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 OLEDB:Database 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" a écrit dans le message de groupe de discussion :
...
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

On Jan 7, 3:36*pm, "michdenis" wrote:
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 OLEDB:Database 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" a écrit dans le message de groupe de discussion :
...
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

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" a écrit dans le message de groupe de discussion :
...
On Jan 7, 3:36 pm, "michdenis" wrote:
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 OLEDB:Database 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" a écrit dans le message de groupe de discussion :
...
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