How to use Dictionary in Microsoft Access 2010

Dictionary is most widely used Data Structure among various programming languages. It is also known as Symbol Table, maps in other languages. It is kind of abstract data type and composed of collection of key, value pair such that each possible key appears at most once in the collection. It is used so that during operation (such as INSERT, DELETE) it can maintain set of data. In order to use this Data Structure first we need to create a form as shown in figure below.

How to use Dictionary in Microsoft Access 2010 Fig 1.1


As shown in figure above form named frmDictionary will contain few MS Access controls. There will be one text box to insert value into dictionary and another control will be a button with Caption Insert to fire corresponding action. Initially a massage box will come up to insert values into Dictionary.

How to use Dictionary in Microsoft Access 2010 Fig 1.2


Now in order to use this data structure we need to explicitly set reference to Microsoft Scripting Runtime Library. To accomplish this task open Visual Basic Editor Window and click on Tools->References and after selecting particular library we can use it in our programming environment. After inserting value in dictionary a massage box will be fired as shown in figure below.

How to use Dictionary in Microsoft Access 2010 Fig 1.3


This kind of data structure could be used to enhance problem solving skills and could be used as an alternative over tables in MS Access for interim processing.

VBA Code:-

Private Sub cmdHash_Click()
Dim i As Integer
Dim dict As Dictionary
Set dict = New Dictionary
i = CLng(100 * Rnd)
If Not (IsNull(Me.txtitem_one)) Then
With dict
.Add i, Me.txtitem_one
MsgBox "Items Inserted into Dictionary: " & dict(i), vbInformation, "Items"
End With
MsgBox "Enter Items to insert", vbInformation, "Empty Values"
End If
Me.txtitem_one = Null
End Sub

Private Sub Form_Load()
MsgBox "Dictionary is Empty, Insert Item to Dictionary"
End Sub


It is advised that the information provided in the article should not be used for any kind formal or production programming purposes as content of the article may not be complete or well tested. ERP Makers will not be responsible for any kind of damage (monetary, time, personal or any other type) which may take place because of the usage of the content in the article.