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

[Salvatore]

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.

[brucevde]

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.

[Salvatore]

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

[PT Exorcist]

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

[brucevde]

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.

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

[Salvatore]

Thanx brucevde, I will give it a shot!

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

Thank you again

[Hyperspaced]

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