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

VBA – CDO Mail

Although I often refer people to use CDO mail and have briefly discussed it in prior postings, I never gave examples. Today that changes!

So what exactly is CDO mail and when is it useful?

CDO mail is one of many technique available to developer to send e-mails.

If you are an MS Access developer you are surely aware of the SendObject method, but it will only allow one attachment and that attahcment has to be an Access object (Not an external file).

Another very common approach is to use Outlook automation, but with new security impositions by Microsoft it has become unreliable and now requires workarounds. Furthermore, after a recent discussion with fellow MVPs, we find out that having an outdated virus definition can stop longstanding code from working! As such, although powerful, outlook automation simply is not reliable for true production. For production tools, one must then look at integrating Outlook Redemption, but the down side to this solution is that it requires registering a COM library for it to work.

Which brings us to CDO Mail!
CDO Mail is a technique that binds you directly to your e-mail server to send out email(s).

Pros

  • No e-mail client (outlook, thunderbird, …) need be installed
    • Hence you bypass any e-mail client limitations and/or security restrictions
  • Permits multiple attachments to be included
  • Permits external documents as attachments
  • Accepts both plain text and HTML formatted content
  • Can be used in any programs which uses VBA (MS Access, Excel, Word, PowerPoint, MS Project, …)
  • Can even be used in VBScript

Cons

  • Requires knowledge of all the server settings (port, username, password, authentication, …)
  • Does not keep a copy of sent e-mails so CCing or BCCing yourself becomes useful for archive pruposes
  • Does not permit attaching database objects directly, but you can export them or print them as PDFs and attach those

Below is a sample CDO Mail function

Const cdoSendUsingPickup = 1 'Send message using the local SMTP service pickup directory.
Const cdoSendUsingPort = 2 'Send the message using the network (SMTP over the network).

Const cdoAnonymous = 0 'Do not authenticate
Const cdoBasic = 1 'basic (clear-text) authentication
Const cdoNTLM = 2 'NTLM

Function SendCDOMail(sTo As String, sSubject As String, sBody As String, _
                     Optional sBCC As Variant, Optional AttachmentPath As Variant)
    On Error GoTo Error_Handler
    Dim objCDOMsg       As Object
 
    Set objCDOMsg = CreateObject("CDO.Message")
 
    'CDO Configuration
    With objCDOMsg.Configuration.Fields
        '
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
        'Server port (typically 25, 587)
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        'SMTP server IP or Name
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.hitterslongrun.com"
        'Type of authentication, NONE, Basic (Base64 encoded), NTLM
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
        'SMTP Account User ID
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "accounts@hitterslongrun.com"
        'SMTP Account Password
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Uhdje!@@0#"
        'Use SSL for the connection (False or True)
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
        .Update
    End With
 
    'CDO Message
    objCDOMsg.Subject = sSubject
    objCDOMsg.From = "accounts@hitterslongrun.com"
    objCDOMsg.To = sTo
    objCDOMsg.TextBody = sBody
    ' Add attachments to the message.
    If Not IsMissing(AttachmentPath) Then
        If IsArray(AttachmentPath) Then
            For i = LBound(AttachmentPath) To UBound(AttachmentPath)
                If AttachmentPath(i) <> "" And AttachmentPath(i) <> "False" Then
                    objCDOMsg.AddAttachment AttachmentPath(i)
                End If
            Next i
        Else
            If AttachmentPath <> "" And AttachmentPath(i) <> "False" Then
                objCDOMsg.AddAttachmentAttachmentPath
            End If
        End If
    End If
    objCDOMsg.Send
 
Error_Handler_Exit:
    On Error Resume Next
    Set objCDOMsg = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: SendCDOMail" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Now that we have a function to call, we can now send out an e-mail by making a single call to the function along the lines of:

Call SendCDOMail("RecipientEmail", "Subject", "EmailBody", , array("C:\Users\Test\Documents\sample.pdf", "C:\Users\Test\Documents\ballons.gif"))

Other references on CDO Mail

Sending mail from Excel with CDO
VBScript To Send Email Using CDO ‘Information directly applies to VBA as well

MS Access List Table Indexes

Once again, trying to help someone in a forum (see Brent Spaulding’s tweak to my code), I quickly put together the following to enumerate a list of indexes for a given table.

'---------------------------------------------------------------------------------------
' Procedure : ListIndexes
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : List the names of the indexes in the specified table
' 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:
' ~~~~~~~~~~~~~~~~
' sTbl      : Name of the table to list the names of the Indexes from
'
' Usage:
' ~~~~~~
' Call ListIndexes("tbl_Appointments")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2014-Nov-20                 Initial Release
'---------------------------------------------------------------------------------------
Function ListTblIndexes(sTbl As String)
On Error GoTo Error_Handler
    Dim db              As DAO.Database
    Dim tdf             As TableDef
    Dim myIndex         As Index
 
    Set db = CurrentDb
    Set tdf = db.TableDefs(sTbl)
 
    For Each myIndex In tdf.Indexes
        Debug.Print myIndex.Name
    Next
 
Error_Handler_Exit:
    On Error Resume Next
    Set tdf = Nothing
    Set db = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: ListTblIndexes" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function