Data decryption

[Trybbe]

Hi I need help with data decryption
I have a application in access which connects to a sql server database and using the provided tables it calculates salary increases per department, per month. Now last month I had to employ encryption on all tables that contains sensitive data.
That being done I am now trying to sort any applications that uses that information. Now if I run this query in sql server it works fine

OPEN Symmetric Key HRSecureSymmetricKey
DECRYPTION By PASSWORD = '*********';
 
SELECT     CAST(CONVERT(VARCHAR(50), DecryptByKey(ALLSTAFF.TotalPack)) AS Float) AS TotalPack, Headcountdec.BranchID, 
                      Headcountdec.headcount AS HC_old
FROM         Headcountdec INNER JOIN
                      ALLSTAFF ON Headcountdec.StaffNo = ALLSTAFF.EmplID AND Headcountdec.Period = ALLSTAFF.Period
WHERE     (Headcountdec.TransferType = 'NoMove') AND (Headcountdec.headcount = 'headcount') 
AND (CAST(CONVERT(VARCHAR(50), DecryptByKey(ALLSTAFF.TotalPack)) AS Float) <> 0)

So I need you to show me how I can run it in access db

 

[Dennisk]

In Access you can use what is called a 'Pass Through' query. Using this method you can construct an SQL query to be executed on the SQL server using SQL Server Code , Otherwise If you had a linked SQL table you would have to convert the above to Jet SQL thereby losing the functionality of the Decrypt function.

 

[Trybbe]

Hi Denis

Actually it is a linked table. However I have managed to include it in the query itself.

Like so. sql = "OPEN Symmetric Key HRSecureSymmetricKey DECRYPTION By PASSWORD = '*********' SQL STATEMENT Close Symmetric Key HRSecureSymmetricKey"

It's working fine. Thanks for the feedback