MS Access – VBA – Close All Open Forms

In some cases it may be useful to close all the forms within a database.

Why you might ask, one example would be to ensure you close all connection to the back-end prior to working with it.

'---------------------------------------------------------------------------------------
' Procedure : CloseAllOpenFrms
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Close all the currently open forms in the database
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Usage:
' ~~~~~~
' Call CloseAllOpenFrms
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2015-02-17                  Initial Release
'---------------------------------------------------------------------------------------
Function CloseAllOpenFrms()
    On Error GoTo Error_Handler
    Dim DbF                   As Access.Form
    Dim DbO                   As Object
 
    Set DbO = Application.Forms    'Collection of all the open forms

    For Each DbF In DbO    'Loop all the forms
            DoCmd.Close acForm, DbF.Name, acSaveNo
    Next DbF
 
Error_Handler_Exit:
    On Error Resume Next
    Set DbF = Nothing
    Set DbO = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: CloseAllOpenFrms" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Blocking SPAM from Known Domains in Outlook

As per my previous posting on the subject, for some reason, I have been getting hit recently by SPAM.

HARP Refinance <HARPRefinance@businessthere.eu>
Sitting Solution <SittingSolution@pappend.xyz>
SittingSolution <SittingSolution@lendorate.xyz>
BizCashPros <bizcashpros@fuud-eaat.eu>
Diet Notice <DietNotice@laseral.xyz>
Diet Notice <DietNotice@dashend.xyz>
Diet Notice <DietNotice@pemender.xyz>
Diet Notice <DietNotice@huffinter.xyz>
SittingSolution.com <SittingSolution.com@jakery.xyz>
SittingSolution.com <SittingSolution.com@menastad.xyz>
Curves <Curves@lewaster.xyz>
Curves <Curves@mengert.xyz>
6MinutestoSkinny <6MinutestoSkinny@laserbym.xyz>
6MinutestoSkinny <6MinutestoSkinny@lefferal.xyz>
and so on…

Now, originally, I would simply click on the Junk e-mail option to Block Sender.  The issue with this is that these SPAMmers never use the same e-mail account twice, so this is completely ineffective.

So by examining the sources of the problem, we can very quickly find a nice pattern,  all the SPAM is comming from .xyz and .eu domains.  Knowing this we can use Outlook’s Blocked Senders to build a simple pattern to block them all!  Here’s how it is done.

On the Home tab on the Ribbon, click on the Junk drop-down

Outlook 2013 - RibbonClick on the Junk E-mail Options…

Outlook 2013 - Ribbon - Junk E-mail Options MenuGoto the Blocked Senders Tab, followed by the Add… button

Outlook 2013 - Junk E-mail Options DialogThen simply add the domain suffix for the domain to block in the provided textbox.

Outlook 2013 - Junk E-mail Options Add address or domain to blocked sender listingFinally simply click OK twice to return back to Outlook.

 

It’s that easy.  Since having taken this step, I no longer have to deal with any of the garbage sent by these perpetual SPAMmers as their e-mails go directly into my Junk E-mail folder.  Life is good once again!

 

Important Note:

  • In this example I am blocking outright any e-mail from a .xyz or .eu domain.  That said, you need to carefully examine which domains are SPAMming you and act accordingly by using their suffix.
  • Blocked e-mails are not deleted, but rather moved directly to the Junk E-mail folder.  As such, you should review the content of that folder from time to time to ensure no legitimate e-mails end up in there.
  • At any time you wish, you can go back into the Blocked Senders Tab of the Junk E-mail Options… dialog and highlight an entry and simply delete it by clicking on the Remove button.  So nothing is permanent and you can undo this process at any time.

Stellar Phoenix Access Database Repair Review

Stellar Phoenix Access Database Repair

I was approached by Stellar Phoenix to review their Access Database Repair software.

After performing a few tests, here are my thoughts on the matter:

Password Encrypted Files

Stellar Phoenix Access Database Repair - Not a Valid MS Access fileThe first hurdle that I faced was the fact that I encrypt most of my databases with a password. Sadly, Access Database Repair cannot work with such files! To me this is a major limitation.

Recovering Deleted Records

