HOW do I ' create function ' ???? ' cannot change object type in ascript ' ????

[Bob]

this is about as newbie as it gets, and I can't seem to find an answer
in any source!!!!

all I want to do is create a user defined function.
I'm running access 2k project as the front end, and sql-server (7) as
back end.

WHERE exactly do I go to create an user defined function??!??

if I try to create a new procedure, and say 'create function ' - I
get ' cannot change object type '.

so if I don't create a function in the 'stored procedures' area of an
adp - WHERE EXACTLY do I go to do this????

if this can't be done w/ sql 7 - using this syntax - how do I create a
user defined table or function in sql 7.... or more specifically - how
do I reference a function from a view that will work.
(defining a function as a module - apparently can NOT be used in a
view)

TIA -
Bob

 

[Sylvain Lafontaine]

First, why all these ??!?? and these UPPERCASES everywhere in your post?

Second, you cannot create an User Defined Function (UDF) on SQL-Server 7;
this require at least the version 2000. Also, it's a bad idea to use Access
2000 to create an ADP project as this version is full of bug. I would
suggest to use the 2003 version, if possible; otherwise, the 2007 version
but fully patched.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Sylvain Lafontaine]

More info about UDF in the following article:
http://www.15seconds.com/Issue/000817.htm .

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Bob]

First, why all these ??!?? and these UPPERCASES everywhere in your post?

Second, you cannot create an User Defined Function (UDF) on SQL-Server 7;
this require at least the version 2000. Also, it's a bad idea to use Access
2000 to create an ADP project as this version is full of bug. I would
suggest to use the 2003 version, if possible; otherwise, the 2007 version
but fully patched.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site:http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

Sylvain-

TYVM for your reply; apologies for my improper (excessive) use of caps
& punct.

I hear what you're saying.... but I'm in a situation, that due to
numerous reasons, will not change.

So within the context of sql 7, and access 2k....

is there some other way to provide the functionality of having a user
defined function that can be referenced in a view?

TX again IA...

 

[Bob]

More info about UDF in the following article:http://www.15seconds.com/Issue/000817.htm.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site:http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

Ooooo... excellent reference.... at least for future
endeavors ..... TY

 

[Sylvain Lafontaine]

Sylvain-

TYVM for your reply; apologies for my improper (excessive) use of caps
& punct.

I hear what you're saying.... but I'm in a situation, that due to
numerous reasons, will not change.

So within the context of sql 7, and access 2k....

is there some other way to provide the functionality of having a user
defined function that can be referenced in a view?

TX again IA...

For your first question, SQL-7 is not really a problem for working with ADP
but ADP 2000 might be instead of 2003 or 2007 might be for some loss of
functionality here and there.

For your second question, without knowing what you want to do with this
function and in under which context, it's hard to suggest you any
alternative.

If you want to retrieve the result in ADP, the best way would be to use a
stored procedure. If you wanted to use it inside a View, then you could use
another View but then, you cannot have a parameterized View or a
Multi-statement View; so this limit your possibilities.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Bob]

For your first question, SQL-7 is not really a problem for working with ADP
but ADP 2000 might be instead of 2003 or 2007 might be for some loss of
functionality here and there.

For your second question, without knowing what you want to do with this
function and in under which context, it's hard to suggest you any
alternative.

If you want to retrieve the result in ADP, the best way would be to use a
stored procedure. If you wanted to use it inside a View, then you could use
another View but then, you cannot have a parameterized View or a
Multi-statement View; so this limit your possibilities.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site:http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

actually - what I really want to do is quite simple in concept...

I have a complicated math formula which I will represent simply as y=mx
+b.
I have a public function setup in my adp's module section that works
perfectly.
in this example, m & b are constants that are read from a table; x is
the input to the function, and obviously, y would be the output.

I want to create a view that would be able to have a column that uses
this function.
so....
select myFunction([mytbl.x]) as y from mytbl

unfortunately - view doesn't recognize module functions - so
procedures are left as only alternative.
the only way that I know you can access a procedure is with the exec
statement, and I haven't figured out if/how this can be used in a
select statement for a view.

so ideally (all things as they are), I could do something like this:
select (exec myFunction [mytbl.x]) as y from mytbl

but I haven't figured out if you can use exec within a select, because
all my attempts have generated various errors.

TIA -
Bob

 

[Sylvain Lafontaine]

actually - what I really want to do is quite simple in concept...

I have a complicated math formula which I will represent simply as y=mx
+b.
I have a public function setup in my adp's module section that works
perfectly.
in this example, m & b are constants that are read from a table; x is
the input to the function, and obviously, y would be the output.

I want to create a view that would be able to have a column that uses
this function.
so....
select myFunction([mytbl.x]) as y from mytbl

unfortunately - view doesn't recognize module functions - so
procedures are left as only alternative.
the only way that I know you can access a procedure is with the exec
statement, and I haven't figured out if/how this can be used in a
select statement for a view.

so ideally (all things as they are), I could do something like this:
select (exec myFunction [mytbl.x]) as y from mytbl

but I haven't figured out if you can use exec within a select, because
all my attempts have generated various errors.

TIA -
Bob

SQL-Server should be used for storing and retrieving data, not for doing
computation. If you have already set up a function an in ADP module that
give you the result, then you should do this computation in the ADP
application itself and use SQL only for retrieving the data.

I will do some tests later and I come back again on this question in a few
hours.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Bob]

actually - what I really want to do is quite simple in concept...

I have a complicated math formula which I will represent simply as y=mx
+b.
I have a public function setup in my adp's module section that works
perfectly.
in this example, m & b are constants that are read from a table; x is
the input to the function, and obviously, y would be the output.

I want to create a view that would be able to have a column that uses
this function.
so....
select myFunction([mytbl.x]) as y from mytbl

unfortunately - view doesn't recognize module functions - so
procedures are left as only alternative.
the only way that I know you can access a procedure is with the exec
statement, and I haven't figured out if/how this can be used in a
select statement for a view.

so ideally (all things as they are), I could do something like this:
select (exec myFunction [mytbl.x]) as y from mytbl

but I haven't figured out if you can use exec within a select, because
all my attempts have generated various errors.

TIA -
Bob

SQL-Server should be used for storing and retrieving data, not for doing
computation. If you have already set up a function an in ADP module that
give you the result, then you should do this computation in the ADP
application itself and use SQL only for retrieving the data.

I will do some tests later and I come back again on this question in a few
hours.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site:http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

Sylvain - TYVM for any additional light you can shed on the subject...

I agree in principle with what you're saying.... except that a view
really isn't stored in the adp - it's on sql server.
and there are no "queries" in an adp (which could reference/access the
adp's modules). So this presents a bit of a quandry [sp?]....