Programmatically Viewing/Adding References in Access

When your Access application is distributed to end users, they may have different versions of COM components. You can programmatically add references to resolve broken references due to differing versions. Here is how to get a list of all the current references in MS Access using VBA:

Public Sub PrintOutCurrentReferences()
    Dim iIndex As Integer
    
    For iIndex = 1 To application.References.Count
        Debug.Print application.References(iIndex).name  _
        & ", " & application.References(iIndex).GUID  _
        & ", " & application.References(iIndex).Major _
        & ", " & application.References(iIndex).Minor
    Next
End Sub

Example of adding a reference going from the latest version to the oldest version of the ADO Extension Library:

    'Add Microsoft ADO Ext for 2007, 2003, 2000
    If (AddReference("ADOX", "{00000600-0000-0010-8000-00AA006D2EA4}", 6, 0) = False) Then
        If (AddReference("ADOX", "{00000600-0000-0010-8000-00AA006D2EA4}", 2, 8) = False) Then
            Call AddReference("ADOX", "{00000600-0000-0010-8000-00AA006D2EA4}", 2, 5)
        End If
    End If

Below is the code to perform the action:

Private Function AddReference(sReferenceName As String, sReferenceGUID As String, iMajorVersion As Integer, iMinorVersion As Integer) As Boolean
    Dim bFound As Boolean
    Dim iIndex As Integer
        
    bFound = False
    'Try to find an existing reference
    For iIndex = 1 To application.References.Count
        If application.References(iIndex).name = sReferenceName Then
            bFound = True
            'Remove the reference if it is broken
            If application.References(iIndex).IsBroken Then
                application.References.Remove application.References(iIndex)
                bFound = False
            End If
            Exit For
        End If
    Next
        
   
    'If the reference was not found, or it was broken, add it
    If bFound = False Then
        On Error Resume Next
        application.References.AddFromGuid sReferenceGUID, iMajorVersion, iMinorVersion
        If Err.Number = 0 Then
            bFound = True
        Else
            Err.Clear
        End If
    End If
    
    AddReference = bFound

End Function

Automated Build Tools

Automated Build Tools

Here are the options that I have found so far to automate .NET builds:

TFS (Team Foundation Server) - This is Microsoft's answer to automated builds. Difficult to set up but it integrates well with Visual Studio and Defect Tracking.

CruiseControl – This tool basically runs a NAnt configuration file. There are a lot of pre-defined tasks that NAnt can perform such as getting the latest version from source control, building, and FTPing a file. The config files for NAnt are not that fun to set up.

MSDOS Batch File – If you don’t want to buy anything or mess with the convoluted NAnt config files, you can certainly build your own batch file and use windows task scheduler to schedule the build. There are many free command line programs available to email the log file, ftp files etc.

Commercial Automated Build Tools
Automated Build Studio
Final Builder
Visual Build Pro
TeamCity

Compact and Repair an Access Database using VBA

Below is some useful code to compact and repair the current Access Database using VBA:

Public Sub CompactAndRepair()
    CommandBars("Menu Bar"). _
    Controls("Tools"). _
    Controls("Database utilities"). _
    Controls("Compact and repair database..."). _
    accDoDefaultAction
End Sub

Free Tools for Testing Regular Expressions

Creating and maintaining regular expressions are not like riding a bike. You can create a regular expression for a project and come back a couple months later and it is unreadable. Scott Hanselman has a saying, "If you try to solve a problem with a regular expression, you now have two problems."

Fortunately there are several resources available for free to help you test and understand regular expressions.

The Kellerman Quick Reference Pack that contains a Regular Expression cheat sheet:
Free Quick Reference Pack

If you work at a bank and can't install software use Reggie. This is a regular expression tester, and reference in a simple HTML file. This is my favorite.
Reggie

If you are just getting started with Regular Expressions, Roy Osherove has a great free tool called Regulazy that will help you build simple Regular Expressions.
Regulazy

Roy also has an advanced tool (also free) called Regulator for building regular expressions.
Regulator

Someone may have already built the regular expression you need. Take a look at this giant repository of regular expressions.
RegexLib

What is the Strongest Encryption Algorithm?

New Article:
What is the strongest encryption algorithm?

The Greatest Video on YouTube

My brother in law (also a Christian) recently forwarded me a video on YouTube about Laminin. My wife is a Micro Biologist so it always peaks my interest to see something cool that God has created at the molecular level.



