Getting a result from ODBC connected SQL Server Function 1

[Baesucks]

I have a front-end access database file (mdb) which connects to linked tables from 2 different SQL server databases using ODBC. I want each user to have a similar front-end mdb file but be able to create and store their own queries. I want users to have access to several custom functions, so rather than write those functions in vba I want to have them as functions on the SQL server.

How do I reference a function located on the SQL server to get a result from a front-end mdb with ODBC linked tables.

As an example, lets say I have a function on the server called getColor() that accepts a number 1 thru 10, and returns a string representing a color (ie. "BLUE"). I want to build a query with a result field that calls this function something like...

SELECT ColorID, <sql-server-function>getColor([ColorID]) as ColorName FROM CarCollection;

 

[genomon]

Have you looked at pass-through queries in Access help?

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[Baesucks]

Yes, but HOW do I specify the ODBC connection, the database name, and the function name in the pass-through query?

 

[genomon]

It would likely be a 2 step process. Gotta go now but be back tomorrow. BTW, could you use DLookup() on a linked table for this?

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[Baesucks]

I don't want to use vba code in the front-end mdbs. I want everyone to be able to call the SAME function from the SAME place, so it needs to be on the SQL server.

 

[Baesucks]

I should also add... the function I'm trying to call is more complicated than a DLookup. thats why it needs to have several lines of code to figure out what the return result is going to be. My example of getColor above is an over-simplification of what the real function is doing.

 

[dhookom]

You can create a connection in the properties of a pass-through. Once this has been specified, your SQL syntax is exactly the same as running the query in Query Analyzer or whatever.

Duane
Hook'D on Access
MS Access MVP

 

[Baesucks]

Awesome dhookom!