1

I am working on a project in which my BE is Microsoft SQL Server 2008 R2 and my FE is Access 2010 in accdb format. I am trying to open a recordset that uses a Scalar-Valued function like so:

SELECT [ID], [FirstName], [LastName], [Comment]
FROM [table general information]
WHERE dbo.udf_StripHTML([Comments]) like '%Hello World%'
ORDER BY [ID]

When I run this from within SQL Server everything works fine, but when I run this:

Dim rs As New ADODB.Recordset
Dim con As ADODB.Connection

Set con = Application.CurrentProject.Connection
rs.Open strQuery, con, adOpenStatic

I get Undefined function dbo.udf_StripHTML in expression

I am assuming I'm using the wrong connection. If so - is there a built in property that I can get the connection from? And if that's not the case, what's the problem?

Note: [table general information] is linked to a table in SQL Server

HansUp
  • 93,940
  • 11
  • 72
  • 130
Yotam
  • 8,667
  • 13
  • 43
  • 66

1 Answers1

2

You should be able to make use of the SQL Server user defined function by creating a pass-through query with a DAO.QueryDef object, as illustrated in my answer here.

Community
  • 1
  • 1
Gord Thompson
  • 107,466
  • 28
  • 191
  • 387