Using Functions stored on SQL Server Using Functions stored on SQL Server Baesucks (Programmer) (OP) 26 Jun 08 13:59 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? RE: Using Functions stored on SQL Server Baesucks (Programmer) (OP) 26 Jun 08 14:07 I could add... the function is defined on the server as...CODEALTER Function getVteResolvedStatus (@VTE_ID INT) Returns char(50) ASBEGIN 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 @OutputStringEnd RE: Using Functions stored on SQL Server PWise (Programmer) 26 Jun 08 14:09 what type of file is the access file a .adp or .mdbthis id the syntex CODESelect db0.functionname()if this is a mdb look @ passthru queriesfor a adp create a sql view RE: Using Functions stored on SQL Server PWise (Programmer) 26 Jun 08 14:13 s/bCODESelect dbo.functionname() RE: Using Functions stored on SQL Server Baesucks (Programmer) (OP) 26 Jun 08 14:31 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. RE: Using Functions stored on SQL Server PWise (Programmer) 26 Jun 08 14:43 If I understand correctly you ae useing a .mdb filethen use this syntex in a pasthru queryCODEselect databasename.dbo.functionname(pram) RE: Using Functions stored on SQL Server Baesucks (Programmer) (OP) 26 Jun 08 15:13 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.