Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Working with Chart Object in VBA

Working with Chart Object in VBA is a complicated matter, but interesting too. Once the code is developed it is very useful and can be used across Applications to prepare 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, are too many. There are Chart Type specific properties and value-ranges, like the Auto Scaling property that can be used only for 3D Charts.

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

Resizing of Graph Chart and formatting of several elements, like Chart Title, Axis Titles, Chart Area, Plot Area 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, are 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 is used for all the three. Pie-Chart will only use the first record from the Table and the treatment of values in a 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.

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

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 Code:

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

    A 2D Chart Object has 3 different areas:

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

    If the SizeMode property 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 proportions 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 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.

  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
    '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" & crmsg = msg & "2. Line Chart" & crmsg = msg & "3. Pie Chart" & crmsg = msg & "4. Quit" & crmsg = 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 = xl3DColumnClustered
        '.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
    
    '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
    

  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 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 the 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 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 Demo Database
Share:

Linking with IBM AS400 Tables

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 being transferred to local drive then it can be automated with Macro Scripts. The key strokes 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 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 are more than 65535 (the limitation of Microsoft Excel Worksheet) it will create more than one Worksheet automatically to accommodate all the data into Excel File.

Later on, we will look into a VBA Program to upload the AS400 Report Spool File directly into Microsoft Access Table, after removing Report Headers and other unwanted lines.

But for now, let us go through the steps for Linking IBM iSeries DB2 Tables into MS-Access Database. The example images are created from 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 is there then separate them with Comas.
  8. Put a check mark 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 following 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.

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 using the linked iSeries Table that opens with an Autoexec Macro or the Form in Startup, or even better write a VBA routine to open the linked table which will invoke the login 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.

Share:

REPAIRING COMPACTING DATABASE WITH VBA

As far as Microsoft Access is concerned compacting the Database is an essential function to keep the Database size to the minimum. When you work with the Database MS-Access creates temporary work-objects within the Database resulting the file size to expand. We can display some of these temporary objects with VBA Code. Copy and paste the following Code into the Global Module of a Database that was not compacted recently. Press Ctrl+G to open the Debug Window (Immediate Window), click somewhere within the code and press F5 to execute it.

Public Sub tmpObjects()
Dim ctr As Container, doc As Document
Dim cdb As Database, item

Set cdb = CurrentDb
Set ctr = db.Containers("tables")
For Each item In ctr.Documents
   If Left(item.Name, 1) = "~ " Then
      Debug.Print item.Name
   End If
Next
Set db = Nothing
Set ctr = Nothing
End Sub

You will find a listing of temporary work-files, similar to the sample lines given below, in the Debug Window:

~sq_cBRVISIT
~sq_cBR
~sq_cBRVISIT
~sq_cBRV_Detail
~sq_cBRVISIT
~sq_cEMPCOD
~sq_cBRVISIT
~sq_cYRMTH

After compacting the Database if you run this code again then these type of objects will not appear at all.

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

Select the Menu Tools - -> Options - - > General Tab and put a check mark 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 of the Database to run compacting. When you turn on the Compact on Close feature it will attempt to do that 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 of the Database for compacting because it deletes the original Database in this process and re-creates it. Check the following steps taken by the compacting Procedure:

  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. Re-opens the newly created Database.

If a database is not active when you select the option in Step-1 you are asked to select the Source Database from 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 files.

To do compacting on 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 on 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 but with some change in the way the procedure is Run.

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

  • Uses Local Disk Space for Work-File, which will speed up the process, and extra space on 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 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.

  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 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. Re-size 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. Re-size 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 : cmdRun
    • 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, and 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, 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 to a 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.
  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 cmdRun_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 cmdRun_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, "cmdRun_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 & vbCrLf & "is active. Skipping to the Next in list."
              GoTo nextstep
           End If
    
           If MsgBox("Repair/Compact: " & dbname & vbCrLf & "Proceed...?", vbQuestion + vbDefaultButton2 + vbYesNo, "cmdRun_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 + 5 'delay loop
                   Doevents 'do nothing
                Loop
            End If
        End If
    Next
    
       Me.lblMsg.Visible = False
       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
    
    cmdRun_Click_Exit:E
    xit Sub
    
    cmdRun_Click_Err:
    MsgBox Err.Description, , "cmdRun_Click()"
    Resume cmdRun_Click_Exit
    End Sub
    
    Private Function dbCompact(ByVal strdb As String)
    Dim ErrLoop As Error, t As Long
    Dim xdir As String, strbk As String
    Const tmp As String = "c:\tmp\"
    
    On Error GoTo Err_Compact
    
    If Dir(tmp & "db1.mdb")  "" 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 Dir(strdb)  "" 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
    
    Err_Compact_Exit:
    Exit Function
    
    Err_Compact:
      For Each ErrLoop In DBEngine.Errors
        MsgBox "Compacting Unsuccessful!" & vbCr & "Error number: " & ErrLoop.Number & vbCr & ErrLoop.Description
      Next
     ErrLoop
      Resume Err_Compact_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 check mark from Display Database Window Option.

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

Share:

Database Open Close Event Alerts

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 installed on Network for the benefit of few trusted Users. You already knew their Workstation IDs/Network IDs, but you would like to know some one else opens it to explore and when they do catch them red handed. Warn the unauthorized intruder immediately by sending a message to his Workstation informing that he is not in the permitted zone and request him to close it immediately. He will know that you are on watch 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.

You are doing some maintenance work on the Database and you would like to know if some one opens it in the middle of your work. Or you are waiting for the Current User to close the Database to do 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 feed back 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 Users 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 few cases On. You don't need to touch any of your Databases at all for this change over.

After going through the above introduction you may be interested to have a look at it and probably assuming that it is a week-long Project to implement all of this. Or you 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 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 :

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 Server, then it makes 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.

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 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:
    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 Module's Declaration area 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 exists:
    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 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.

  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 Server already in use by others. Save the Text File with the name AccsCtrl.txt at the Location on Server specified in the constant declaration line path given above. Databases can be in any location on Server.
  6. Select New from Notepad File Menu to open a new text File. Save the empty text file on the same location on 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 the history when needed.

    This File can be created through code, if not present. But, I thought it is better to leave the option to 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, 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 controls 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.
    • 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 turns 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 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 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 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 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 Log file 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 Server.
    • Create copies of these Files periodicaly as a backup and keep it in a safe place for restoring it, if the active one got corrupted or deleted by mistake.
  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. Write the NetworkID of a known machine in place of the text 'WorkstationID' and a test message replacing the text 'messagetext--.'
  10. Select Save As from 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 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 a space between the WorkstationID and the Message, then press Enter Key.

When you implement this 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 internal security of Objects and for regulating access rights to 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 twist 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.

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.

Share:

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands ms-access functions msaccess functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security Array Custom Functions Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Variables Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts