Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA ADODB Automation - Truncated Memo Fields
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA ADODB Automation - Truncated Memo Fields
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA ADODB Automation - Truncated Memo Fields
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
long memo access fields when loaded to excel 2003 with vb | Excel Programming | |||
Memo Control in Excel XP | Excel Discussion (Misc queries) | |||
Import automation, selected mdb fields from server to Excel | Excel Programming | |||
Access to Excel Automation ADODB Problem | Excel Programming | |||
Fill a Memo field | Excel Programming |