Faster loading and save data in a grid

[mel_flynn]

I am using an MSHFlexgrid to view the contents of an MS Access Database table. The Table has 3 fields and about 5000 records. All records must be loaded, so I am forces to load it as follows:

    SetConnection
    Set RSProd = New ADODB.Recordset
    RSProd.Open "SELECT * FROM TblProdMast ORDER BY ProdNo", Conn, adOpenStatic, adLockOptimistic
    
    With grdProdMast
        .Clear
        .ClearStructure
        .Cols = 4
        .ColWidth(0) = 500
        .ColWidth(1) = 2500
        .ColWidth(2) = 4000
        .ColWidth(3) = 1000

        .Rows = 0
        GCont = ""
        For RCount = 0 To RSProd.Fields.Count - 1
            If GCont = "" Then
                GCont = vbTab & RSProd.Fields(RCount).Name
            Else
                GCont = GCont & vbTab & RSProd.Fields(RCount).Name
            End If
        Next
        .AddItem GCont

        If RSProd.RecordCount > 0 Then
            Do Until RSProd.EOF
                GCont = ""
                For RCount = 0 To RSProd.Fields.Count - 1
                    If GCont = "" Then
                        GCont = vbTab & RSProd.Fields(RCount)
                    Else
                        GCont = GCont & vbTab & RSProd.Fields(RCount)
                    End If
                Next
                .AddItem GCont
                RSProd.MoveNext
            Loop
        Else
            .AddItem ""
        End If
        RSProd.Close
    
        .FixedCols = 1
        .FixedRows = 1
        .ColAlignment(1) = flexAlignLeftCenter
        .ColAlignment(2) = flexAlignLeftCenter
        .ColAlignment(3) = flexAlignLeftCenter
        
        Set RSProd = Nothing
        CloseConnection
    End With

This is obviously very slow to load. Is there a way of speeding up this process?

[Leather]

Set its data source property to the recordset and it will do the rest...

[mel_flynn]

Setting the Datasource to the Recordset can result in problems.
i.e. Numeric Data being loaded as Exponential Numbers

Also I don't like the idea of setting any objects datasource, I prefer to load the data into an object and then you can decide what you want to do with it.

[Leather]

Yes, but loading the data manually is much much much slower....


also you can get round these problems if you clever with your SQL....

eg in the SQL format the number..

[Leather]

If you insist on loading the data manually do this:


Create SQL that will return a single field, that contains all the data tab delimited.. then you won't have to loop through each field on each row to create the add string...

[mel_flynn]

Create SQL that will return a single field, that contains all the data tab delimited

How do I do this?

[Leather]

"SELECT Field1 + '" & chr(9) & "' + Field2 + '" & chr(9) & "' + Field3 As SingleTabDelimitedField FROM MyTable"


(Chr(9) is the Tab character)

[mel_flynn]

Will give it a try. Thanks

[mel_flynn]

I just noticed that when I load the grid using Set Grd.DataSource = RS, all records are not loaded. There are 5000 records in the Table and onlt 2100 records are loaded.

[Leather]

Are you sure?

How do you knwo how many records have been loaded?

[mel_flynn]

grdProdMast.Rows = 2100

[beachbum]

As far as the grid loading goes you should add

grdProdMast.Redraw = False
    
    'Your processing that fills the grid
 
    grdProdMast.Redraw = True

That will speed it up immensely