multi-tasking while searching the table? 
Author Message
 multi-tasking while searching the table?

To those who know what you are doing, I am new in this,  please help.

I am hitting a bunch of access tables with quarries, with total records
exceeding 100,000 and putting the hits in a list box. it seems like all
activities goes on stand by until the search is done, even the list box
won't get populated until the whole thing is over.  I need to let the
user know this is not a crash with either a graphical indicator or
gradually filling the list box.  any ideas would be greatly appreciated.



Sun, 09 Nov 2003 10:33:52 GMT  
 multi-tasking while searching the table?
DoEvents is a command that releases a quantum of time to the operating
system to respond to things that are waiting for a timeslice. You can try
slipping one of these in between queries or after each additem in the
listbox. Of course, this slows things down, but you can do it if you want
to.

Another possibility is using the progress bar control to let the user know
that something is indeed happening. But this won't respond to user events
such as mouse clicks.

Jim


Quote:
> To those who know what you are doing, I am new in this,  please help.

> I am hitting a bunch of access tables with quarries, with total records
> exceeding 100,000 and putting the hits in a list box. it seems like all
> activities goes on stand by until the search is done, even the list box
> won't get populated until the whole thing is over.  I need to let the
> user know this is not a crash with either a graphical indicator or
> gradually filling the list box.  any ideas would be greatly appreciated.



Sun, 09 Nov 2003 10:49:02 GMT  
 multi-tasking while searching the table?
thanks Jim, I am sure I won't be able to repay the favor here but I will try
to help the less fortunate, if there are any.
Quote:

> DoEvents is a command that releases a quantum of time to the operating
> system to respond to things that are waiting for a timeslice. You can try
> slipping one of these in between queries or after each additem in the
> listbox. Of course, this slows things down, but you can do it if you want
> to.

> Another possibility is using the progress bar control to let the user know
> that something is indeed happening. But this won't respond to user events
> such as mouse clicks.

> Jim



> > To those who know what you are doing, I am new in this,  please help.

> > I am hitting a bunch of access tables with quarries, with total records
> > exceeding 100,000 and putting the hits in a list box. it seems like all
> > activities goes on stand by until the search is done, even the list box
> > won't get populated until the whole thing is over.  I need to let the
> > user know this is not a crash with either a graphical indicator or
> > gradually filling the list box.  any ideas would be greatly appreciated.



Sun, 09 Nov 2003 11:03:44 GMT  
 multi-tasking while searching the table?
And not to forget:
Make the lstList.visible=false

But speed has to be in mind when dealing with great numbers of records:
You can make your code up to 3-4 times faster
by explicitly creating Field objects before running the loop,
as follows:

Dim rs As New ADODB.Recordset
Dim fldFName As ADODB.Field
Dim fldLName As ADODB.Field

rs.Open "SELECT au_lname, au_fname FROM authors",  _
                "DSN=pubs", , , adCmdText

' create the two Field objects that reference those two fields
Set fldLName = rs.Fields("au_lname")
Set fldFName = rs.Fields("au_fname")

Do Until rs.EOF
    ' here you use the Field object's default Value property
    List1.AddItem fldLName & ", " & fldFName
    rs.MoveNext
Loop
rs.Close

However, bear in mind that looping on all the records
in a Recordset rarely is your fastest option.
For example, in this particular case you can populate
the ListBox control faster using the following approach:

Dim rs As New ADODB.Recordset
Dim varArray() As Variant
Dim i As Long

' let SQL Server concatenate the strings
rs.Open "SELECT au_lname+', '+au_fname FROM authors",  _
             "DSN=pubs", , , adCmdText
' get all the strings in one operation
varArray() = rs.GetRows()
' fill the ListBox control
For i = 0 To UBound(varArray, 2)
    List1.AddItem varArray(0, i)
Next

Morten


