I have a program in VB6 that creates, for reasons I won't go into, a host of Access databases from data held in a central Access database. There is a control database that restricts the data and defines security in the final database. The central database has only one table, SALESDATA. This table can be over 2 million rows long. It is created by merging data from several sources.

My operations team have asked me if I can show that the program is working by putting a progress bar on a form while a target database is being populated. I think this is so they can schedule their naps better but it's not prudent to say things like that.

The problem is during the Select * from SALESDATA command. If I process it synchronously the system sits and appears to be frozen while its being executed, the task manager shows the program as Not Responding, so I decided to process the Select command Asynchronously.

This code happily processes the Select asynchronously and makes a little graphic spin round but I'd still like to use a progress bar and for that I need numbers. The number of rows in the table and the current number of rows processed.

Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
Dim lngRecs As Long
cmd.ActiveConnection = cnnSalesData
cmd.CommandTimeout = 1
cmd.CommandText = "Select * from SALESDATA"
cmd.CommandType = adCmdText
Set rssSalesData = cmd.Execute(lngRecs, , adAsyncExecute)

intImage = 0

Do While (cmd.State And adStateExecuting) = adStateExecuting
intImage = intImage + 1
If intImage > 4 Then
intImage = 1
End If
Set picSpinner.Picture = ImageList1.ListImages(intImage).Picture

I already have a fast way to determine the number of rows in a table. Even for a table of over 1 million rows my method takes only half a second. The problem I have is finding out how far through the table the Select statement is at any given time so that I can increment the progress bar value accordingly.

Can anybody help me here? I have scoured the help and the web but to no avail. Is there a property that gives me an answer to 'how many rows have you processed so far?'


Kevin Bell

PS. Here is the code to retrieve the number of rows in a table. All you Access wizards out there probably already know how to do this but I'm new to Access and VB and am quite pleased with myself that I came up with this.

Public Function GetTableRowCount(objCnn As ADODB.Connection, sTBL As String) As Long
Dim Rst As New ADODB.Recordset
Dim lRecCount As Long
Dim sSQL As String
On Error GoTo Err_Handler
sSQL = "SELECT Count(*) As RecNum From " & sTBL
Set Rst = objCnn.Execute(sSQL)
lRecCount = Rst.Fields("RecNum")
GetTableRowCount = lRecCount
Set Rst = Nothing
Exit Function
GetTableRowCount = -1
End Function