Syntax for calling a Transact-SQL UDF in Access 2003

[GLT101]

Hi,

The following query fails in Access 2003 with the error:
"Undefined function 'GetReplaceFactor' in expression"

GetReplaceFactor is a User defined Function in the SQL
server database. What is the correct syntax in Access
for me to call this function?

Cheers,
Geoff
SQL Query:
SELECT DISTINCT
TOP 100 PERCENT dbo_orddet.ordd_sku_cd AS sku_cd, dbo_sxsn_sn.sn_sn As
SN, dbo_sxsn_sn.sn_udfdt1 As ExpDate,
SUM(dbo_orddet.ordd_xno_users * GetReplaceFactor(dbo_sn_udfdec3)) AS
NumUsers,
dbo_orddet.ordd_release_dt As ReleaseDate
FROM dbo_sxsn_sn INNER JOIN
dbo_orddet ON dbo_orddet.ordd_xpk = dbo_sxsn_sn.ordd_xpk
WHERE (dbo_orddet.ordd_sku_cd = dbo_orddet.ordd_sku_cd) AND
(dbo_sxsn_sn.sn_sn = dbo_sxsn_sn.sn_sn) AND
(dbo_orddet.ordd_release_dt > DATEADD(DD, -14, GETDATE()) )
GROUP BY dbo_orddet.ordd_sku_cd, dbo_sxsn_sn.sn_sn, dbo_sxsn_sn.sn_udfdt1,
dbo_orddet.ordd_release_dt
HAVING SUM(dbo_orddet.ordd_xno_users *
dbo_GetReplaceFactor(dbo_sn_udfdec3))>10
ORDER BY SUM(dbo_orddet.ordd_xno_users *
dbo_GetReplaceFactor(dbo_sn_udfdec3)) DESC;

 

[[MVP] S.Clark]

ADP or MDB?
How are you calling it?
How are you calling other Stored Procs?
VBA?
Have you tried the Command object with ADO?

 

[GLT101]

Hi Steve,
This is an MDB.
The function needs to be called from within the SQL query.
I am not calling any Stored Procedures (although if I were I would expect to
be able to use "EXEC <Stored_Procedure_Name>" in the SQL query.

Sure, I could write the function into a VBA module and call that, but that
would duplicate the code that is already written and working as the UDF insde
the SQL server. What I want is to call the UDF directly from the SQL query in
Access,
exactly as you would if this were an SQL Server query running in any other
database application.

Thanks,
Geoff

 

[John Spencer (MVP)]

To do this you would need to use a PASS THROUGH query that was written in T-SQL.
Look up Access help on Pass Through queries.

 

[IhateAccessWithAPassion]

I sure wish Acess could handle this, just the way GLT101 needs it. I am a
SQL Server DBA and all my users use MS Access. I am trying to implement
business rules by building UDFs on the Data Warehouse server, so users can
use these in their queries and the Business RUles are maintained on the
server.

Problem with Pass Through queries is that you can't do any make (local)
table queries, which the users do a lot of.