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" & 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 = 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.

---

Database Daily Backup
Days in Month Function
Finding Consecutive Workdays with Query
Transparent Command Buttons
Colorful Command Buttons

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.

---

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

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. Copies db1.mdb file with the original name.


6. Deletes the temporary Database db1.mdb


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




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
                '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:
Exit 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.

---

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

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



10. 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.



11. 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.

---

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