Working with Chart Object in VBA

Introduction.

Working with Microsoft Access Chart Object in VBA is a complicated matter, but interesting too. Once the code is developed it can be used in other Access Databases to prepare Access Graph Charts with the same look and feel in minutes. 

There are several Chart Types, Chart Areas, and numerous Object Groups and Objects to work with. The Range of values that can go into them is too many. There are Access Chart Type-specific properties and value ranges, like the Auto Scaling property that can be used only for 3D Charts.

Note: But first, please ensure that you have attached the following important Reference Library Files to your Database, in order to work with VBA Code without running into Errors.:

Attaching System Reference Library Files

Display the Visual Basic Editor window, by selecting the Visual Basic Editor Option from Tools Menu or Code from View Menu.

Select the References from Tools Menu. Put the check mark on the following Library Files in the Available References Dialogue Control:

1. Microsoft Office 12.0 Object Library
2. Microsoft Excel 12.0 Object Library

These are also necessary when you run Options in the Demo Database, attached for Downloading at the end of this Page.

I did some searching, and experimenting, and created a VBA Routine that can modify the Access Graph Chart Elements with beautiful formatting, to my liking. I hope you will like it too. The sample run result image is given below.

Formatting Chart Elements

The Resizing of the Graph Chart and formatting of several elements, like Chart Title, Chart Axis Titles, Chart Area, Chart PlotArea, and Chart Bars with Gradient color, Legends, Grid Lines, Data Labels, and alignment of Data Labels are done with VBA Code. Placing a Chart Object on the Report with few property changes, as a starter kit, is done manually.

When the Program is run, it gives three options to choose from:

1. Bar Chart
2. Line Chart
3. Pie-chart.

The same Graph Chart is transformed into any one of these Types and the sample values from a table are used for all three. Pie-Chart will only use the first record from the Table and the treatment of values in the pie-chart is different from Bar Chart or Line Chart. Here, it is used only for example purposes, and Pie-chart is not treated separately.

Color Numbers

The following Color index numbers can be used in different combinations in the Program to get different shades of gradients.

Preparing for Trial Run

Follow the few simple steps explained below to get prepared for the trial run.

1. Create a Table with the following Structure and save it with the name Table1.

   

2. Add the sample records with the values shown above.

3. Open a new Report in Design View.
4. Select Object from Insert Menu and select Microsoft Graph 2000 Chart (or Microsoft Graph Chart in MS-Access 2003) from the Object Type List.

   The inserted chart object with default values will be in an activated state, displaying the default values in the Data Grid.

   

5. Click outside the chart on the Detail Section of the Report to de-activate and de-select the Chart.

6. Click on the Chart again to select it and display the Property Sheet (View - - >Properties)
7. Change the following Property Values as given below:
   • Name: Chart1
   • Row Source Type: Table/Query
   • Row Source: Table1
   • Column Heads: Yes

   Even though the following properties can be changed manually, these are changed through the Code:

   • SizeMode : 3 (Zoom)
   • Column Count: Number of Columns in the Source Data
   • Left: 0.2917?
   • Top: 0.2708?
   • Width: 5.5729?
   • Height: 4.3854?

   2D Chart Area Zones

   An MS Access 2D Chart Object has 3 different areas:

   1. the outer Frame
   2. Chart Area where Chart Title, Axis Titles, and Legend Values appear.
   3. Plot Area where the chart Data Series Values, Data Labels, Gridlines, Scale, and Category Values appear in the Bar or Line Charts.

   If the Size Mode property is set to Clip (value 0) then resizing the Chart with the last four values (Left, Top, Width & Height given above) will only change the size of the Outer Frame. The inner Sections' Chart Area and Plot Area will not resize with the change. If it is set to Stretch or Zoom(Values 1 or 3 respectively) then they will resize to fit within the outer frame area. Zoom is a better setting because it will maintain the Chart in the correct aspect ratio when re-sized, without distortion.

   The Column count property value is found from the Row Source Table or Query String.

   We are validating the settings in the Row Source Type property. If it is not set with the value Table/Query then it will change to that value. But, if the Row Source property is not set with a Table Name or valid SQL String then the program will terminate with a message.

8. Save the Report with the Name myReport1.

The VBA Code.

