Using Functions stored on SQL Server

[Baesucks]

I have an MS-Access file with linked tables from a SQL Server, and want to create a query that uses the result of a function that resides on the SQL Server. Is there a way that can be done?

 

[Baesucks]

I could add... the function is defined on the server as...

ALTER  Function getVteResolvedStatus (@VTE_ID INT) Returns char(50) AS
BEGIN
	DECLARE @ResolveDate varchar (50)
	DECLARE @OutputString varchar (50)

	Set @ResolveDate = (SELECT TOP 1 ImagingDate  FROM ImagingResults WHERE VenousThrombusEventID = @VTE_ID AND ImagingResultTypeID BETWEEN 1 AND 2 ORDER BY ImagingDate)

	IF (@ResolveDate IS Not NULL) BEGIN
		 -- Record found indicating VTE is resolved
		SET @OutputString = 'Resolved ' + @ResolveDate
		Return @OutputString
	End
	Set @OutputString = 'Not Resolved'
	Return @OutputString

End

 

[PWise]

what type of file is the access file a .adp or .mdb

this id the syntex

Select db0.functionname()

if this is a mdb look @ passthru queries

for a adp

create a sql view

 

[PWise]

s/b

Select dbo.functionname()

 

[Baesucks]

Basically, I have 2 SQL Server databases, and need some users to have an ACCESS MDB file that links to tables from both databases. I want users to be able to create fairly simple queries, but use centrally stored functions that perform some of the calculations that might be commonly used.

Previously, I have written vba functions within Access applications to run such calculations, but if each user has their own front-end mdb to access the back-end data, keeping such functions up to date in each users front-end mdb file is unrealistic.

 

[PWise]

If I understand correctly you ae useing a .mdb file
then use this syntex in a pasthru query

select databasename.dbo.functionname(pram)

 

[Baesucks]

I'm not clear on how to specify the database. I'm using linked tables through an ODBC driver. So, when I look at the linked table manager the table for this data is defined as:

Pics_VenousThrombusEvents (DSN=Cascade_PICS; DATABASE=Thrombo-PICS

The SQL Server is called CASCADE, the database is called Thrombo-PICS.