Faster loading and save data in a grid

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:

Code:

---

    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?

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

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.

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..

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...

Quote:

---

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

---

How do I do this?

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


(Chr(9) is the Tab character)

Will give it a try. Thanks

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.

Are you sure?

How do you knwo how many records have been loaded?

grdProdMast.Rows = 2100

As far as the grid loading goes you should add

VB Code:

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

That will speed it up immensely :)