9. Copy and Paste the following VBA Code into the Global Module and save the Module.

   Public Function ChartObject(ByVal ReportName As String, ByVal ChartObjectName As String)
   '------------------------------------------------
   'Author : a.p.r. pillai
   'Date   : June-2008/Revision on 12/2019
   'Source Code : from Microsoft Access Help
   'and modified certain parameters
   '------------------------------------------------
   Dim Rpt As Report, grphChart As Object
   Dim msg As String, lngType As Long, cr As String
   Dim ctype As String, typ As Integer, j As Integer
   Dim db As Database, rst As Recordset, recSource As String
   Dim colmCount As Integer
   Const twips As Long = 1440
   
   'On Error GoTo ChartObject_Err
   
   cr = vbCr & vbCr
   
   msg = "1. Bar Chart" & cr
   msg = msg & "2. Line Chart" & cr
   msg = msg & "3. Pie Chart" & cr
   msg = msg & "4. Quit" & cr
   msg = msg & "Select Type 1,2 or 3"
   
   ctype = "": typ = 0
   Do While typ < 1 Or typ > 4
    ctype = InputBox(msg, "Select Chart Type")
    If Len(ctype) = 0 Then
       typ = 0
    Else
    typ = Val(ctype)
    End If
   Loop
   
   Select Case typ
       Case 4
           Exit Function
       Case 1
          lngType = xlColumnClustered
       Case 2
          lngType = xlLine
       Case 3
          lngType = xl3DPie
   End Select
   
   DoCmd.OpenReport ReportName, acViewDesign
   Set Rpt = Reports(ReportName)
   
   Set grphChart = Rpt(ChartObjectName)
   
   grphChart.RowSourceType = "Table/Query"
   
   recSource = grphChart.RowSource
   
   If Len(recSource) = 0 Then
      MsgBox "RowSource value not set."
      Exit Function
   End If
   
   'get number of columns in chart table/Query
   Set db = CurrentDb
   Set rst = db.OpenRecordset(recSource)
   colmCount = rst.Fields.Count
   rst.Close
   
   With grphChart
       .ColumnCount = colmCount
       .SizeMode = 3
       .Left = 0.2917 * twips
       .Top = 0.2708 * twips
       .Width = 5.5729 * twips
       .Height = 4.3854 * twips
   End With
   
   grphChart.Activate
   
   'Chart type, Title, Legend, Datalabels,Data Table
   With grphChart
        .chartType = lngType
       ' .chartType = xlColumnClustered
       '.AutoScaling = True
       ' only for 3D type
       .HasLegend = True
       .HasTitle = True
       .ChartTitle.Font.Name = "Verdana"
       .ChartTitle.Font.Size = 14
       .ChartTitle.Text = "Revenue Performance - Year 2007"
       .HasDataTable = False
       .ApplyDataLabels xlDataLabelsShowValue
   End With
   
   'apply gradient color to Chart Series
   If typ = 1 Or typ = 2 Then
       For j = 1 To grphChart.SeriesCollection.Count
         With grphChart.SeriesCollection(j)
           '.Interior.Color = RGB(Int(Rnd(j) * 200), Int(Rnd(j) * 150), Int(Rnd(j) * 175))
           .Fill.OneColorGradient msoGradientVertical, 4, 0.231372549019608
           .Fill.Visible = True
           .Fill.ForeColor.SchemeColor = Int(Rnd(1) * 54) + 2
           If typ = 1 Then
              .Interior.Color = msoGradientVertical
           End If
           .DataLabels.Font.Size = 10
           .DataLabels.Font.Color = 3
           If typ = 1 Then
               .DataLabels.Orientation = xlUpward
          Else
               .DataLabels.Orientation = xlHorizontal
           End If
        End With
       Next j
   End If
   
   If ctype = 3 Then
    GoTo nextstep 'skip axes for pie chart
   End If
   
   'Y-Axis Title
   With grphChart.Axes(xlValue)
       .HasTitle = True
       .HasMajorGridlines = True
       With .AxisTitle
           .Caption = "Values in '000s"
           .Font.Name = "Verdana"
           .Font.Size = 12
           .Orientation = xlUpward
       End With
   End With
   
   'X-Axis Title
   With grphChart.Axes(xlCategory)
       .HasTitle = True
       .HasMajorGridlines = True
       .MajorGridlines.Border.Color = RGB(0, 0, 255)
       .MajorGridlines.Border.LineStyle = xlDash
       With .AxisTitle
           .Caption = "Quarterly"
           .Font.Name = "Verdana"
           .Font.Size = 10
           .Font.Bold = True
           .Orientation = xlHorizontal
       End With
   End With
   
   With grphChart.Axes(xlValue, xlPrimary)
        .TickLabels.Font.Size = 10
   End With
   With grphChart.Axes(xlCategory)
        .TickLabels.Font.Size = 10
   End With
   
   nextstep:
   
   With grphChart
       .ChartArea.Border.LineStyle = xlDash
       .PlotArea.Border.LineStyle = xlDot
       .Legend.Font.Size = 10
   End With
   
   'Chart Area Fill with Gradient Color
   With grphChart.ChartArea.Fill
       .Visible = True
       .ForeColor.SchemeColor = 2
       .BackColor.SchemeColor = 19
       .TwoColorGradient msoGradientHorizontal, 2
   End With
   
   'Plot Area fill with Gradient Color
   With grphChart.PlotArea.Fill
       .Visible = True
       .ForeColor.SchemeColor = 2
       .BackColor.SchemeColor = 10
       .TwoColorGradient msoGradientHorizontal, 1
   End With
   
   grphChart.Deselect
   
   DoCmd.Close acReport, ReportName, acSaveYes
   DoCmd.OpenReport ReportName, acViewPreview
   
   ChartObject_Exit:
   Exit Function
   
   ChartObject_Err:
   MsgBox Err.Description, , "ChartObject()"
   Resume ChartObject_Exit
   End Function
   

   Running the Code

10. Open the VBA Module that you have pasted the code if you have closed it.
11. Run the code from the Debug Window (press Ctrl+G to display the Debug Window) using the following syntax:

    ChartObject "myReport1", "Chart1"

    Or call the function from On_Click() Event Procedure of a Command Button on a Form.

12. Select Chart Type 1, 2, or 3 for Bar Chart, Line Chart, or Pie respectively.

The Program will open the Report myReport1 in Design View, modifies the Graph Chart for the selected Type, Saves it, and then Re-opens it in Print Preview. You may minimize the VBA Window (Alt+F11) to view the Chart. Since the Report is saved after the changes, you may open it manually in the design view or Print Preview.