One of the nice feature was the fact that Access Repair offers the ability to recover deleted records.  That said, the way in which it works had me very confused.  It offers no way to reinserted the deleted records back into the originating table, but rather one must save the recovered database in which a new table will be created with the deleted records.  It is not the way I would have built the software, but it does work and then you can use queries to recover the records of interest.

As per other freely available recovery techniques, it is impossible to recover deleted records once a database is compacted.

File Corruption

File corruption comes in all sorts of shapes and forms.  One form of corruption that I recently was faced with was the fact that an unknown user had erroneously open an MS Access database in MS Word (why, I have no clue).  By doing so, the MS Access database became corrupted and no longer functioned.  I then checked with fellow MVPs and was surprised that this occurs more often than I thought.  So I decided to try to recover a recently corrupted database using Stellar Phoenix, but it was unable to recover the file stating:

Stellar Phoenix Access Database Repair - Not a Valid MS Access file

I also tried to repair a database which had a corrupted form.  What the exact nature of the form corruption was is unknown to me, but I do know that it became corrupted.  Sadly, Access Database Repair was unable to fix the problem.

 

I then sent an e-mail to my point of contact at Stellar Phoenix to ask for clarifications and below was the list of corruptions that they claim Access Database Repair Addresses:

Symptom 1: Cannot open a form or report
Symptom 2: Number of records varies, depending how the data is sorted (index corruption)
Symptom 3: Some table rows show ‘Deleted’
Symptom 4: Memo field contains strange characters.
Symptom 5: “An error occurred while loading Form_FormName”
Symptom 6: “Error Accessing File. Network Connect May Have Been Lost”
Symptom 7: “AOIndex is not an index in this table”
Symptom 8: Key field is no longer primary key, and relationships are gone

 

Conclusions

I’m not sure what to think.  I will need to do further testing to truly know how effective a tool Access Database Repair truly is.

My other thought on the matter is that in many cases, the solutions to many (if not all) of the symptoms is freely available and various resources exist to help.  For instance:

For me, corruption, the possibility of corruption, simply reinforce the critical importance of Backups for all files (not just MS Access databases)!  By implementing a proper backup strategy, the repair of corrupted files becomes unnecessary.

 

more to come…

Smart Inderter Not Working in Office 2010, 2013

VBA Smart Indenter

If you do any serious VBA development, you should have heard, and installed, Office Automation Ltd’s Smart Indenter.  If you haven’t you should!  It is an exceptional FREE tool that helps properly indent your code to help with code legibility & standardization.  It offers numerous customizable options to suit your specific needs.

That said, I recently tried installing on a machine with Office 2013 and it would not work!?  What to do.

After so going back and forth with Mz-Tools (another must have VBA developer tool), I finally got it operational.

Now, I do not know exactly why or how, but the only conclusion I could come up with is the order of installation somehow fixes whatever the underlying problem is/was.  You must install Mz-Tools first and then Smart Indenter.  Smart Indenter on its’ own does not work, nor does installing Mz-Tools subsequently.  After trying different variations, the one that appear to have “fixed” the problem was to uninstall the Smart Indenter & Mz-Tools, then reinstall Mz-Tools followed by Smart Indenter.

So the conclusion drawn from my experience, it would appear, is that  Mz-Tools must be installed first!

 

If you have any insight into the nature of the problem (perhaps even an alternate solution) or if you can confirm my fix, please post a comment.

Maintaining your Virtual Machines – Windows XP Mode

Did you know you can actually maintain your Virtual Machines? I sure didn’t! I have been using Windows Virtual PC / Windows XP Mode for a little while now and never thought remotely about maintenance. It worked, so why concern myself.

That said, over time I noticed that some of my Virtual Machine were bloating in size when I thought of the contents. These Virtual Machines were GB larger than what I thought they should be and thus hogging valuable Hard Drive real estate for no reason. As such, I started nosing around and discovered that you can in fact Compact a Virtual Machine.

Sadly, once again Microsoft has buried something useful under layers of menus.  No clue why they chose to hide such a command under the Modify button, but if you dig a little, the option to regain a little hard drive space is indeed there.

