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
  • 95,961
  • 11
  • 77
  • 135
Yotam
  • 9,789
  • 13
  • 47
  • 68

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
  • 116,920
  • 32
  • 215
  • 418