When you run the Code for Bar Chart or Line Chart, the Gradient Scheme Color Codes are selected Randomly, which will give different shades of Gradient Colors every time.

NB: Any suggestions for improvement are welcome.

Download the Demo Database.

Download Demo Database

1. MS-Access and Graph Charts
2. MS-Access and Graph Charts-2
3. Working With Chart Object in VBA
4. Column Chart and VBA
5. Pie Chart Object and VBA
6. Missing Lines in Line-Chart
7. Pie Chart and Live Data on Form
8. Scaling Chart Object
9. Cross-Tab Union Queries for Chart

Read More

Linking with IBM AS400 Tables

Introduction.

We can convert IBM iSeries (AS400) DB2 Table into dBase Format, download, and Link or Import into MS-Access Databases. The downloading procedure must be created and run from AS400 Menus. If it requires several steps before the output file is transferred to the local drive, then it can be automated with Macro Scripts. The keystrokes can be recorded and modified in such a way that the target file goes to a specific location on the Local Machine with a predefined name that remains linked with the Microsoft Access Database.

If you have sufficient Access Privileges to iSeries (AS400) Main Frame Tables you can Link them directly into your MS-Access Database. We are going to look into this method with ODBC based procedure shortly.

Normally, Reports are generated from AS400 and provided to Users as Hard Copies, or converted into the Report Spool File in Text Form, if Soft Copy is requested for. We can download this File either in Text form or upload it into Microsoft Excel (may not Parse the data correctly into Number or Date etc.) with iSeries Report Down-Loader Program. AS400 tables also can be downloaded directly into Microsoft Excel and in this process, if the number of lines is more than 65535 (the limitation of Microsoft Excel Worksheet) it will create more than one Worksheet automatically to accommodate all the data in Excel File.

Linking to IBM iSeries DB2 Tables.

Let us review the steps for Linking IBM iSeries DB2 Tables in MS-Access Database. The example images are created from the Windows2000 Workstation.

Creating ODBC System DSN.

1. Select Start Menu - - > Settings - - > Control Panel - - > Administrative Tools - -> double-Click on Data Sources (ODBC).

   The ODBC Data Source Administrator Settings will be displayed. See the Image given below. The Following Steps will walk you through the procedure:

   

2. Select System DSN Tab on the ODBC Data Source Administrator.

3. Click Add . . . Button to display a list of Data Source Drivers.

   

4. Select Client Access ODBC Driver (32-bit) from the displayed list in the Create New Data Source Control and Click Finish.

   

5. Type a name in the Data Source name Control. I have inserted the name myData as Data Source Name. We have to look for this name when we attempt to link the Table to MS-Access.

6. Click on the Server Tab.

   

7. Type the specific iSeries Folder Name where your data table resides in the Library List Control. If more than one Library File is there, then separate them with Comas.

8. Put the checkmark on the Read-Only (Select statements only) option under Connection Type, to ensure that we have no intention to modify the data in iSeries Table.

9. Click Apply followed by OK Button. The System Data Source Name myData appears in the System DSN Tab. See the image below.

   

10. Click OK to close the ODBC Configuration Main Control (the Apply button remains disabled in this case).

Linking to MS-Access Database.

1. Open your MS-Access Database.
2. Select File - -> Get External Data - - > Link Table or Import Option.
3. Select ODBC Databases in the Files of Type control in the Common Dialog Control and Click Link (or Import) Button as the case may be.

4. Select Machine Data Source Tab on the ODBC Control and find the Data Source Name myData that you have created, select it, and click OK.

   

   You will be prompted for AS400 User-ID and password. Key-in your User ID, Password, and click OK.

   A-List will open up showing all the Table Names available in the AS400 iSeries Machine Prefixed with the Library Name followed by a period.

   

5. Select the Table(s) to Link and Click OK.

   The Fields of the selected Table will be displayed suggesting to highlight one or more fields for indexing if needed.

6. Highlight the field(s), if you would like to create a Unique Index for the Table, otherwise Click OK without selecting any.

The selected Table will be linked (or Imported as the case may be) into your Database.

The AS400 Login Issue

If the table remains linked, whenever you attempt to use the table after opening your MS-Access Database for the first time it will prompt for the AS400 iSeries UserID and Password and after that, the login is valid for the current Session.

If you don't want this to happen in the middle of some processing steps it is better to invoke the login immediately after opening the Database. To do this, create a Query or Form or Report that uses the linked iSeries Table that opens with an Autoexec Macro or the Form in Startup, or even better create a VBA routine to open the linked table which will invoke the login procedure, and the User is prompted for keying in her User ID and Password at the beginning of the current session itself. This will take care of the rest of the Session.

• Days in Month Function
• Finding Consecutive Workdays with Query
• Transparent Command Buttons
• Colorful Command Buttons
• Double Action Command Button

Read More

REPAIRING COMPACTING DATABASE WITH VBA

Introduction.

Repairing and Compacting the Database is an essential function to keep its Size on Disk to the minimum. When you work with the Database MS-Access creates temporary work objects within the Database resulting in the file size keeps expanding.

If the Database is a Single User one, then there isn't much to worry about this issue, because all you have to do is to enable the Compacting on Close feature in the Access Options settings.

Select the Menu Tools - -> Options - - > General Tab and put a checkmark in the Compact on Close option. Every time MS-Access will compact the Database when you close it.

