Thread
:
VBA ADODB Automation - Truncated Memo Fields
View Single Post
#
4
Posted to microsoft.public.excel.programming
michdenis
external usenet poster
Posts: 135
VBA ADODB Automation - Truncated Memo Fields
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
Reply With Quote
michdenis
View Public Profile
Find all posts by michdenis