MS Access – VBA – The Save Operation Failed

I was recently working on a database of mine which has been in production for over 4 years now and all of a sudden it started giving me the error: “The Save Operation Failed”. Nothing like a nice obscure error message from the VBA/VBE!!!

I tried the normal approaches: Compact and repair, Decompile/Recompile, … None worked for me.

I took a look at Microsoft’s Knowledge Base and found an article, ACC97: Error “The Save operation failed” When Saving a Module, for Access 97 (as a lot of the information can be used in future version, I read it over). Sadly, the article is pretty much useless.

In the end, the solution, for me, was to start a new database and import all of the database objects (File -> Get External Data -> Import) from the database giving me the error message. Setup the startup properties… and everything was in working order again!

I hope this helps someone else in the same predicament!

Microsoft Access Database Add-ins, Tools, Etc.

One thing any good worker will tell you is that you must have the right tools to do the job. Computer programming, MS Access database development, is no exception to this rule!

I thought I’d try and list a few add-ins and other useful tools that I have come across, or heard of, in the hopes it could prove useful to other developers.

This is an unbiased listing and I have no financial gain from any of the tools listed below. I am simply trying to regroup them to help you find them, and you can judge their usefulness on your own. In a future post, I will examine the 2 or 3 that I use myself and find very useful.
Continue reading →

MS Access – VBA – Determine if a Module Exists

Sometimes it can be handy to be able to identify whether or not a module is in a database or not. One way to check is to loop through the AllModules collection to see if it is there or not. Below is a sample procedure that demonstrates how you can check for the existence of a VBA module within your database.

Determine whether a Module Exists using the AllModules Collection

'---------------------------------------------------------------------------------------
' Procedure : ModuleExist
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine if a module exists within 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).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sModuleName - Name of the module you are searching for
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' ModuleExist("Module2")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2008-Aug-13                 Initial Release
'---------------------------------------------------------------------------------------
Function ModuleExist(sModuleName As String) As Boolean
On Error GoTo Error_Handler
Dim mdl                   As Object
    ModuleExist = False 'Initialize our variable
    'Loop through all the modules in the database
    For Each mdl In CurrentProject.AllModules
        If mdl.Name = sModuleName Then
            ModuleExist = True
            Exit For 'No point continuing if found
        End If
    Next

Error_Handler_Exit:
    On Error Resume Next
    Set mdl = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: ModuleExist" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

VBA – Validate E-mail Address

One option is to systematically check for the various dos and don’t using a series of if statements such as that in the function below:

Public Function isValidEmail(inEmailAddress As String) As Boolean 
' Author: Unknown

If (Len(inEmailAddress) = 0) Then 
    MsgBox "Please enter your email address." 
    isValidEmail = False 
    Exit Function 
End If 
If (InStr(1, inEmailAddress, "@") = 0) Then 
    MsgBox "The '@' is missing from your e-mail address." 
    isValidEmail = False 
    Exit Function 
End If 
If (InStr(1, inEmailAddress, ".") = 0) Then 
    MsgBox "The '.' is missing from your e-mail address." 
    isValidEmail = False 
    Exit Function 
End If 

If (InStr(inEmailAddress, "@.") > 0) Then 
    MsgBox "There is nothing between '@' and '.'" 
    isValidEmail = False 
    Exit Function 
End If 

If ((InStr(inEmailAddress, ".")) = ((Len(inEmailAddress)))) Then 
    MsgBox "There has to be something after the '.'" 
    isValidEmail = False 
    Exit Function 
End If 

If ((Len(inEmailAddress)) < (InStr(inEmailAddress, ".") + 2)) Then 
    MsgBox "There should be two letters after the '.'" 
    isValidEmail = False 
    Exit Function 
End If 

If (InStr(1, inEmailAddress, "@") = 1) Then 
    MsgBox "You have to have something before the '@'" 
    isValidEmail = False 
    Exit Function 
End If 

isValidEmail = True 
End Function

based on some comments below, I modified the above slightly.