Quote:
> DoEvents is a command that releases a quantum of time to the operating
> system to respond to things that are waiting for a timeslice. You can try
> slipping one of these in between queries or after each additem in the
> listbox. Of course, this slows things down, but you can do it if you want
> to.

> Another possibility is using the progress bar control to let the user know
> that something is indeed happening. But this won't respond to user events
> such as mouse clicks.

> Jim



> > To those who know what you are doing, I am new in this,  please help.

> > I am hitting a bunch of access tables with quarries, with total records
> > exceeding 100,000 and putting the hits in a list box. it seems like all
> > activities goes on stand by until the search is done, even the list box
> > won't get populated until the whole thing is over.  I need to let the
> > user know this is not a crash with either a graphical indicator or
> > gradually filling the list box.  any ideas would be greatly appreciated.



Mon, 10 Nov 2003 05:11:11 GMT  
 multi-tasking while searching the table?
Thanks Morten,  I had two invisible text boxes bounded to thefields that I was
interested in and checking the text box for hits as I was whiling through, but
your code looks much cleaner, I will give the array approach a shot and see what
happens to performance.
thanks again.
Quote:

> And not to forget:
> Make the lstList.visible=false

> But speed has to be in mind when dealing with great numbers of records:
> You can make your code up to 3-4 times faster
> by explicitly creating Field objects before running the loop,
> as follows:

> Dim rs As New ADODB.Recordset
> Dim fldFName As ADODB.Field
> Dim fldLName As ADODB.Field

> rs.Open "SELECT au_lname, au_fname FROM authors",  _
>                 "DSN=pubs", , , adCmdText

> ' create the two Field objects that reference those two fields
> Set fldLName = rs.Fields("au_lname")
> Set fldFName = rs.Fields("au_fname")

> Do Until rs.EOF
>     ' here you use the Field object's default Value property
>     List1.AddItem fldLName & ", " & fldFName
>     rs.MoveNext
> Loop
> rs.Close

> However, bear in mind that looping on all the records
> in a Recordset rarely is your fastest option.
> For example, in this particular case you can populate
> the ListBox control faster using the following approach:

> Dim rs As New ADODB.Recordset
> Dim varArray() As Variant
> Dim i As Long

> ' let SQL Server concatenate the strings
> rs.Open "SELECT au_lname+', '+au_fname FROM authors",  _
>              "DSN=pubs", , , adCmdText
> ' get all the strings in one operation
> varArray() = rs.GetRows()
> ' fill the ListBox control
> For i = 0 To UBound(varArray, 2)
>     List1.AddItem varArray(0, i)
> Next

> Morten



> > DoEvents is a command that releases a quantum of time to the operating
> > system to respond to things that are waiting for a timeslice. You can try
> > slipping one of these in between queries or after each additem in the
> > listbox. Of course, this slows things down, but you can do it if you want
> > to.

> > Another possibility is using the progress bar control to let the user know
> > that something is indeed happening. But this won't respond to user events
> > such as mouse clicks.

> > Jim



> > > To those who know what you are doing, I am new in this,  please help.

> > > I am hitting a bunch of access tables with quarries, with total records
> > > exceeding 100,000 and putting the hits in a list box. it seems like all
> > > activities goes on stand by until the search is done, even the list box
> > > won't get populated until the whole thing is over.  I need to let the
> > > user know this is not a crash with either a graphical indicator or
> > > gradually filling the list box.  any ideas would be greatly appreciated.



Mon, 10 Nov 2003 23:02:41 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. multi-tasking while searching the table?

2. Multi tables vs multi mdb files

3. Multi-tabled db: acess to all tables

4. Empty tables in a multi-tables query

5. Empty tables in a multi-tables query

6. Multi-day task pads like weekly calendar

7. Advice on Multi-Tasking / Windows Services

8. How to do multi-tasking in VB

9. Multi-Tasking DOS

10. Need Help with Multi-Tasking in MS-DOS 5.0

11. Multi-tasking/API question

12. Multi-tasking

 

 
Powered by phpBB® Forum Software