Encoding data field in MS Access 2010 using VBA

How to encode data field into table using MS Access VBA

This article explains fundamental concept to encode data field in a MS Access table. As shown below in figure Fig-1.1 we will create a form named detailfrm and it include buttons named cmdencode with caption insert and another button named Fetch with caption cmddata. Details have been shown in figure below.

Storing data in encrypted form in MS Access table Fig-1.1

Fig-1.1

First we will create a table named tblperson along with necessary field to facilitate this process. Detail has been shown in figure Fig-1.2.

Storing data in encrypted form in MS Access table Fig-1.2

Fig-1.2

Now in next step we store some details into table named tblperson .Using form detailfrm make an entry into table because we have associated an on click event with insert control of this from hence usr_password field always stores encrypted value in it. Details has been shown in figure Fig 1.3.

Storing data in encrypted form in MS Access table Fig-1.3

Fig-1.3

In order to fetch details of recently created record click on fetch. A dialog box appear asking ID value to fetch corresponding record. Details has been shown in figure Fig-1.4.

Storing data in encrypted form in MS Access table Fig-1.4

Fig-1.4

We have associated an on click event with this control hence encoded password shown in decoded format. Details has been mention in figure Fig-1.5.

Storing data in encrypted form in MS Access table Fig-1.5

Fig-1.4

VBA Code

VBA code for On Click event of insert control:-

Private Sub cmdencode_Click()
Dim strpassword As String
Dim Db As DAO.Database
Set Db = CurrentDb
strpassword = encryptPW(Me.txtpass)
Db.Execute "insert into tblperson(usr_name,usr_password) values( '" & Me.txtname & "', '" & strpassword & "' )"
MsgBox "Encoded Password Stored into table", vbOKOnly
Me.txtname = Nothing
Me.txtpass = Nothing
End Sub

'VBA code for On Click event of fetch control
Private Sub cmddata_Click()
Me.txtname = Nothing
Me.txtpass = Nothing
Dim strdecpassword As String
Dim i As Integer
Dim Db As DAO.Database
Dim rst As DAO.Recordset
i = InputBox("Enter ID to fetch corresponding data", "Getting values")
Set Db = CurrentDb
Set rst = Db.OpenRecordset("select * from tblperson where ID = " & i)
strdecpassword = decryptPW(rst!usr_password)
MsgBox "Decoded Password Field", vbOKOnly
Me.txtname = rst!usr_name
Me.txtpass = strdecpassword
End Sub

'VBA code for On Click event of fetch control
Private Sub cmddata_Click()
Me.txtname = Nothing
Me.txtpass = Nothing
Dim strdecpassword As String
Dim i As Integer
Dim Db As DAO.Database
Dim rst As DAO.Recordset
i = InputBox("Enter ID to fetch corresponding data", "Getting values")
Set Db = CurrentDb
Set rst = Db.OpenRecordset("select * from tblperson where ID = " & i)
strdecpassword = decryptPW(rst!usr_password)
MsgBox "Decoded Password Field", vbOKOnly
Me.txtname = rst!usr_name
Me.txtpass = strdecpassword
End Sub

Private Sub cmdencode_Click()
Dim strpassword As String
Dim Db As DAO.Database
Set Db = CurrentDb
strpassword = encryptPW(Me.txtpass)
Db.Execute "insert into tblperson(usr_name,usr_password) values( '" & Me.txtname & "', '" & strpassword & "' )"
MsgBox "Encoded Password Stored into table", vbOKOnly
Me.txtname = Nothing
Me.txtpass = Nothing
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.