How to use SQL Server UDF in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a User Defined function in SQL SErver that returns a single value.

I cannot figure out how to reference it in each of the following
circumstances:

in an IF THEN ELSE construct in VBA code

as the Control Source of a Text Box

Can anyone give me examples?
 
Bill Sturdevant said:
I have a User Defined function in SQL SErver that returns a single
value.

I cannot figure out how to reference it in each of the following
circumstances:

in an IF THEN ELSE construct in VBA code

as the Control Source of a Text Box

Can anyone give me examples?
Click to expand...

Is this in an MDB file, or in an ADP? If it's an ADP I don't know
whether it's easy or hard, as I haven't had occasion to do it in an ADP.

If it's in an MDB, then I think it will be easy if the UDF takes no
parameters: make a pass-through query that invokes the UDF and returns
the UDF's output value as a selected row. Then use DLookup to get the
value from the pass-through query.

If the UDF takes parameters, then I think you'd need to write your own
VBA function that uses ADO to invoke the function (supplying the
parameters) and get its return value, then return that value.

The above suggestions are purely theoretical, as I haven't actually
tried them.
 
Should have said it is an MDB.

Dirk Goldgar said:
Is this in an MDB file, or in an ADP? If it's an ADP I don't know
whether it's easy or hard, as I haven't had occasion to do it in an ADP.

If it's in an MDB, then I think it will be easy if the UDF takes no
parameters: make a pass-through query that invokes the UDF and returns
the UDF's output value as a selected row. Then use DLookup to get the
value from the pass-through query.

If the UDF takes parameters, then I think you'd need to write your own
VBA function that uses ADO to invoke the function (supplying the
parameters) and get its return value, then return that value.

The above suggestions are purely theoretical, as I haven't actually
tried them.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Running a little slow this morning!

Also should have said, I have some UDFs that take no parameters, and some
the take parameters.
 
Back
Top