How to call SQL-Server user-defined function using access vba

Assuming that this application has working linked tables?

Then just create a pass-though query. Type in your functon with a select statement in front. Run the pt query. It should return your value. So, in the query builder, creaate that PT query. For the source of the PT query you can type in any valid t-sql, but in this case, it makes sense to type in a test of the function.

Say this:

SELECT dbo.textFunction(12)

If that works (and MAKE SURE you get it working - you not written one line of code). Once you have that PT query working?

Then you can use this VBA code:

Sub Test33434()

   Dim lngID           As Long
   Dim lngReturnValue  As Long
   
   lngID = 1234
   
   With CurrentDb.QueryDefs("qryPass")
      .SQL = "select dbo.testFunction(" & lngID & ")"
      lngReturnValue = .OpenRecordset()(0)
   End With

End Sub

So, in above we have the value we pass, and the return value. And note how we don't have to mess with any connection object in code - so using a saved PT query is nice, since you don't have connection strings in code.