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

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

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

Things I wish I knew about Microsoft Access before I started using Microsoft Access

Microsoft Access is one of the most powerful databases for small business available on the market. Not only does it have its own form and reporting development environment built in; the wizards and design views allow the user to quickly create data centered applications. This ease of use, coupled with the simple fact that Access is included with Microsoft Office has lead to widespread usage in both the small business and corporate environments.

I have worked with Access for several years. In my experience, I have come to several conclusions about Access:

Multi-User Company Wide Applications should never be built using Microsoft Access.
Multi-User applications in Access use the file locking system built into Windows. What this means is that after the first user connects to the database, there is a significant slowdown in performance. MS Access supports up to 256 connections, the actual performance limit is much lower. I have seen access applications come to a crawl with only 10 users. Instead use SQL Server with a .NET front end.

Set the Focus On The Field
One of the painful things you must do in MS Access when doing applications is that you must set the focus on the field before you can get the value the user typed in.

MS Access Corrupts Databases
Because of the file locking and users disconnecting due to lockups in Windows, MS Access Databases will sometimes become corrupted. Try these things if it happens to you:

1. Do a repair and compact.


2. Restore from your backup. If you don’t have a backup, you need to back up your database nightly.


3. Create a blank database and import it in.


4. Buy a tool that repairs access databases.

Easy Applications Are Super Easy, Hard Applications are Super Hard
Access tends to be incredible at building quick and dirty database applications. If there are a significant amount of business rules involved, Microsoft Access simply becomes counterproductive. It is easier to build a business rule intensive application in .NET than to build it in Microsoft Access.

So what is Microsoft Access good for?

• Microsoft Access is great when you have a bunch of spreadsheets that you need to import and run some reports on.


• Microsoft Access is great to store simple things at home like your recipes or DVD collection.


• • Microsoft Access is great for small business until the business is ready to afford a full time dedicated IT person to do .NET development.

Finally, get yourself an Access Database Comparison Tool, you make copies of your Access Database, employees will change things and then you will wonder what changed. The best tool for comparing Access Databases is AccessDiff.