Create add ins in MS Access

How to create and install AddIn in MS Access using VBA code.

This article is about to create and install addin in other MS Access applications. For implementation of AddIn we are using USysRegInfo. USysRegInfo is used for creating AddIn and then installing in other MS Access applications. There are two different types of add-ins: application-specific add-ins and Component Object Model add-ins. COM add-ins can be used across all Microsoft Office applications. However, they are not as easy to build and to distribute. An application-specific add-in works in only one application. According to requirement we can use one of them.

In AddIn Manager, new entries are created in the Windows Registry. The values for these keys are stored in the USysRegInfo table. The first created record is stored in 0 positions in the Type field and blanks in the ValName and Value fields. For implementation of this firstly we have to create the form with given fields and one for sub key. Then embed the Sub Form with following fields as shown in Fig 1.1. The record source for this Form is:

"SELECT USysRegInfo.Subkey FROM USysRegInfo GROUP BY USysRegInfo.Subkey". In highlighted sub key field, record source is Sub key from above query.

Create add ins in MS Access Fig-1.1


Next is to create the Form which will work like sub form for add info manager form. The record source for this form is "SELECT USysRegInfo.ValName, USysRegInfo.Value FROM USysRegInfo WHERE (((USysRegInfo.ValName) Is Not Null)) ORDER BY USysRegInfo.ValName".

For value name textbox the record source is ValName and for value textbox the record source is Value as shown in Fig 1.2.

Create add ins in MS Access Fig-1.2


Now open the add in info manager form in Form view as shown in Fig 1.3. Now we can type the information in the fields.

Create add ins in MS Access Fig-1.3


Now we can use this addin in multiple Microsoft Office application by placing the addin Form in "C:\Users\admin\AppData\Roaming\Microsoft\AddIns" according to system settings. For testing of this addin we have to open other MS Access application. Go in the Database ribbon and select the AddIn and click on Add New, above specify path automatically opened. Then install this addin as shown in Fig 1.4.

Create add ins in MS Access Fig-1.4


VBA Code for Microsoft Access Class Objects Form_AddInInfoManager:

Option Compare Database
Option Explicit
Public Function GetSummaryInfoProperty(strProperty As String) As Variant
GetSummaryInfoProperty = CurrentDb.Containers("Databases") .Documents("SummaryInfo") .Properties(strProperty)
End Function

Public Function SetSummaryInfoProperty(strProperty As String, strValue As String)
CurrentDb.Containers("Databases") .Documents("SummaryInfo") .Properties(strProperty) = strValue
End Function
Private Sub txtCompany_AfterUpdate()
Call SetSummaryInfoProperty `("description", Me.txtCompany)
End Sub
Private Sub txtTitle_AfterUpdate()
Call SetSummaryInfoProperty("name", Me.txtTitle)
Dim strTitle As String
strTitle = Me.txtTitle
If strTitle = "" Then
Exit Sub
End If
Call UpdateSubkey(strTitle)
End Sub

Public Function UpdateSubkey(strValue As String)
Dim strSQL As String
Dim strSubkey As String
strSubkey = "HKEY_CURRENT_ACCESS_ PROFILE\Menu Add-ins\" & strValue
strSQL = "UPDATE USysRegInfo SET USysRegInfo.Subkey = '" & strSubkey & "';"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Function