×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Using Functions stored on SQL Server

Using Functions stored on SQL Server

Using Functions stored on SQL Server

(OP)
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

(OP)
I could add... the function is defined on the server as...

CODE

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

RE: Using Functions stored on SQL Server

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

this id the syntex

CODE

Select db0.functionname()

if this is a mdb look @ passthru queries

for a adp

create a sql view

RE: Using Functions stored on SQL Server

s/b

CODE

Select dbo.functionname()

RE: Using Functions stored on SQL Server

(OP)
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

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

CODE

select databasename.dbo.functionname(pram)

RE: Using Functions stored on SQL Server

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close