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