Stack Data Structure Operations Using MS Access VBA

This article is on Stack a commonly used Data Structure in various programming languages. There are numerous instances where one wants to use this kind of Data Structure. This article uses Class Module in order to declare common properties and variable to this class module. Article has been explained in a very concise manner. As shown in figure below, first of all we have to create a form named frmStackImplementation with controls over it.

Stack Data Structure Operations Using MS Access VBA Fig 1.2

Fig 1.1

A simple data structure operation in context to stack based on principle of LIFO (Last in first out operation). These two operations commonly known as Push and Pop operations. This article explains these two operation precisely. There are many scenarios where this kind of operations could be exploited. For this purpose first of all we declare a Class module named Stack. Initially user will be asked for option to set up stack limit through an input box that has been shown in figure below.

Stack Data Structure Operations Using MS Access VBA Fig 1.2

Fig 1.2

As shown in above figure form mainly contains two different button. For the purpose of implementing Stack operation we will use these buttons. One for Push operation and another for Pop operation. There will be corresponding VBA Code associated with these two buttons (Push and Pop). Now in MS Access VBA in order to simulate Stack operation we will use a table named tblStackOperation. Concerned fields has been shown in figure below.

Stack Data Structure Operations Using MS Access VBA Fig 1.3

Fig 1.3

Above table will store items in it and stack operations will be performed corresponding to this tables. Apart from this table there will be one more table named tbltmp that will be used for interim processing. This table has been shown in figure below.

Stack Data Structure Operations Using MS Access VBA Fig 1.4

Fig 1.4

Initially a message box will popup regarding appropriate operation that needs to be performed by user.

Stack Data Structure Operations Using MS Access VBA Fig 1.5

Fig 1.5

If user chooses to perform Pop operation, then Stack Underflow occurs because initially Stack is empty. This situation has been shown in figure below.

Stack Data Structure Operations Using MS Access VBA Fig 1.6

Fig 1.6

If User try to insert item into Stack more than its defined limit then an error message fires that has been shown in figure below.

Stack Data Structure Operations Using MS Access VBA Fig 1.7

Fig 1.7

VBA Code for Stack Operation:-

VBA Code for Class Module: Stack

Public Sub Push(item As Variant)
Dim rst As DAO.Recordset, rs As DAO.Recordset, count As Double
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tbltmp")
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblStackOperation")
If rs.RecordCount <> 0 Then
count = 0
While Not rs.EOF
count = count + 1
rs.MoveNext
Wend
If IsNumeric(item) And (count +1) <= rst!tblSize Then
CurrentDb.Execute "INSERT INTO tblStackOperation(Item) VALUES(" & item & ")"
MsgBox "Item '" & item & "' inserted into Stack", vbInformation, "Push Operation Successful"
ElseIf (count+1) <= rst!tblSize Then
CurrentDb.Execute "INSERT INTO tblStackOperation(Item) VALUES('" & item & "')"
MsgBox "Item '" & item & "' inserted into Stack", vbInformation, "Push Operation Successful"
Else
MsgBox "Delete Some items ", vbCritical, "Stack Overflow"
End If
Set rst = Nothing
Set rs = Nothing
Exit Sub
Else
If IsNumeric(item) Then
CurrentDb.Execute "INSERT INTO tblStackOperation(Item) VALUES(" & item & ")"
Else
CurrentDb.Execute "INSERT INTO tblStackOperation(Item) VALUES('" & item & "')"
End If
MsgBox "Item '" & item & "' inserted into Stack", vbInformation, "Push Operation Successful"
End If
Set rst = Nothing
Set rs = Nothing
End Sub
Public Sub Pop()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblStackOperation")
rst.MoveLast
If rst.RecordCount = 0 Then
MsgBox "Stack Underflow", vbCritical, "Empty Stack"
Else
rst.MoveLast
MsgBox "Item:" & rst!item & " Popped out of Stack", vbInformation, "Pop Operation Successful"
CurrentDb.Execute "DELETE * FROM tblStackOperation where Item = '" & rst!item & "'"
End If
Set rst = Nothing
End Sub

VBA Code associated with MS Access Objects:

Dim myStack As Stack
Private Sub cmdPop_Click()
Set myStack = New Stack
myStack.Pop
End Sub
Private Sub cmdPush_Click()
Set myStack = New Stack
If Not IsNull(Me.txtPush) Then
myStack.Push (Me.txtPush)
Else
MsgBox "Enter Value", vbInformation, "Required"
End If
Me.txtPush = Null
End Sub
Private Sub Form_Close()
CurrentDb.Execute "DELETE * FROM tblStackOperation"
CurrentDb.Execute "DELETE * FROM tbltmp"
End Sub
Private Sub Form_Load()
Dim i As Integer
i = InputBox("Enter Maximum Stack Size", "Numeric Values only", 10)
CurrentDb.Execute "INSERT INTO tbltmp(tblSize) VALUES(" & i & ")"
CurrentDb.Execute "DELETE * FROM tblStackOperation"
MsgBox "Stack is Empty insert some items", vbInformation, "Important"
Exit Sub
End Sub


DISCLAIMER

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. Access Guru 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.