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
DoEvents
Loop

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?'

Regards,


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
'
Rst.Close
Set Rst = Nothing
'
Exit Function
'
Err_Handler:
'
GetTableRowCount = -1
'
End Function