But, if the Database is Shared on Network then enabling this feature can lead to problems. You should have Exclusive Access to the Database to run compacting. When you turn on the Compact on Close feature, it will attempt to run the Compact and Repair procedure but will fail if others are using it. We don't know what will be the impact of these repeated attempts and failures, from different sharing points, on the Database. Enabling the Exclusive Access Option (Tools - -> Security - -> User and Group Permissions - -> Permissions Tab) will not allow concurrent sharing.

Why MS-Access insists on Exclusive Access to the Database for compacting is because it deletes the original Database in this process and re-creates it. Check the following steps taken by the compacting Procedure:

Database Compacting Steps.

1. Select Tools - -> Database Utilities - - > Compact and Repair Database. Closes the Current Database.
2. Creates a temporary Database with the name db1.mdb in the current folder.
3. Transfers all the Objects (Tables, Forms, Reports, etc.), except the work objects, into db1.mdb.
4. Deletes the original Database.
5. Renames db1.mdb file to the original name.
6. Opens the newly created Database.

When No Database is active.

If a database is not active when you select the option in Step-1 you are asked to select the Source Database from the disk and will prompt for a Target Name to save the compacted copy in your preferred location. It will not re-create it with the original name. It is up to you to decide what to do with the old and new Database Files.

To do compacting on the Server, User-level Disk Quota Allocation is also a matter of concern. The User should have double the size of the Database or more free disk space available under his allocated Disk Quota.

In these circumstances, a separate Compacting Utility Program is useful when you have several Databases shared with the Network. We are going to create such a Database with VBA Programs that can compact one or more Databases easily. The Program follows the same route map described in Step-1 to 7 above, and with some changes in the way the procedure is Run.

The Compacting Utility, that we are going to create, has the following advantages:

• Uses Local Disk Space for Work-File, which will speed up the process, and extra space on the server is not required for this purpose.
• Can select more than one Database for compacting.
• Takes a safe Backup on Local Drive besides compacting.
• No failures due to the non-availability of enough Disk Space under the User's Disk Quota.

We will create a small Database with a Table to hold a list of Database Path Names, a Form, and two VBA Programs on the Form Module.

The Design Task

1. Create a new Database with the name CompUtil.mdb.

2. Create a Table with the following structure.

   Table: FilesList

   Field Name	Type	Size
   ID	AutoNumber
   dbPath	Text	75

3. Save the Table with the name FilesList and key in the full path names of your Databases running on the Server and close the table. Do not use UNC ("\\ServerName\FolderName\DatabaseNname") type addressing method.

   

4. Open a new Form and create a List Box using the FilesList Table. See the design given below. Draw two Label Controls below the List Box and two Command Buttons below that, side by side.

   

5. Resize the Controls and position them, to look almost like the design given above. The finished design in Normal View is given below. The Labels below the List Box are kept hidden and will appear only when we run the Program.

   

   Change the Property Values of the Form and Controls so that you can make them look exactly like the design given above.

6. Click on the List Box and display the property sheet (View - - > Properties).

7. Change the List Box's Property Values as given below:

   • Name: dbList
   • Row Source Type: Table/Query
   • Row Source: SELECT [FilesList].[ID], [FilesList].[dbPath] FROM [FilesList]
   • Column Count: 2
   • Column Heads: No
   • Column Widths: 0.2396";1.4271"
   • Bound Column: 2
   • Enabled: Yes
   • Locked: No
   • Multiselect: Simple
   • Tab Index: 0
   • Left: 0.3021"
   • Top: 0.7083"
   • Width: 3.2083"
   • Height: 1.7708"
   • Back Color: 16777215
   • Special Effect: Sunken

8. Resize the child Label Control, attached to the List Box, to the same size and position it above the List Box. Change the Caption to Database List.

9. Click on the first Label Control below the List Box, display the Property Sheet, and change the following Properties:

   • Name: lblMsg
   • Visible: No
   • Left: 0.3021"
   • Top: 2.5"
   • Width: 3.2083"
   • Height: 0.5"
   • Back Color: 128
   • Special Effect: Sunken

10. Display the Property Sheet of the second Label Control and change the following Properties:

    • Name: lblstat
    • Visible: No
    • Left: 0.3021"
    • Top: 3.0417"
    • Width: 3.2083"
    • Height: 0.1667"
    • Back Style: Transparent
    • Back Color: 16777215
    • Special Effect: Flat
    • Border Style: Transparent

11. Change the following properties of the left-side Command Button:

    • Name: cmdRepair
    • Caption: Repair/Compact
    • Tab Index: 1
    • Left: 0.3021"
    • Top: 3.25"
    • Width: 1.4271"

    • Height: 0.2292"

12. Change the following properties of the right-side Command Button:

    • Name: cmdClose
    • Caption: Quit
    • Tab Index: 1
    • Left: 2.0833"
    • Top: 3.25"
    • Width: 1.4271"
    • Height: 0.2292"

13. Change the Properties of the Form. Click on the left top corner of the Form where the left-side and Top design guides (Scales) meet. When you click there a blue square will appear, indicating that the Form is selected. Display the Property Sheet and click on the All Tab, if that is not the current one, change the following Properties:

    • Caption: External Repair/Compact Utility
    • Default View: Single Form
    • Views Allowed: Form
    • Allow Edits: Yes
    • Allow Deletions: No
    • Allow Additions: No
    • Data Entry: No
    • Scroll Bars: Neither
    • Record Selectors: No
    • Navigation Buttons: No
    • Dividing Lines: No
    • Auto Resize: Yes
    • Auto Center: Yes
    • Pop up: Yes
    • Modal: Yes
    • Border Style: Dialog
    • Control Box: Yes
    • Min, Max Buttons: None
    • Close Button: Yes
    • Width: 3.9063"

