How to simulate a progress bar in a form without using an ActiveX control in Access 2002

This article was previously published under Q304581
This article has been archived. It is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

IN THIS TASK

SUMMARY
This article shows you how to simulate a progress bar on a form by using the rectangle controls in the Microsoft Access toolbox. In this example, you create a form that uses either Data Access Objects (DAO) or Microsoft ActiveX Data Objects (ADO) to read data in the Customers table. As Access reads each record, the form code fills another piece of the progress bar.

NOTE
: If you decide to use ADO to read the records, you must have either the Microsoft Data Engine (MSDE) or Microsoft SQL Server installed. Additionally, you must have either Northwind or NorthwindCS installed on your MSDE or SQL Server.

back to the top

Create a New Access Database and Import the Customers Table

  1. Start Microsoft Access.
  2. Create a new database, and name it Progressbar.
  3. On the File menu, point to Get External Data, and then click Import.
  4. In the Import dialog box, locate Northwind.mdb, and then click Import.
  5. In the Import Objects dialog box, click the Customers table, and then click OK.
back to the top

Create the Form

  1. Open the Progressbar database that you created earlier.
  2. On the View menu, point to Database Objects, and then click Forms.
  3. Click New. In the New Form dialog box, make sure that Design View is selected, and then click OK.
  4. Add the following objects to the form and set the following properties:
       Form: frmProgressBar   -------------------------   Caption: Progress Bar Form   Width: 6.333"   General Declarations: 
    Dim lCounter As Long
       OnOpen:
     ' Make sure, when the form is initially opened, that the status caption reads READY.Me.Status.Caption = "Ready"
       Detail   ------------   Height: 1.5"   Label: Status   ----------------   Caption: Reading   Left: 0.0833"   Top: 0.4167"   Width: 0.4583"   Height: 0.1667"   Text Box: CurrentRecordID   -------------------------   Left: 0.5833"   Top: 0.4167"   Width: 0.9167"   Height: 0.1667"   Rectangle   ----------------------   Name: ProgressBarA   Left: 0.0833"   Top: 0.7083"   Width: 4.7917"   Height: 0.1667"   Special Effect: Sunken   Rectangle   -------------------------   Name: ProgressBarB   Left: 0.0833"   Top: 0.7083"   Width: 0"   Height: 0.1458"   Back Style: Normal   Back Color: 10040115   Special Effect: Flat   Border Style: Transparent   Command Button   --------------   Name: Read   Caption: Read   Left: 2.9167"   Top: 0.3333"   Width: 1.0"   Height: 0.25"   OnClick: 					
    back to the top

    ADO Method

    This code sample uses ADO. Type or paste the following procedure:
    ' As soon as you click the command button, start reading records.' Because you've started reading records, update the status caption to' READING.Me.Status.Caption = "Reading"' For the currently open form, display a busy/hourglass mouse icon.Screen.MousePointer = 11' Dimension the connection and recordset objects for using ADO.Dim cn As ADODB.ConnectionDim rs As ADODB.Recordset' Set the connection properties. ' ==================================================================' BEGIN - Using ADO connection to local table.' ==================================================================' Use Set cn = CurrentProject.Connection to connect to a local table ' using ADO.    Set cn = CurrentProject.Connection ' ==================================================================' END - Using ADO connection to local table.' ==================================================================' ==================================================================' BEGIN - Using new ADO connection to SQL Server.' ==================================================================' If you want to connect to a SQL Server using ADO, then uncomment these ' lines of code and comment out the "Using ADO connection to local ' table" code above. ' With cn' .Provider = "SQLOLEDB"' Change the Data Source name to your SQL Server.' The current connection string specifies integrated ' security. You may have to change this.'.ConnectionString = "Data Source=TestSQL; Integrated Security=SSPI;Initial Catalog=Northwind"' Open the connection.'.Open'End With' ==================================================================' END - Using new ADO connection to SQL Server.' ==================================================================' Set the recordset object to the Customers table.            Set rs = New ADODB.RecordsetWith rsSet .ActiveConnection = cn.CursorLocation = adUseClient.Source = "Customers".Open' While there are still records to be read, display the current customer ' ID number and fill the progress bar to the current record's location, ' based on the total number of records in the Customers recordset. While Not .EOF' Set the form's record ID number text box equal to the current record ' being read.CurrentRecordID = .Fields("CustomerID")' Set the width of the visible (or top) progress bar rectangle.         ProgressBarB.Width = (ProgressBarA.Width / .RecordCount) * .AbsolutePosition' Repaint the current form.Me.Repaint' Move to the next record..MoveNextFor lCounter = 1 To 750000: Next    WendEnd With' If you're at the end of the Customers recordset, then fill the ' progress bar completely and repaint the form.If rs.EOF ThenProgressBarB.Width = rs.RecordCountMe.Repaint' Clear any customer ID information from the form because you're finished.' Set the caption for the Status label to DONE.   CurrentRecordID = ""Me.Status.Caption = "Done"   ' Set the progress bar's width to zero. Repaint the form.ProgressBarB.Width = 0Me.Repaint    End If    ' Close the recordset.rs.Close' Close the connection.cn.Close' Clear the recordset and database objects.Set rs = NothingSet cn = Nothing' Set the form's mouse pointer back to the default mouse pointer.Screen.MousePointer = 0
    back to the top

    DAO Method

    This code sample uses DAO. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, follow these steps:

    • On the View menu, point to Database Objects, and then click Modules. Click New.
    • On the Tools menu, click References. Locate the Microsoft DAO Object Library. Select the Microsoft DAO 3.6 Object Library check box, and then click OK.
    • Close the new module without saving it.
    Type or paste the following procedure:
    ' As soon as you click the command button, start reading records. Because' you've started reading records, update the status caption to READING.Me.Status.Caption = "Reading"' For the currently open form, display a busy/hourglass mouse icon.Screen.MousePointer = 11' Dimension the database and recordset objects for using DAO.    Dim db As DAO.DatabaseDim rs As DAO.Recordset' Set the database object to the currently opened database.' Set the recordset object to the Customers table.        Set db = CurrentDbSet rs = db.OpenRecordset("Customers", dbOpenSnapshot)' Go to the first record in the Customers recordset/table.rs.MoveFirst' While there are still records to be read, display the current customer ' ID number and fill the progress bar to the current record's location, ' based on the total number of records in the Customers recordset/table. While Not rs.EOF    ' Set the form's record ID number text box equal to the current record ' being read.CurrentRecordID = rs!CustomerID' Set the width of the visible (or top) progress bar rectangle.                   ProgressBarB.Width = (ProgressBarA.Width / rs.RecordCount) * rs.AbsolutePosition' Repaint the current form.Me.Repaint' Go to the next record in the Customers recordset/form.rs.MoveNextFor lCounter = 1 To 750000: NextWend' If you're at the end of the Customers recordset/form, then fill the ' progress bar completely and repaint the form.If rs.EOF ThenProgressBarB.Width = rs.RecordCountMe.Repaint' Clear any customer ID information from the form because you're finished.CurrentRecordID = ""' Set the caption for the Status label to DONE.Me.Status.Caption = "Done"' Set the progress bar's width to zero. Repaint the form.ProgressBarB.Width = 0Me.Repaint    End If    ' Close the recordset.rs.Close' Clear the recordset and database objects.Set rs = NothingSet db = Nothing' Set the form's mouse pointer back to the default mouse pointer.Screen.MousePointer = 0
  5. Remove the label for the CurrentRecordID text box.
  6. Make sure that ProgressBarA appears directly over ProgressBarB in the form. To make sure that this happens, click ProgressBarA, and then click Send to Back on the Format menu. Then, click ProgressBarB, and click Bring to Front on the Format menu.
  7. Compile the code, and then save the form.
  8. Open the form in Form view. Note that "Ready" appears in the Status label.
  9. Click the READ button and note that the Status label changes to "Reading." After all the records have been processed, the progress bar is completely filled from left to right, and "Done" appears in the Status label.


back to the top
inf openrecordset dao ado progress bar read connection status meter
Properties

Article ID: 304581 - Last Review: 12/06/2015 04:26:55 - Revision: 4.1

Microsoft Access 2002 Standard Edition

  • kbnosurvey kbarchive kbhowtomaster kbprogramming kbvba KB304581
Feedback