'---------------------------------------------------------------------------------------
' Procedure : isValidEmail
' Author    : Unknown
' Modified by: Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Validate an e-mail address against some basic formatting rules
'              returns True/False indicating if the e-mail address is properly formatted
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sEmailAddress : E-mail address to validate
'
' Usage:
' ~~~~~~
' isValidEmail("@.") -> False with error message
' isValidEmail("chantal@hotmail.com") -> True
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1
' 2         2017-01-26              Added Error Handler
'                                   Concatenation of error messages into a single msg
'---------------------------------------------------------------------------------------
Public Function isValidEmail(sEmailAddress As String) As Boolean
' Author: Unknown
' Modified by: Daniel Pineault
    On Error GoTo Error_Handler
    Dim sMsg                  As String

    isValidEmail = True

    If (Len(sEmailAddress) = 0) Then
        sMsg = "Please enter your email address."
        isValidEmail = False
    End If

    If (InStr(1, sEmailAddress, "@") = 0) Then
        sMsg = sMsg & IIf(sMsg = "", "", vbCrLf) & "The '@' is missing from your e-mail address."
        isValidEmail = False
    End If

    If (InStr(1, sEmailAddress, ".") = 0) Then
        sMsg = sMsg & IIf(sMsg = "", "", vbCrLf) & "The '.' is missing from your e-mail address."
        isValidEmail = False
    End If

    If (InStr(sEmailAddress, "@.") > 0) Then
        sMsg = sMsg & IIf(sMsg = "", "", vbCrLf) & "There is nothing between '@' and '.'"
        isValidEmail = False
    End If

    If ((InStr(sEmailAddress, ".")) = ((Len(sEmailAddress)))) Then
        sMsg = sMsg & IIf(sMsg = "", "", vbCrLf) & "There has to be something after the '.'"
        isValidEmail = False
    End If

    If ((Len(sEmailAddress)) < (InStr(sEmailAddress, ".") + 2)) Then
        sMsg = sMsg & IIf(sMsg = "", "", vbCrLf) & "There should be two letters after the '.'"
        isValidEmail = False
    End If

    If (InStr(1, sEmailAddress, "@") = 1) Then
        sMsg = sMsg & IIf(sMsg = "", "", vbCrLf) & "You have to have something before the '@'"
        isValidEmail = False
    End If

    'Display the error message if applicable
    If isValidEmail = False Then
        Call MsgBox(sMsg, vbCritical + vbOKOnly, "E-mail Address Syntax Error")
    End If

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: isValidEmail" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

A Second better approach, like with many web programming languages, is to use regular expressions to validate certain type of strings. Now Access' VBA does not allow RegEx persey, but you can very easily access such functionality by adding 2 simple lines of code. You then end up with a total of a three lines of code to validate almost any string, including an e-mail address. For all the details, and a link to where you can get a multitude of the RegEx so you don't have to reinvent the wheel simply check out my post VBA – Using Regular Expressions (RegEx) and Validate E-mail Addresses.

MS Access – VBA – Loop Through All The Controls on a Form

Below is come sample VBA which illustrates how one can easily loop through all the controls within a form to identify and work with them.

Dim ctl As Control
For Each ctl In Me.Controls
    ctl.Name 'Get the name of the control
    ctl.Value 'Get or set the value of the control
    ctl.Visible = False 'Control the visibility of the control
Next ctl

Now how can this be put to good use? Well one use for such code would be to setup a Select All, or Select None button for a series of check boxes on a form. Below is what the code could look like for each command button:

'Select All
Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.ControlType = acCheckBox Then
        If ctl.Value <> True Then
            ctl.Value = True
        End If
    End If
Next ctl

'Select None
Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.ControlType = acCheckBox Then
        If ctl.Value <> False Then
            ctl.Value = False
        End If
    End If
Next ctl

Lastly, you could easily adapt the general form specific code and transform it into a generic procedure to which you will supply the form name to loop through the control, rather than working with the current form, you could do something along the lines of:

Function YourProcedureName(ControlName As String, frm As Access.Form)
    Dim ctl As Access.Control
    For Each ctl In frm.Controls
        

    Next ctl
End Function

PC Information HTA

A simple HTML Application (HTA) which can retrieve an extensive set of information on various computer aspects.

Currently, it can return information on hardware, printer, memory, CPU and hard drives, processes, video and audio,… It can also query other computers (as long as you have admin privileges on the remote machine). Simply add remote computer names to the associated text file.

Download the sample database: PC Information Utility HTA.

MS Access Sample – Change Password

If you are using user-level security (ULS), the following is a simple demonstration of a form that can be added to any database to permit the user the ability to change their password. Useful when you create an mde or disable the standard toolbars for increased security but still want to give the user the ability to rotate their own password.

Download the sample database: User-Level Security Change of Password Form Demonstration.

Special Note & Warning
Be very careful using this sample.  If you run it on a database which does not have a designated mdw security database assigned, it will alter the system.mdw, thus affecting ALL your databases!  It is only meant to be run on secured databases using ULS security.  Any other applications can have disastrous results on your databases.  I mention this because I recently was e-mail by someone who made this mistake.  Below is the solution to fix such a mistake:

So if I understand the situation properly, you ran the chgPwd.mdb on your computer, in a db, or on its own in a db that wasn’t already secured?  I would assume that by doing so you inadvertantly applied a password against your system mdw database.  So in fact you secured the master system.mdw database, thus imposing a password against all databases running on your computer.  The fix, you’d need to either reinstall Access, or copy over a system.mdw from another clean PC over to your.