Using ADO GetString with MSHFlexgrid - problem with memo field 
Author Message
 Using ADO GetString with MSHFlexgrid - problem with memo field

Y'all:

I'm using the code below to grab a big recordset from an Access MDB
file.  The MDB file has several memo type fields with embedded
ctrl-enter characters.

I take the recordset and using the Clip procedure I stick it into my
MSHFlexgrid in one fell swoop.  It works great as long as the memo
fields from the Access database DONT CONTAIN ctrl-enter characters.
Will I have to abandon the clip method (oh, its so nice and fast) and
do a field-by-field input of the data and parse each field looking for
and removing ctrl-enter characters?  Please say no.

thanks,
russ

Dim conn As New ADODB.Connection
Dim ConnectionString As String
Dim fl As ADODB.Field
Dim rsVar As Variant

ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;" & _
                "Password=pwd" & _
                ";Persist Security Info=True;" & _
                "User ID=uid" & _
                ";Data Source=" & sFile & _
                ";Jet OLEDB:System database=" & App.Path & "\my.mdw"

conn.Open ConnectionString  'Open the ADO connection
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient  
rst.CursorType = adOpenDynamic    
rst.LockType = adLockBatchOptimistic
rst.Open "MyTable", conn, , , adCmdTable  'grab the table

rst.MoveFirst

rsVar = rst.GetString(adClipString, rst.RecordCount)  '<---------
   With MSHFlexGrid1
     .Cols = rst.Fields.Count
     .Rows = rst.RecordCount
   For i = 0 To rst.Fields.Count - 1
     .TextMatrix(0, i) = rst.Fields(i).Name
   Next

   .Row = 1
   .Col = 0
   .RowSel = .Rows - 1
   .ColSel = .Cols - 1
   .Clip = rsVar       '<------------ problemo code
   .RowSel = .Row
   .ColSel = .Col

   End With
   rst.Close
   conn.Close



Thu, 15 Apr 2004 03:26:13 GMT  
 Using ADO GetString with MSHFlexgrid - problem with memo field
Pretty simple solution.  I just use Replace to replace the pesky CRLF
with nothing.  It is nice and fast too.  The only problem remaining is
the fact that the conversion of the Access memo field type to my
grid's text type is that the text type is limited to 255 characters.
After I do the import I check to see if the length of any text field
is greater than 250.  If not I don't worry about clipping of
anything...if so I popup a copout message.

later,
russ

rsVar = Replace((rst.GetString(adClipString, rst.RecordCount)),
vbCrLf, "")

Quote:

> Y'all:

> I'm using the code below to grab a big recordset from an Access MDB
> file.  The MDB file has several memo type fields with embedded
> ctrl-enter characters.

> I take the recordset and using the Clip procedure I stick it into my
> MSHFlexgrid in one fell swoop.  It works great as long as the memo
> fields from the Access database DONT CONTAIN ctrl-enter characters.
> Will I have to abandon the clip method (oh, its so nice and fast) and
> do a field-by-field input of the data and parse each field looking for
> and removing ctrl-enter characters?  Please say no.

> thanks,
> russ

> Dim conn As New ADODB.Connection
> Dim ConnectionString As String
> Dim fl As ADODB.Field
> Dim rsVar As Variant

> ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;" & _
>                 "Password=pwd" & _
>                 ";Persist Security Info=True;" & _
>                 "User ID=uid" & _
>                 ";Data Source=" & sFile & _
>                 ";Jet OLEDB:System database=" & App.Path & "\my.mdw"

> conn.Open ConnectionString  'Open the ADO connection
> Set rst = New ADODB.Recordset
> rst.CursorLocation = adUseClient  
> rst.CursorType = adOpenDynamic    
> rst.LockType = adLockBatchOptimistic
> rst.Open "MyTable", conn, , , adCmdTable  'grab the table

> rst.MoveFirst

> rsVar = rst.GetString(adClipString, rst.RecordCount)  '<---------
>    With MSHFlexGrid1
>      .Cols = rst.Fields.Count
>      .Rows = rst.RecordCount
>    For i = 0 To rst.Fields.Count - 1
>      .TextMatrix(0, i) = rst.Fields(i).Name
>    Next

>    .Row = 1
>    .Col = 0
>    .RowSel = .Rows - 1
>    .ColSel = .Cols - 1
>    .Clip = rsVar       '<------------ problemo code
>    .RowSel = .Row
>    .ColSel = .Col

>    End With
>    rst.Close
>    conn.Close



Fri, 23 Apr 2004 01:55:15 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Memo fields - problems reporting 2 memo fields

2. Unable to retrieve a memo field using ADO

3. Updating Memo Fields in MS Access using ADO Parameter / Stored Procedures

4. Updating Memo Fields in MS Access using ADO Parameter / Stored Procedures

5. Save a 'Memo' Field Using ADO

6. ADO Update (using VB) of Memo fields in Access 97 DB

7. Using ADO Command object and Access PROCEDURE to insert Memo/Binary fields

8. ADO, VB and Access MEMO field problem

9. Memo Field Update Problem ADO JET

10. ADO and Memo field problems

11. ADO-Oracle-Long(Memo) field concurrency problem

12. VB + ADO + VFP ODBC Driver problem MEMO field

 

 
Powered by phpBB® Forum Software