Speeding up grid loading

[savok]

I know there has to be a better way to load the grid than the one i use. My db is about 40k records and this is how i load the grid

strSearch = UCase(txtName) & "%"
objComm.CommandText = "Select field from table where lastname like '" & strSearch & "' order by lastname"
objComm.CommandType = adCmdText
Set objRec = New ADODB.Recordset
Set objRec.Source = objComm
objRec.Open
If objRec.EOF Or objRec.BOF Then
msgbox("blah blah&quot
Else
While Not objRec.EOF
MSFlexGrid1.Col = 0
MSFlexGrid1.Row = i

If Not IsNull(objRec("field&quot) Then
MSFlexGrid1.Text = objRec("field&quot
End If
objRec.MoveNext
Wend
End if


now if the Search string is a few characters like AB the grid loads fine but if the search string is one character like A or M it takes forever.

Anything I can do to speed this up?

I am new to VB so dont know too much

 

[TomB]

I am not sure, but there should be a property of the FlexGrid like ScreenUpdate which you should set to false before you load the data and then set it back to true.

Hope that helps ...

Tom

 

[savok]

Couldnt find anything on ScreenUpdate. Does anyone have any other ways to maximize grid loading speed?

 

[TomB]

The property name is MSFlexGrid.Redraw !

Tom

 

[savok]

I got it to work Tom, but the Redraw only hides the grid and then show it once its all loaded. Doesnt speed up the loading much. What I am looking for is a quicker way to load the grid it self. Instead of the usual do while loop and loading it row by row, col by col. Because that takes a long time with many records. Anyone know?

thanks

 

[TomB]

It's me again, try this:

You can put the text in a cell directly:

MSFlexGrid1.TextMatrix(row, column) = newText

You don not need to set the col and row property.

Tom

 

[savok]

Hey Tom thanks again, this method is faster then the one I used. But my problem is still there and i think it has to do with the way i select the records out of the database. Every time I try to load over 100 records or so it just slows down. Perhaps its because it can only store 100 and then does something else to add extras. It must have to do something with the ammount of records it can hold at a time. Because if i load 50 records its super fast. Once it gets over 100 it just takes forever. Any ideas at all on what it could be?

this is the sql i use to select the records perhaps there is a faster way?

Select * from table where LastName like '" & strSearch & "' order by lastname, firstname

strSearch is a txtbox where user Enters the Lastname. If you enter Morg it gives the results super fast. if you enter M it takes forever.

Could it also have something to do with the fact that i am sorting it ?

Any ideas at all on how to speed up the selection

thanks

 

[DSJ1967]

I had a very similar problem and was resigned to the fact that I was not going to load the entire table into the grid. Instead load my record set by 'pages' (i.e) the number of records that are actually visible in the flexgrid. Using the absolute position of the recordset + the number of records visible, simply load the amount of records that you need to display. To pull this off, you need a vertical scroll bar to be placed onto of the scroll bar of the Flexgrid so that the records displayed are actually controlled by the 'new' scroll bar. I seen a really good article on this @

http://support.microsoft.com/support/kb/articles/Q254/1/17.asp

Hope this helps

 

[bzac]

I am using mshflex grid to list 238 customers and it doesn't take any time to fill it out

set mshflexgrid1.datasource = <adorecordSet Name>

if mshgrid has a datasource property to bound , try it

 

[edderic]

place a ADO data control and bind it to Msflexgrid

strSearch = UCase(txtName) & &quot;%&quot;
objComm.CommandText = &quot;Select field from table where lastname like '&quot; & strSearch & &quot;' order by lastname&quot;
objComm.CommandType = adCmdText
Set objRec = New ADODB.Recordset
Set objRec.Source = objComm
objRec.Open

set adodc1.Recordset = objRec Eric De Decker
vbg.be@vbgroup.nl

License And Copy Protection AxtiveX.

Download Demo version on my Site:

http://www.createsoftware2000.com

 

[edderic]

Or :

MSFlexGrid1.Cols = objRec.Fields.Count
For i = 0 To MSFlexGrid1.Cols - 1
MSFlexGrid1.TextMatrix(0, i) = objRec.Fields(i).Name
Next
Do While Not objRec.EOF
MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
For i = 0 To MSFlexGrid1.Cols - 1
MSFlexGrid1.TextMatrix(MSFlexGrid1.Rows - 1, i) = objRec.Fields(i) & &quot;&quot;
Next
objRec.MoveNext
Loop

Eric De Decker
vbg.be@vbgroup.nl

License And Copy Protection AxtiveX.

Download Demo version on my Site:

http://www.createsoftware2000.com

 

[DariceLR]

What are the objRec and objComm typed as?

Are they variables or a form field? ****************
DariceLR
:-{} :-V :-x
****************

 

[chadt]

Is this a linked table in access? I have had probelms when trying to access a linked Btrieve table. The overhead in access can slow it down tons. Make sure your using the proper connection string.