Laminin is great not only because it is evidence of God; but that God is an awesome coder. Only God is awesome. Laminin is only one protein in the body. As a coder myself, I am especially impressed with some other code God has written. There is a cell process to translate RNA into protein molecules. RNA is basically a subset of DNA. What is happening in this process is you have a section of RNA code copying another section of RNA code. My current project is a smart client application with a WCF back end. I can't help but think of the similarity between RNA translation and performing translations of proxy objects to client objects. Not long ago scientists used to believe that cells were no more complicated than Jello. It just goes to show you how dumb we are and how incredible God is. Anyone looking at the application that we developed can see comments with our names. Laminin is God's code comment.

Read Colossians 1:16-17 below.

Colossians 1:16-17
For by him all things were created: things in heaven and on earth, visible and invisible, whether thrones or powers or rulers or authorities; all things were created by him and for him. He is before all things, and in him all things hold together.

It is funny how my mind works, but when I see this verse I think of the Stephen Wright joke "What do batteries run on?" Being a software engineer if one of my kids asked that question I would explain that there is a chemical reaction that produces a flow of electrons from the positive terminal to the negative terminal. The problem is, now I am stuck because I can't explain what electrons run on. At the atomic level, electrons circle the nucleus in an electron cloud. What gives electrons the power to circle the nucleus? I believe that God is present even at the atomic level. He is the one that keeps the electrons circling the nucleus and holding everything together. After seeing the Laminin video it is clear that God put molecular processes in place to hold things together as well.

I'm running for President

I am not to fond of either candidate so I am going out on my own. I would appreciate your vote.

Retrieve a Private Member Variable using Reflection

Despite the private scope modifier, you can still get to a private member variable using Reflection.

Our Example Customer Class:

   public class Customer
   {
       private int _myPrivateVar = 6;
   }

The code to get the private member variable:

 using System.Reflection;
 
 public void test()
 {
  Customer myCustomer = new Customer();

  object value =
     typeof(Customer).InvokeMember("_myPrivateVar",
                  BindingFlags.GetField
                  | BindingFlags.NonPublic
                  | BindingFlags.Instance, null, myCustomer, null);

  Console.WriteLine(value.ToString());
 }

Get the current version of Access using VBA

Sometimes it is useful to know what version of Access your Access application is running under. Below is a function that will return the current running version of Access.

'Return a string according to the current access version
Public Function GetAccessVersion() As String
  Dim sVersion As String

  sVersion = SysCmd(acSysCmdAccessVer)

  Select Case sVersion
      Case "8.0"
          GetAccessVersion = "97"
      Case "9.0"
          GetAccessVersion = "2000"
      Case "10.0"
          GetAccessVersion = "2002"
      Case "11.0"
          GetAccessVersion = "2003"
      Case "12.0"
          GetAccessVersion = "2007"
      Case Else
          GetAccessVersion = "Future Version:  " & sVersion
  End Select
End Function

Regular Expression Groups in .NET

Regular Expressions are a great way of querying and replacing text. A while ago I stumbled upon a feature in the .NET framework for regular expressions: groups. Groups are not supported in browsers but they can be used for back end code in .NET. Breaking expressions into groups makes it easier to parse text. Here is a line of text from a standard FTP directory listing:

string directoryLine= "drwxr-xr-- dds grp 0 Feb 23 2002 data";

Here is the regular expression that we can use to parse the FTP directory line:

string mask = @"^(?<dir>[\-d])(?<permission>([\-rwxt]+))\s+\d+\s+\w+\s+\w+\s+(?<size>\d+)\s+(?<timestamp>\w+\s+\d+\s+\d{1,2}:\d{2})\s+(?<name>.+)"

The group names are prefixed by a question mark and then the group name in <name>.

Some example code to pull out the groups:

Regex regEx = new Regex(mask);
Match match = regEx.Match(directoryLine);

if (match.Success)
{
string fileName= match.Groups["name"].Value;

if (match.Groups["dir"].Value == "d")
{
//Do Something
}
}

Be Thankful For What You Have

As I go from assignment to assignment as a consultant, I am amazed at how different the offerings at each facility.

A couple assignments ago, there was one toilet for 30 guys and no air freshener in the bathroom. That was really bad after lunch. However there was free pop. That was really great.

On my last assignment there were no beverages at all. However it was bathroom heaven. There were six individually designed bathrooms.

On my current assignment, there are no office supplies. I have had pens stolen that I have brought from home too. Also, there is no working printer. However there is tea and three different kinds of coffee.

Arnulfo made me blog today. :)