SQL Server user-defined functions from access??

[OPMCoordinator]

hello

i have spent two days now (on and off) searching for an answer on this one. (the searching itsself actually eclipsed the specific reason for searching this time as i learnt a HEAP of other stuff on the way )

but i am still stumped on the original question.

I have an SQL server (finally) that i have (finally) figured out how to write and use user-defined functions (and stored procedures) on the server. Along the way i learnt that if you use user-defined functions that are built in access (i think mainly in the where clause of the sql string) that access would not get the sql server to do the processing, but will pull the whole of the table/s accross the network and process locally itsself. that defeats the reasoning behind me getting (finally) the server.

my question is then
Can you use the userdefined functions built on the SQL server in an access front end? (on forms, in reports, in queries, in code etc)

Cheers

Mike C

 

[WayneRyan]

Mike,

If you use an ODBC connection, you can execute procedures that are
stored on your server. You can even create, delete them and pass
parameters

DbConnection.Execute ("CheckDelete_" & Me.lstTables.ItemData(lngTable))

If the VBA string strProc contains a valid T-SQL script you can create a
new procedure by:

DbConnection.Execute (strProc)

Recordsets are WAY slower than letting the server do the work. Use
procedures, functions and above all triggers on the server.

Just linking the tables in Access is OK, but the SQL server db engine is
pretty impressive.

Wayne

 

[OPMCoordinator]

hello wayne!

I think my explanation was a bit confusing.

I have userdefined functions that are stored on the SQL Server.

I want to use those from access rather than userdefined functions that i have in access.

i think i can use them by sending a string to the server using a connection as you said. what i need to know is can i use them in places like the query design grid in access. also in the report / form side of things in unbound control boxes etc. i can use the result of them via the data source if they were included as part of the sql to build the record set.

it may be i need to have a duplicate (complimentary) set of user defined functions in a module in the front end, but i would prefer not to if i could avoid it.

hope this makes a little more sense.

cheers
Mike C

 

[Pat Hartman]

If you want to use SQL user-defined functions, you will need to use pass-through queries. Be aware that pass-through queries are NOT updatable so they are useless as the recordsource for a form.

There are articles in the Microsoft knowledge base regarding ODBC optimization. In general, Jet will try its best to send the entire query to the server for processing. Some things get in the way though. For example, using user-defined functions in the where clause. The database server cannot execute VBA user-defined functions so Jet requests the entire table and applies the selection crteria locally. By the same token Jet cannot execute SQL server user-defined functions. If you try to send one, the query will fail.