How to call a function from ms sql server from access?

Question

Microsoft Access

There are programs for 2013 Access. In them tables from 2008 ms sql of the server are linked. The programs have forms where people edit data and changes through Update requests to linked tables are saved to the server. The problem is that one of the fields must be written in encrypted form. For example, a person enters the value 1342 in the form, and it should be written as Lr $ k # yi! &
On the ms sql server there are functions ID_Decrypt, ID_Encrypt (not stored procedures, but, I emphasize, scalar functions ), which just deal with this encryption . They take one parameter of type varchar, decrypt \ encrypt it and return the resulting value, also varchar.
The question is, how can I apply these functions to the desired field in the access?
What I have tried:
1. Make a passthrough update request in access. In this case, he does not swear at an unfamiliar function, which means he sees it. Moreover, if you do not give an input parameter, it says that a parameter is needed;
2. Code in VBA via ADO. I used about five different options, googled on stackoverflow, none of them worked. Too many options and combinations within them. Someone did it through Recordset, someone through Command + added different variations of the function call, a la "select ID_Decrypt ('1234') as res", "exec ID_Decrypt '123'" and others like them.
If maybe you have a working version of the code lying around (that is, it was guaranteed to work for you in a similar situation), please share.

Answer 1

Placed the following code in the "After update" event of the form:

Dim qdef As DAO.QueryDef
    Dim id As String
    Dim guest_id As String
    
    'взял из формы номер, который нужно зашифровать, и поместил в переменную
    id = [Forms]![anketa]![Pass_N]
    guest_id = [Forms]![anketa]![gid]
    
    'создал объект "запрос"
    Set qdef = CurrentDb.CreateQueryDef("")
    'поскольку это pass-through запрос, нужно заполнить свойство Connect. Я взял его от одной из прилинкованных таблиц
    qdef.Connect = CurrentDb.TableDefs("dbo_guest_names").Connect
    'мой запрос - на обновление, записей не возвращает
    qdef.ReturnsRecords = False
    qdef.SQL = "UPDATE [hostel].[dbo].[guest_names] " _
        & "SET id = hostel.dbo.IDEncrypt('" & id & "') " _
        & "WHERE guest_id = '" & guest_id & "'"
    qdef.Execute

Accordingly, dbo.IDEncrypt is the name of the scalar function on ms sql server, and hostel is the database in which this function is located. The code works for sure, the id is saved to the database in encrypted form.

Answer 2

Good.
In this formulation: Option 1 is simple and does not require additional gestures. What do you not like about it, why do you need a second one?
I am personally embarrassed: "to the field in accession." And why, in fact, not to make a stored procedure with parameters, form a string from an access, and perform everything in general, and not specifically update one field, on the server side?