How to get most recent DBEngine object

Below is Graham Mandeno's version of  VBA GetDBEngine(), now in VB.NET. If user have several versions of DAO installed this function returns reference to latest available.

 1:  Public Function GetDBEngine() As Object

 2:  Dim aVersions() As String = {"120", "36", "35"}

 3:  Dim i As Integer

 4:  GetDBEngine = Nothing

 5:  Try

 6:   For i = LBound(aVersions) To UBound(aVersions)

 7:    Try

 8:      GetDBEngine = CreateObject("DAO.DBEngine." & _
        aVersions(i))

 9:      Exit For

 10:   Catch ex As Exception When Err.Number = 429

 11:   'continue after "Cannot create ActiveX component"

 12:   Catch

 13:    Throw

 14:   End Try

 15:   If i = UBound(aVersions) Then _ 

        Throw New System.Exception("No known version" & _
        " of DAO is available")

 16:   Next

 17:  Catch ex As Exception

 18:   Throw New System.Exception("Cannot create " & _
      "DBEngine" & vbCrLf & ex.Message)

 19:  End Try

 20:  End Function

How to make Windows 8 work for you

These 2 steps you need to do if you want to do some coding/testing on Windows 8, rather than playing with Metro UI.

1. Switch to Window 7 type Start menu
Navigate using RegEdit to: HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer
Set registry key RPEnabled to 0

How to get a Windows 7 start menu in Windows 8

2. Disable UAC completely
Navigate using RegEdit to: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System
Set registry key EnableLUA to 0

How To (Really) Completely Disable UAC on Windows 7

Replace special characters for XML

If you are writing custom function for XML export – do not forget to replace predefined XML characters, else XML parsing function may fail. There are only 5 characters, so function looks pretty simple:

Function XMLSpecialChars(ByVal varText As Variant) As String 
  varText = varText & "" 
  varText = Replace(varText, "&", "&") 
  varText = Replace(varText, "'", "'") 
  varText = Replace(varText, """", """) 
  varText = Replace(varText, ">", ">") 
  varText = Replace(varText, "<", "&lt;") 
  XMLSpecialChars = varText 
End Function 

Lost in conversion? Excel's Convert() will help you

Quite useful Convert() function, if you need to convert inches to meters or gallons to cups. Excel's CONVERT function for metric and more article shows usage samples and measurement list.

Multi-value Fields - Appending / Updating with query

Access 2007 introduced Multi-value fields (MVF), some people like it, some – hate it. I personally like UI for MVF in Access, but don't like realization in table level – you can't get access to  junction table of MVF. Anyway - any Access developer have to learn working with it, sooner or later, and here something your can look at.

A.D. Tejpal, Access MVP, has published Multi-value Fields - Query Based Solution For Bulk Appending / Updating sample database, which shows several techniques to work with MVF.

Convert linked table to local

There is an easy way to convert linked table to local in Access 2010, in other words – import linked table into local database, using acCmdConvertLinkedTableToLocal command:

 DoCmd.SelectObject acTable, "Company", True RunCommand acCmdConvertLinkedTableToLocal

Command is mentioned in Office Help, but not really documented. Thanks to fellow MVPs for pointing to it!

Access source control software

Source control software is quite useful when several people work on the same project same time; one can take ownership on certain object or module (check-out), make modification, put it back (check-in), and other developers get changes you made. I have used myself Visual Source Safe, long time ago, in Access 97 time. Last version, Visual SourceSafe 2005, will retire from mainstream support on 10 July 2012, and now you can use Team Foundation Server, which offers similar functionality. Recently I learned about OASIS-SVN versioning system, which is much cheaper than TFS. Do you know other source control software for Access? Are you happy using it? Please share your experience!