Import SQL Server User-Defined Functions into Access 2002

[Guest]

I have an Access 2002 database consisting mostly of linked tables from a SQL
Server 2000 database. I was hoping to import a SQL Server user-defined
function from the SQL database into Access. It's not one of the commonly
requested functions (calculate working days between two dates, etc.). It's
proprietary to our business needs.

In general, can this be done and if so, how? I tried copying and pasting but
got syntax errors. I've also tried creating a SQL View but for some reason,
the "Functions" tab is not populated so I can't select my functions. I will
post this as a separate question in a SQL Server newsgroup.

 

[Sylvain Lafontaine]

The easiest way is probably to convert it to an ordinary VBA function. To
call it from a Select statement, you must use the type Variant for your
parameters and return value; however, I don't remember if you must use the
ByRef or ByValue mode. Intuitively, I would go with ByValue first.

Be carefull where you are using this kind of function as it can have a
servere performance penalty.

Look at http://www.aspfaq.com/show.asp?id=2519 for a better way of
calculating the difference in working days between two dates then using a
function.

 

[aaron.kempf]

you should be using access data projects not your wimpy mdb format.

access data projects supports sql udf without any problem

-aaron

 

[Douglas J. Steele]

I'm unaware of any way to do this.

Functions in SQL Server are quite different than functions in Access.

 

[John Vinson]

I have an Access 2002 database consisting mostly of linked tables from a SQL
Server 2000 database. I was hoping to import a SQL Server user-defined
function from the SQL database into Access. It's not one of the commonly
requested functions (calculate working days between two dates, etc.). It's
proprietary to our business needs.

Not easily. T-SQL and Access VBA are both powerful, full featured
programming languages but they are DIFFERENT languages. SQL functions
will not compile in VBA, and vice versa.

John W. Vinson[MVP]

 

[Guest]

Thanks, I was afraid of that. I'll have to work on rewriting it for Access.

 

[aaron.kempf]

functions in SQL are much _BETTER_ than functions in VBA.


Lose the training wheels; just use Access Data Projects.

ADP uses SQL Server; which is an enterprise level database system.

MDB is for retards and babies

-Aaron
ADP Nationalist