Below are the instructions on how to do so.

  1. Open your Virtual Machine Folder (typically found in %UserProfile%/Virtual Machines/)
    Virtual Machines
  2. Select the Virtual Machine you would like to compact
  3. Click on the Settings button in the toolbar immediately above the Virtual Machine Listing (a pop-up dialog will appear)
    Windows Virtual PC Settings
  4. Select Hard Disk 1 (right-hand side of the dialog)
  5. Click on the Modify Button (Left-hand side of the dialog – another pop-up will appear)
    Modify Virtual Hard Disk
  6. Click on Compact virtual hard disk
    Compact Virtual Hard Disk
  7. Click on Compact
    Compacting Virtual Hard Disk
  8. Wait for the the Compaction Success Confirmation (this can take more or less time depending on many factors)
    Virtual Hard Disk Compaction Successful
  9. Click Close

Now, I’m not saying you need to do this every week, but after deleting a large amount of data, removing sizeable programs, … from a Virtual Machine, it may be a good idea to perform a quick compaction.

VBA – Remove HTML Tags from a String

I needed to clean some webpage texts for a private project I was working on and created the following function. Today, in a forum question, the subject resurfaced so I dug up my function and am posting it here should it might serve someone else.

So here is a simple function utilizing Regular Expressions to remove/sanitize/extract HTML Tags from the passed string. So you get returned the text and nothing else.

'---------------------------------------------------------------------------------------
' Procedure : RemoveHTML
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Remove any HTML tags and/or comments from a string
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sString   : String to sanitize (remove HTML tags from)
'
' Usage:
' ~~~~~~
' RemoveHTML("<html><b>And</b><!-- some comment --> <p>then<br/> some</p></html>")
'           Returns: And then some
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-Aug-26             Initial Release
' 2         2009-Oct-30             Changed pattern to include comments
'---------------------------------------------------------------------------------------
Function RemoveHTML(sString As String) As String
    On Error GoTo Error_Handler
    Dim oRegEx          As Object
 
    Set oRegEx = CreateObject("vbscript.regexp")
 
    With oRegEx
        'Patterns see: http://regexlib.com/Search.aspx?k=html%20tags
        '.Pattern = "<[^>]+>"    'basic html pattern
        .Pattern = "<!*[^<>]*>"    'html tags and comments
        .Global = True
        .IgnoreCase = True
        .MultiLine = True
    End With
 
    RemoveHTML = oRegEx.Replace(sString, "")
 
Error_Handler_Exit:
    On Error Resume Next
    Set oRegEx = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: RemoveHTML" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

VBA – Split/Break a Camel Case String

I was having a recent discussion with a fellow MVP and came across a function I developed a while back and thought it could be useful to others.  Below is a very simple function which uses a RegEx pattern to break apart a Came Case string into a legible string.

'---------------------------------------------------------------------------------------
' Procedure : SplitCamelCase
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Split/Break a Camel Case string
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sString   : Camel Case string to break/split
' sDelim    : Character to use as a spcaer, if omitted will use a space
'
' Usage:
' ~~~~~~
' ?SplitCamelCase("SplitCamelCase")
'       Returns Split Camel Case
' ?SplitCamelCase("SplitCamelCase", "_")
'       Returns Split_Camel_Case
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2008-May-03             Initial Release
'---------------------------------------------------------------------------------------
Function SplitCamelCase(sString As String, Optional sDelim As String = " ") As String
On Error GoTo Error_Handler
    Dim oRegEx          As Object
 
    Set oRegEx = CreateObject("vbscript.regexp")
    With oRegEx
        .Pattern = "([a-z](?=[A-Z])|[A-Z](?=[A-Z][a-z]))"
        .Global = True
        SplitCamelCase = .Replace(sString, "$1" & sDelim)
    End With
 
Error_Handler_Exit:
    On Error Resume Next
    Set oRegEx = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: SplitCamelCase" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Trusted Location Editor by CARDA Consultants Inc.

Tired of battling with Microsoft’s never ending changing layouts, navigating through an endless maze of menus, and the fact that the Trust Center is inaccessible on the runtime version of MS Access, I recently created the Trusted Location Editor. A very lightweight application which put the user back in the driver’s seat to manage all the various MS Office application Trusted Locations from one single screen! In essence, what Microsoft should have provided users 8 years ago!

Simplicity was the order of the day, so everything is straightforward and no more than 1 click of a button away!

Trusted Location Editor - Overview

For all the details on this application, please use the following link:

MS Office Trusted Location Editor