Results 1 to 13 of 13
  1. #1
    lsbang221 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2020
    Posts
    6

    Encrypt column using VBA loop

    Hi,



    I am a total novice to Access and VBA, and am trying to hash a column of names using an encryption method found online. The only problem is the code only applies to a specified string, whereas I need it to apply the hash to an entire column. Here is my code:

    Option Compare Database
    Option Explicit

    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("MyTable", dbOpenDynaset, dbSeeChanges)

    Public Const Salt As Long = 54321
    Public Function Encrypt()

    Dim strChr As String
    Dim i As Integer

    Do Until rs.EOF
    For i = 1 To Len(rs("Names"))
    strChr = strChr & CStr(Asc(Mid(rs("Names"), i, 1)) Xor Salt)
    rs.MoveNext
    Loop
    End Function

    Not sure if this is right either, but then to call the function in the immediate window:
    ?encrypt()

    I'm getting the error:

    Compile error:

    Variable not defined

    I appreciate any help!
    Attached Thumbnails Attached Thumbnails Database.jpg  

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you put function in a general module? Module and function cannot have same name.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    lsbang221 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2020
    Posts
    6
    Yes, it's in the general module. Do they have the same name, seeing as the module is called general, and the function Encrypt? I'm not sure I understand, but I attached an image for your reference.

  4. #4
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here is how I think you should use it:

    Code:
    Private Sub cmdEncryptNames_Click()  'Add to a button on a form
    Dim db as Dao.Database
    Dim rs as Dao.Rcordset
    Dim sName as String, sEncryptedName as string
    
    
    Set db=CurrentDb
    Set rs=db.OpenRecordset("Temp",dbOpenDynaset,dbSeeChanges)
    
    
    If rs.recordcount=0 then GoTo Exit_Encrypt
    
    
    Do until rs.EOF
      sName =rs("Names")
      sEncryptedName =vcEncrypt(sName, 54321)
    	rs.Edit
    	rs("Names")=sEncryptedName 
    	rs.Update
    rs.moveNext
    Loop
    
    
    Exit_Encrypt:
    rs.close
    Set rs=Nothing
    Set db=Nothing
    End Sub
    
    
    
    
    Public Function vcEncrypt(strIn As String, lngKEY As Long) As String
    Dim strChr As String
    Dim i As Integer
     
    For i = 1 To Len(strIn)
    strChr = strChr & CStr(Asc(Mid(strIn, i, 1)) Xor lngKEY)
    Next i
    vcEncrypt = strChr
    End Function
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Module is named Module3.

    Can declare variables in header but need to Set within procedure. Move the Set lines into the function. If these objects are not going to be used by any other procedure, no need for header declaration. Declare within procedure.

    Global/Public variables lose their content in runtime error without proper error handling.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    lsbang221 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2020
    Posts
    6
    Thank you for your response. Unfortunately, when I try to implement the code, I receive the error the 'Argument not optional'. Also, I need to manipulate a column in the dataset itself, not a form.

    Click image for larger version. 

Name:	DB function.jpg 
Views:	19 
Size:	173.4 KB 
ID:	42332

    Is ?vcEncrypt() the correct way to reference the function in the Immediate box?




  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The revised function requires input to arguments. The function name is now vcEncrypt - to test it immediate window:

    ?vcEncrypt("some name", 54321)

    That literal input returns:

    543385436654364543565428954367543525436454356
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    lsbang221 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2020
    Posts
    6
    Ok, that makes sense, but how do I reference the Names column in the Temp table in ?vcEncrypt?

    ?vcEncrypt("Names", 54321) just encrypts the word Names, not the column.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The button click event does that.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    lsbang221 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2020
    Posts
    6
    Ok, I'm sorry for the back and forth, but is there any way to apply this code directly to my table without first creating a form?

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Of course. Put both code into general module. Change the button Click declaration to:

    Public Sub EncryptNames()
    ...
    End Sub



    Now just call the Sub in intermediate window:

    Call EncryptNames



    Or change the Function to Public in general module and call from query object:

    UPDATE Temp SET Names = vcEncrypt([Names], 54321)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Thanks June!

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    lsbang221 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2020
    Posts
    6
    That worked! Thanks Vlad and June.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Password protect/encrypt a field in an Access form
    By ndesforges in forum Programming
    Replies: 12
    Last Post: 10-22-2019, 04:26 PM
  2. encrypt decrypt data in column field
    By mroshdy in forum Programming
    Replies: 2
    Last Post: 05-18-2017, 06:44 PM
  3. Encrypt/Decrypt jpeg image/binary file
    By wackywoo105 in forum Programming
    Replies: 3
    Last Post: 10-07-2014, 08:59 AM
  4. Encrypt text in Access fields.
    By dontaylor in forum Access
    Replies: 2
    Last Post: 10-29-2013, 02:32 AM
  5. encrypt and decryt text
    By marianne in forum Access
    Replies: 6
    Last Post: 09-25-2010, 09:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums