Encrypting data (only) in an Access Database

[yosik]

Hi,
I would appreciate help on that.
What I am trying to do is have a Access Database wich accesses all sort of external dat, like text etc... I want this data to be inaccessible to users (encrypted) while at the same time it WILL be accessible via the Access Database.
Help will be greatly appreciated.
Thanks
Yossi

[Dermot]

This is an Access function I use to encrypt and decrypt data

Set the PASSWORD_KEY first in the Declaration section of the code module.
Const PASSWORD_KEY = "ASFRPJHKYFGTKLNVCZXERTGJHXOPKNMTR"

This is the function. It encrypts and decrypts depending on wheter you pass True or False to the fEncrypt arg.
'================================================
' Function: encrypt
'
' Purpose: To encrypt or decrypt a string using
' a predefined key value stored as
' the constant PASSWORD_KEY. The same
' string encrypted twice won't always
' look the same because the function
' chooses a random starting position
' within the key to encrypt values.
'
' Call: gResult = encryt("Hello World", true)
'
' In: strFull - String containing the
' text to encrypt/decrypt
'
' fEncrypt - Boolean set to TRUE to
' encrypt the string, FALSE
' to decrypt it.
'
' Out: none
'
' Returns: String - The encrypted/decrypted
' value if successful, ""
' otherwise
'
'================================================
Public Function encrypt(strFull As String, fEncrypt As Boolean) As String


Dim intInputPos As Integer
Dim intPassKeyPos As Integer
Dim strOutput As String
Dim intTemp As Integer
Dim strStartingPos As String

On Error GoTo HandleErr
If strFull = "" Then GoTo ExitHere

' Encrypt a value
If fEncrypt Then
' Initialize the random function
Randomize

' Determine the starting position to use in
' the encryption string
intPassKeyPos = Int(Len(PASSWORD_KEY) * _
Rnd + 1)

' Encrypt the starting position used in
' preparation to storing it in the middle of
' the encrypted result
intTemp = intPassKeyPos + _
Asc(left(PASSWORD_KEY, 1))
If intTemp > 255 Then intTemp = intTemp - 255
strStartingPos = Chr(intTemp)

' Encrypt the full string
For intInputPos = 1 To Len(strFull)
intPassKeyPos = intPassKeyPos + 1

' Wrap to the beginning of the key if we have
' have used up the last character.
If intPassKeyPos > Len(PASSWORD_KEY) Then
intPassKeyPos = 1
End If

' Add the value of the character to be
' encrypted to the value of the character
' stored at the current position in the key
intTemp = Asc(Mid(strFull, intInputPos, 1) _
) + Asc(Mid(PASSWORD_KEY, _
intPassKeyPos, 1))
If intTemp > 255 Then intTemp = intTemp - 255

' If we are at middle of the result string,
' insert our encrypted starting position so
' we can decrypt this string later.
If CInt(Len(strFull) / 2) + 1 = intInputPos _
Then strOutput = strOutput & strStartingPos

strOutput = strOutput & Chr(intTemp)

Next intInputPos

' Decrypt a value
Else
' Retrieve the encrypted starting position from
' the middle of the string to be decrypted
intPassKeyPos = Asc(Mid(strFull, _
CInt((Len(strFull) - 1) / 2) + 1, 1)) - _
Asc(left(PASSWORD_KEY, 1))

If intPassKeyPos < 0 Then _
intPassKeyPos = intPassKeyPos + 255

' Decrypt the full string
For intInputPos = 1 To Len(strFull)
intPassKeyPos = intPassKeyPos + 1

If intPassKeyPos > Len(PASSWORD_KEY) Then _
intPassKeyPos = 1

' Decrypt each character by subtracting the
' value of the corresponding character stored
' in the key.
intTemp = Asc(Mid(strFull, intInputPos, 1)) - _
Asc(Mid(PASSWORD_KEY, intPassKeyPos, 1))

If intTemp <= 0 Then intTemp = intTemp + 255

' If we are looking at the middle character,
' ignore it since its the encrypted starting
' position.
If intInputPos = CInt((Len(strFull) - 1) / 2) + _
1 Then
intPassKeyPos = intPassKeyPos - 1

Else
strOutput = strOutput & Chr(intTemp)

End If

Next intInputPos

End If

encrypt = strOutput



ExitHere:
Exit Function

HandleErr:
Select Case Err.Number
Case Else
encrypt = ""
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Error"
End Select
' End Error handling block.
End Function

Hope it helps

Dermot

[yosik]

Dermot.
Thanks a bunch Man!
I will try it out and let you know.
Just to be shure. This function encrypts the data at building time and unless you (and by that I mean the database) provide the password key, data remains encrypted.
Did I get it right?

Thanks

Yossi

[Dermot]

Yossi

You would use this function to encrypt the data as you inserted it into a table using DAO or ADO. It is not going to be much good if the data is already in the database but if you want to read data in from seperate files and insert it into a table then it should work fine.

When you want Access to display the data you will have to decrypt it. This could be done in the ControlSource of a text box for example like this.

=Encrypt([Field_Name_Here],False)

Dermot