14. Click on the Detail Section of the Form, and change the Height Property:

    • Height: 3.7917"

15. Create a Header Label at the top with the Caption Compacting Utility and change the Font Size to 18 Points or size to your liking.

    NB: If you would like to create a Heading with 3D style characters like the sample shown above, visit the Page Create 3D Heading on Forms and follow the procedure explained there. This you can do it later also.

16. Select the Rectangle Tool from the Toolbox and draw a Rectangle around the Controls as shown on the Design.
    

    Form Class Module Code.

17. Display the VBA Module of the Form (View - - > Code), Copy, and Paste the following code into it, and save the Form with the name Compacting.

    Private Sub cmdClose_Click()
    If MsgBox("Shut Down...?", vbYesNo + vbDefaultButton2 + vbQuestion, _"cmdQuit_Click()") = vbYes Then
        DoCmd.Quit
    End If
    End Sub
    
    Private Sub cmdRepair_Click()
    Dim lst As ListBox, lstcount As Integer
    Dim j As Integer, xselcount As Integer
    Dim dbname As String, t As Double, fs, f
    Dim ldbName As String, strtmp As String
    
    'create a temporary folder C:\tmp, if not present
    On Error Resume Next
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder("c:\tmp")
        If Err = 76 Or Err > 0 Then
           Err.Clear
           fs.createfolder ("c:\tmp")
        End If
    
    On Error GoTo cmdRepair_Click_Err
    
    Me.Refresh
    Set lst = Me.dbList
    lstcount = lst.ListCount - 1
    
    xselcount = 0
    For j = 0 To lstcount
    If lst.Selected(j) Then
        xselcount = xselcount + 1
    End If
    Next
    
    If xselcount = 0 Then
       MsgBox "No Database(s)Selected."
       Exit Sub
    End If
    
    If MsgBox("Ensure that Selected Databases are not in Use. " _
    & vbCrLf & "Proceed...?", vbYesNo + vbDefaultButton2 + vbQuestion, "cmdRepair_Click()") = vbNo Then
       Exit Sub
    End If
    
    For j = 0 To lstcount
        If lst.Selected(j) Then
          dbname = lst.Column(1, j)
           dbname = Trim(dbname)
           ldbName = Left(dbname, Len(dbname) - 3)
           ldbName = ldbName & "ldb" 'for checking the presense of lock file.
           If Len(Dir(ldbName)) > 0 Then 'database is active
              MsgBox "Database: " & dbname v vbCrLf & "is active. Skipping to the Next in list."
              GoTo nextstep
           End If
    
           If MsgBox("Repair/Compact: " & dbname & vbCrLf & "Proceed...?", vbQuestion + vbDefaultButton2 + vbYesNo, "cmdRepair_Click()") = vbYes Then
                Me.lblMsg.Visible = True
                Me.lblStat.Caption = "Working, Please wait..."
                Me.lblStat.Visible = True
                DoEvents
    
                dbCompact dbname 'run compacting
    
                Me.lblStat.Caption = ""
                DoEvents
    
    nextstep:
                t = Timer
                Do While Timer <= t + 7 'delay loop
                   DoEvents 'do nothing
                Loop
            End If
        End If
    Next
    
       Me.lblMsg.Visible = False
       Me.lblStat.Caption = ""
       Me.lblStat.Visible = False
    
    strtmp = "c:\tmp\db1.mdb" 'Delete the temporary file
    If Len(Dir(strtmp)) > 0 Then
      Kill strtmp
    End If
    
    Set fs = Nothing
    Set f = Nothing
    Set lst = Nothing
    
    cmdRepair_Click_Exit:
    Exit Sub
    
    cmdRepair_Click_Err:
    MsgBox Err.Description, , "cmdRepair_Click()"
    Resume cmdRepair_Click_Exit
    End Sub
    

    
    

    Private Function dbCompact(ByVal strdb As String)
    Dim t As Long
    Dim xdir As String, strbk As String
    Const tmp As String = "c:\tmp\"
    
    On Error GoTo dbCompact_Err
    
    If Len(Dir(tmp & "db1.mdb")) > 0 Then
        Kill tmp & "db1.mdb"
    End If
    
    t = InStrRev(strdb, "\")
    If t > 0 Then
       strbk = Mid(strdb, t + 1)
    End If
    strbk = tmp & strbk
    
    xdir = Dir(strbk)
    If Len(xdir) > 0 Then
       Kill strbk
    End If
    'Make a Copy in c:\tmp folder as safe backup
    Me.lblMsg.Caption = "Taking Backup of " & strdb & vbCrLf _
    & "to " & tmp
    DoEvents
    
       DBEngine.CompactDatabase strdb, strbk
    
    Me.lblMsg.Caption = "Transferring Objects from " & strdb & vbCrLf _
    & "to " & tmp & "db1"
    DoEvents
    
       DBEngine.CompactDatabase strdb, tmp & "db1.mdb"
    
    ' Delete uncompacted Database and Copy Compacted db1.mdb with
    ' the Original Name
    
    lblMsg.Caption = "Creating " & strdb & " from " & tmp & "db1.mdb"
    DoEvents
    
        If Len(Dir(strdb)) > 0 Then
            Kill strdb
        End If
    
        DBEngine.CompactDatabase tmp & "db1.mdb", strdb
    
    lblMsg.Caption = strdb & " Compacted Successfully." & vbCrLf & "Database backup copy saved at Location: " & tmp
    DoEvents
    
    dbCompact_Err_Exit:
    Exit Function
    
    dbCompact_Err:
    MsgBox Err & " : " & Err.Description, , "dbCompact()"
      Resume dbCompact_Err_Exit
    End Function
    

    You can set the Compacting Form to open at Startup. Select Startup from Tools Menu. Select the Form Compacting in the Display Form/Page Control. If you want to hide the Database Window then remove the checkmark from the Display Database Window Option.

    The Trial Run

18. Open the Compacting Form in Normal view. Select one or more Databases from the List Box for Compacting.
19. Click the Repair/Compact Command Button.

If you are running the Program for the first time it will check for the presence of a Folder c:\tmp. If it is not present, then it will be created. This folder will be used as Work-Area for the Compacting Utility irrespective of from where you are running this Program (from Server or from Local Drive) and places the Backup Copies of the Compacted Databases.

The program runs a check on each selected database before running the Compacting Procedure to re-confirm that nobody is using it. If it does, then it will display a message as such and will not Compact that Database.

The Label Controls that we have created and kept hidden under the List Box, will be visible now. It will be updated with the program's current activity information, at different stages of the Compacting Procedure.

Any suggestions for improvement of this Program are welcome.

The Demo Database is upgraded to MS-Access Version 2016 and can be downloaded from the link given below.

Download Repairing.zip

• Transparent Command Buttons
• Colorful Command Buttons
• Double Action Command Button
• Sending Alerts to Workstations
• Refresh Dependent Combo Box Contents

Read More

Database Open Close Event Alerts

Introduction.

Your application is installed on a Network and shared by different Users. You would like to monitor the activities of this Database, like which User Opens or Closes it, and at what time? Or your database is not a Secured one (which means anybody can open it) and is installed on a Network, for the benefit of a few trusted Users. You already knew their Workstation IDs/Network IDs, but you would like to know someone else is opening it to explore and when they do catch them red-handed. Warn the unauthorized intruder immediately by sending a message to his Workstation informing him that he is not in the permitted zone and requesting him to close the database immediately. He will know that you are observing the activities of over your Database and will not attempt a second time when you are around! If he responded positively by closing the Database then you will get an Alert message from the Database transmitted by the Closing Event.

Or you are doing some maintenance work on the Database and you would like to know if someone opens it in the middle of your work. Or you are waiting for the Current User to close the Database to make some changes. Or you would like to send a message to the Active User (without lifting your Telephone), asking her to shut down the Application so that you can fix some bugs or want to inform her about the completion of the maintenance tasks by sending a message to her PC.

It is interesting and very useful to automate some of these tasks. Get timely feedback from your Databases so that you can plan your actions and execute them conveniently, keeping in touch with your Users and Databases all the time. 

All these tricks are possible when you get Alert Messages from your Databases to your PC immediately after the User(s) opens or closes it. Visit the Page Titled: Sending Alerts to Workstations based on process-related activities within a Database. Once you get the User's Workstation ID you can send messages back to them from your Desktop, if you need to communicate with them. Read the earlier Article Titled: Who is Online, where we have approached some of these issues differently.

You have several Applications on the Network. Alert Messages coming to you are too many, then switch them Off or keep a few cases On. You don't need to touch any of your Databases at all for this changeover.

After going through the above introduction you may be interested to have a look at it and probably assume that it is a big project for implementing all of these features. Or you have already come to the conclusion that the whole thing is going to be so complicated you don't want to touch it at all. Relax, it is so simple you will love it, I am sure about it. If you have 10 minutes of your time to spare, you can implement it and try it out. All you need is a Windows Network in your Office.

The Alert Message Contents

The Alert Messages must be loaded with the following information when received:

• User Workstation-ID
• Date and Time
• Database PathName
• Status info (Open/Close)
• User Network ID
• MS-Access User ID

Our Network Administrator was kind enough to set my Last Name (PILLAI) as my WorkStation ID. The images of sample Network Messenger Alerts that comes to my WorkStation are given below. 

The Database Open Event Alert Message.

Database Close Event Alert Message

All we need for this interesting Trick is a VBA Routine (given below) and 3 Text Files. The VBA Program must be copied to the Global Module of your Database and call it from the Main Switchboard (Control Screen) in the On Open and On Close Event Procedures.

If you have a Library Database already linked with your Applications from a Common Location on the Server, then it makes it much easier to implement by placing the main Code in the Library Database only. Then you need to install only two lines of code in the On Open and On Close Event Procedures in the Main Switchboard Form (Control Screen) in your Databases.

Prepare for a Trial Run

But for now, let us try it in one of your Databases installed in Network Location shared by Users. When you are ready to implement it in other Databases you can do so following the same procedure. The Names and usage of the three Text files (all of them are Optional), created for your control purposes, are given below:

File-1: AccsCtrl.txt - List of Databases where the Message Alert Programs are running.

File-2: AccLog.txt - Message Alert Log entries for reference purposes.

File-3: Message.BAT - For sending messages, and alerts from your PC to your Database Users' Workstation.

1. Copy and Paste the following VBA Code into a Global Module of your Project or Library Database and save it:

   The OpenCloseAlert() Function

   Public Const accmsgtxt As String = "h:\inhsys\comlib\AccsCtrl.txt"
   Public Const acclogtxt As String = "h:\inhsys\comlib\AccsLog.txt"
   
   Public Function OpenCloseAlert(ByVal mstat As String)
   '-----------------------------------------------------------
   'Author: a.p.r. pillai
   'URL   : www.msaccesstips.com
   'Date  : 20/05/2008
   'All Rights Reserved by www.msaccesstips.com
   '-----------------------------------------------------------
   Dim t As Double, WorkStationID As String * 15
   Dim netUserName As String * 15, accUserName As String * 15
   Dim str As String, loc As Integer, AlertMsg As String
   Dim AlertFlag As String,  flag As Boolean
   Dim chk As String, dbpath As String, m_stat As String * 1
   Dim status As String * 8, dttime As String * 22
   
   On Error GoTo OpenCloseAlert_Err
   
   dbpath = CurrentDb.Name
   m_stat = mstat
   str = Dir(accmsgtxt)
   If Len(str) = 0 Then
   'accsctrl.txt File Not Found   
   'ignore ON/OFF flag checking   
   'proceed to Alert Message Section
      GoTo nextstep
   End If
   
   'open the control file and check the Status
   Open accmsgtxt For Input As #1
   AlertFlag = " ": flag = False
   Do While Not EOF(1)
   Input #1, chk
   If flag = False And Left(chk, 5) = "ALERT" Then
      flag = True
      chk = UCase(chk)
      loc = InStr(6, chk, "OFF")
      If loc > 0 Then     'Turn off Alerts from All Databases
        Exit Function
      Else
        GoTo readnext
      End If
   End If
   
   loc = InStr(1, chk, dbpath)
   If loc > 0 Then'database file found, take the flag value
     AlertFlag = Right(Trim(chk), 1)
     Exit Do
   End If
   readnext:
   Loop
   Close #1
   
   If AlertFlag = "0" Or Len(AlertFlag) = 0 Then   'Turn off Alert in this Database
      Exit Function
   End If
   
   nextstep:
   
   WorkStationID = Environ("COMPUTERNAME")
   netUserName = Environ("USERNAME")
   accUserName = CurrentUser
   
   status = IIf(Left(mstat, 1) = "O", "OPEN", "CLOSE")
   dttime = Format(Now(), "mm/dd/yyyy hh:nn:ss")
   
   AlertMsg = LCase(CurrentDb.Name) & " OPEN/CLOSE Status : " & vbCr & vbCr & "STATUS............: " & IIf(Left(mstat, 1) = "O", "OPEN", "CLOSE") & vbCr & "WORKSTATION ID....: " & WorkStationID & vbCr & "NETWORK USERNAME..: " & netUserName & vbCr & "ACCESS USERNAME...: " & accUserName & vbCr 
   
   'insert your Workstation ID replacing the text
    Call Shell("NET SEND  " & AlertMsg, vbHide)
   
   str = Dir(acclogtxt)
   If Len(str) = 0 Then
     GoTo OpenCloseAlert_Exit
   Else
     Open acclogtxt For Append As #1
     Print #1, status; dttime; WorkStationID; netUserName; accUserName; CurrentDb.Name
     Close #1
   End If
   
   OpenCloseAlert_Exit:
   Exit Function
   
   OpenCloseAlert_Err:
   MsgBox Err.Description, , "OpenCloseAlert()"
   Resume OpenCloseAlert_Exit
   End Function
   

   The first two lines in the above code must be placed on the Global Declaration area of Module, below the Option Declarations as indicated below.

   Option Compare Database
   Option Explicit
   Public Const accmsgtxt As String = "h:\inhsys\comlib\AccsCtrl.txt"
   Public Const acclogtxt As String = "h:\inhsys\comlib\AccsLog.txt"
   

2. Copy the following lines in the VBA Module of the Main Switchboard (Control Screen) Form or copy only the middle line into their respective Event Procedures, if they already exist:

   Private Sub Form_Open(Cancel As Integer)
        OpenCloseAlert "Open"
   End Sub
   
   Private Sub Form_Close()
        OpenCloseAlert "Close"
   End Sub
   

3. We will create the Text Files AccsCtrl.txt and AccsLog.txt in a common location on the Server, where all your Application Users have Access Rights. Change the Server Path h:\inhsys\comlib in the Constant Declaration lines to the common location Path on your Server. Do not use UNC like Path specification \\Server\Folder\database.

   Note: The term Path represents the location address of a file without the File Name, but Path Name denotes the location Address including File Name and File Name Extension.

   Create Text Files

4. Open Windows Notepad (Start - - > Program Files - - > Accessories - - > Notepad). Copy and Paste the following lines into the blank Notepad File:

   ALERT=ON
   H:\FolderName\FolderName\MYDB1.MDB=1
   G:\FolderName\MYDB2.MDB=0
   

5. Change the second and third lines to point to your Databases on a Server already in use by others. Save the Text File with the name AccsCtrl.txt at the Location on the Server specified in the constant declaration line path given above. Databases can be in any location on the Server.

6. Select New from Notepad File Menu to open a new text File. Save the empty text file in the same location on the Server with the name AccsLog.Txt. The Log details of the Alert Messages that you receive will be saved in this file too. You can open and view history when needed.

   This File can be created through code, if not present. But, I thought it was better to leave the option for you to decide, whether you need it or not. If you have several Applications running with this Alerts mechanism the log file can grow very fast and can shrink the allocated space on your Disk Quota. You may try this out and watch the frequency of events. You can periodically review the contents and delete old entries or copy them into another file on Local Drive.

7. Create Desktop Shortcuts to these Files so that you can open them quickly, and edit or view the contents when needed. Right-Click on the Desktop, highlight New and select Shortcut from the displayed menu. Browse to the location of AccsCtrl.txt file and select it, click OK, click Next then click Finish.

   Let us look into the Logic of the Main Program OpenCloseAlert() and how the File AccsCtrl.txt contents are interpreted and control the execution of the Program.

   • If you have not created the File AccsCtrl.txt at all (as per the declaration Public Const accmsgtxt As String = "h:\inhsys\comlib\AccsCtrl.txt") in the specified location, still you will receive the Alert Messages but you cannot control it by switching Off or On at will.

   • If you have created the File AccsCtrl.txt but forgot to add the Database Path Name with flag 1 at the end, then the Alerts will not trigger for that Database.

   • If the first line in this file is ALERT=OFF then further lines in this file will not be processed and turn Off Alerts from All Databases.

   • Any value after the word ALERT= other than OFF (ON or anything else except space) will be ignored and will proceed with the individual Database level flag checking. We opted out of checking the ON flag setting because we are doing it at the individual database level to trigger the Alerts. Besides, this line was introduced to switch off all alerts from all databases with one change. If you prefer to control with an individual file level flag setting (which is explained below) you don't need to put this line at all in this File.

   • If you place the ALERT=OFF statement after a few lines of the Database Path Names (see Step-4) in the Text File then the program always checks the lines above this statement and executes the program OpenCloseAlert() depending on the logic setting (1 or 0) at the Database level. But Path Names after this line are ignored and never checked.

   • We have added two sample Database Path Names after the first line with Alert status flag =1 and =0 at the end (1= Alerts ON, 0=Alerts OFF). You may add any number of Databases in this way with flag value 0 or 1 after the equal (=) sign, after installing the Program OpenCloseAlert() in the Database.

   • When the program encounters the Database Path Name it extracts the last character from that line to check the status. Ensure that you are not adding any other characters after 0 or 1 except white spaces.

   • If the flag is 0 then Alert Message will be turned off for that Database. Any other value will send it.

   • By adjusting the values in this file you can control Alerts coming to you without touching any of your Databases.

   • If you have not created the Logfile AccsLog.txt the program will not generate any error, but the message history is not maintained. You can delete old records from this file to keep the file size small on the Server.

   • Create copies of these Files periodically as a backup and keep them in a safe place for restoring, if the active one got corrupted or deleted by mistake.

   The DOS Batch File.

8. We will create a DOS Batch File on your Desktop for sending messages to your Database Users as and when the need arises. Open Windows Notepad and write the following line:

   NET SEND WorkstationID messagetext
   

9. Note: The NET SEND Command works under Windows XP only. Later versions of Windows use the MSG Command.  C:\>MSG /? - The Command displays the details of different Parameters which can be used with this Command.

   Write the network of a known machine in place of the text 'WorkstationID' and a test message, replacing the text 'message text--.'

10. Select Save As from the File menu and Browse to your Desktop (C:\Documents and Settings\your network Id\Desktop) and save it with the name MESSAGE.BAT. Select All Files in the Save as Type control before saving it. The MESSAGE.BAT name will appear on your Desktop.

11. Since this file is on your Desktop you can always right-click on it, select Edit from the displayed menu and open it in Notepad and change the WorkStationID and message text and close it.

12. Double-click on the Desktop icon of MESSAGE.BAT File to send the message after making changes. Batch Files are treated as programs and if you face any security issues please check with your System Administrator. Alternatively, you can send Network messages directly from DOS Prompt. Open Run from Start Menu. Type cmd and click OK. Type the NET SEND command followed by WorkstationID and your Message Text, giving space between the WorkstationID and the Message, then press Enter Key.

When you implement this method correctly, you can keep track of your Databases and the log file (AccsLog.Txt) will record the history of activities. If you need, you can link this Text File to a Database for analysis purposes and find out the frequency and User-level time spent, and so on.

When you communicate with your Database Users asking them to open or close the Database they will also know that you are watching over their activities. You can open and check the AccsLog.Txt file contents to find out who is active and for how long etc.

You must consider securing your Databases for the internal security of Objects and for regulating access rights to a different category of Users if you have not done that so far. Read the Pages Microsoft Access Security for details on this very powerful feature. It may take some time to understand all the twists and turns of this feature. But, if you start using it you can learn it over a period of time by experimenting. Believe me, if you are a serious MS-Access Developer you must learn it. Doesn't matter how long it takes to understand the rules. You can Post your doubts over Security issues in the Comments Section. I will extend all possible help that I could give you.

The Trial Run.

Now the stage is all set for trial Runs. Ask one of your Application Users to open the above Database from their Workstation. You must get a similar Popup message, like the sample image given above, during both Opening and Closing events. Ask more Users to open the same Database from their PCs too. You must get Alerts from each instance of the same Database.

You can implement the same Code in other Databases also and add their Path Names in the AccsCtrl.txt file.

• Colorful Command Buttons
• Double Action Command Button
• Sending Alerts to Workstations
• Refresh Dependent Combo Box Contents
• Filtering Data for Different Users

Read More