**RESOLVED** Establish Progressbar w/ ADO Connection???

I have been searching this forum as well as the web to locate how I can accomplish this task...

I have a form that connects to a database table, and since the table has many records and several users access it, I have found that it takes between 15-30 seconds to load (it should also be noted that there are several agregate functions involved within the Form Load)

I would like to establish a progressbar that will show the users how long the connection is taking....

I am interested in any other suggestions, but at this point I am lost...

Thank you.

I have never tried this but it seems like it may work.

Declare your recordset using Withevents so you can handle events
Open/execute the query asynchronously.
Use the FetchProgress event to update your progress bar.

That sounds interesting....

How do I go about declaring my recordsets using WithEvents???

Also will the Windows Progressbar that comes with the VB6 work with Asynchronously query...somewhere I read something about the Progress bar not able to do that...

But I am up for anything...I just am not too familiar with the process you suggesssted...if you have time, I would love to learn it..

Thank you..

The progress bar has absolutly NOTHING to do with sync/async. What has to do with it is the ADO things and it's with it that you have to worry about because it might freeze your whole app

This code works for me somewhat. There is no property or information from ADO that indicates how many records will be returned from the database. I just set the ProgressBar.Max value to an "expected value" (ie I know my table contains 94000+ records, so I set it to 100,000). You could always run an Select Count(*) query beforehand.

Since the querie(s) run asynchronously, your users can begin to use your application almost immediately.

VB Code:

Option Explicit
 
Private WithEvents oRS As ADODB.Recordset
 
Private Sub Form_Load()
    Dim oConn As ADODB.Connection
   
    Set oConn = New ADODB.Connection
    oConn.Open "provider=sqloledb;data source=servername;initial catalog=dbname;integrated security=sspi"
    
    Set oRS = New ADODB.Recordset
    With oRS
        .CursorLocation = adUseClient
        .LockType = adLockReadOnly
        .CursorType = adOpenStatic
 
        .Properties("Initial Fetch Size").Value = 10
 
         'the FetchProgress event will fire after every 500 records 
        .Properties("Background Fetch Size").Value = 500
    End With
    
    oRS.Open "Select * From Tablename", oConn, , , adAsyncFetch Or adCmdText
    Set oRS.ActiveConnection = Nothing
 
    'Display data from the first record.
    Text1.Text = oRS.Fields(1).Value
 
 
    oConn.Close
    Set oConn = Nothing
End Sub
 
Private Sub oRS_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
    ProgressBar1.Max = pRecordset.RecordCount
    ProgressBar1.Value = ProgressBar1.Max
End Sub
 
Private Sub oRS_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
    If MaxProgress > ProgressBar1.Max Then
        ProgressBar1.Max = MaxProgress
    End If
    If Progress < ProgressBar1.Max Then
        ProgressBar1.Value = Progress
    Else
        ProgressBar1.Value = ProgressBar1.Max
    End If
End Sub

Thanx brucevde, I will give it a shot!

I appreciate your cooperation and welcome the opportunity to learn!!

Thank you again :wave:

I tried the code.

The problem is that only the last FetchProgress event fires. For example if the recordset has 10,000 records and I set an update interval of 100 records, only the event showing record 9,900 fires.

It seems that the recordset behaves synchronously instead of anynchronously.

I use MDAC 2.8 and I set adAsynchFetch on the recordset.Open options

--hyperspaced