Access and SQL Server - Module Problem

i recently started using .adp project file in access 2003 with sql
server 2000 as backend. when using regular .mdb in access 2003, i could
call a module in a query simply by writing for example: Calc: Sum(A,B)

where i had a function in VBA for example:
Public Function Sum(A,B)
....
A=40
B=60
......
Sum=A+B
End Function

i was wondering how i can do the same thing using Access2003 ADP and
SQL Server 2000. when i try it now writing Calc: Sum(A,B) in a view or
function i get an error like unrecognized function. any help would be
much appreciated.
thanks.

re: Access and SQL Server - Module Problem

CORRECTION:
i recently started using .adp project file in access 2003 with sql
server 2000 as backend. when using regular .mdb in access 2003, i could

call a module in a query simply by writing for example: Calc:
Sum([A],[b])
my table has columns named A and B with numbers.

i have a module function in VBA for example:
Public Function Sum(A,B)
Sum=A+B
End Function

i was wondering how i can do the same thing using Access2003 ADP and
SQL Server 2000. when i try it now writing Calc: Sum(A,B) in a view or
function i get an error like unrecognized function. any help would be
much appreciated.
thanks.

re: Access and SQL Server - Module Problem

MS-SQL has no knowledge of your VBA function or anything about your
Access application at all. Beginning with MS-SQL one can create
functions to be used by Stored Procedures or Views or T-SQL in general
in the Server Database itself.
eg:
CREATE FUNCTION dbo.ProperCase
(
@VarString varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @NewString varchar(8000)
DECLARE @Length int
DECLARE @Position int
DECLARE @CharAtPosition varchar(1)
DECLARE @ASCIIOfChar tinyint
DECLARE @WordStart bit

SET @NewString = ''
SET @Length = LEN (@VarString)
SET @Position = 1
SET @WordStart = 1

WHILE (@Position <= @Length)
BEGIN
SET @CharAtPosition = LOWER(SUBSTRING (@VarString, @Position, 1))
IF (@WordStart = 1)
BEGIN
SET @CharAtPosition = UPPER (@CharAtPosition)
END

SET @ASCIIOfChar = ASCII(@CharAtPosition)
IF ((@ASCIIOfChar>64 AND @ASCIIOfChar<92) OR (@ASCIIOfChar>96 AND
@ASCIIOfChar<123))
SET @WordStart = 0
ELSE
SET @WordStart = 1

SET @NewString = @NewString + @CharAtPosition

SET @Position = @Position + 1
END

RETURN @NewString
END

re: Access and SQL Server - Module Problem

adserte@gmail.com wrote:[color=blue]
> CORRECTION:
> i recently started using .adp project file in access 2003 with sql
> server 2000 as backend. when using regular .mdb in access 2003, i
> could
>
> call a module in a query simply by writing for example: Calc:
> Sum([A],[b])
> my table has columns named A and B with numbers.
>
> i have a module function in VBA for example:
> Public Function Sum(A,B)
> Sum=A+B
> End Function[/color]

But you wouldn't be calling a function the reserved word "sum" now would you
? :)
[color=blue]
> i was wondering how i can do the same thing using Access2003 ADP and
> SQL Server 2000. when i try it now writing Calc: Sum(A,B) in a view or
> function i get an error like unrecognized function. any help would be
> much appreciated.
> thanks.[/color]

Think about it. Your view is stored in SQL Server (or your SQL statement is
sent to SQL Server to execute). How is it supposed to know about a VBA
function you've written in Access?

There are a few ways you can do it. You can use a stored procedure or a
user-defined function. If you are returning a single value then perhaps a
UDF might be appropriate.

eg. (may not be the most elegant syntax:)


CREATE FUNCTION MySum(@XValue AS INT, @YValue AS INT)
RETURNS INT
AS
BEGIN
DECLARE @MyTotal AS INT
@MyTotal = @XValue = YValue
RETURN @MyTotal
END

New your old SQL syntax should work as it did in Access.

SELECT XValue, YValue, MySum(Xvalue, YValue) FROM MyTable

--
regards,

Br@dley

re: Access and SQL Server - Module Problem

Last time I checked one had to specify the owner of functions as in
SELECT XValue, YValue, dbo.MySum(Xvalue, YValue) FROM MyTable.

re: Access and SQL Server - Module Problem

Lyle Fairfield wrote:[color=blue]
> Last time I checked one had to specify the owner of functions as in
> SELECT XValue, YValue, dbo.MySum(Xvalue, YValue) FROM MyTable.[/color]

Of course.
--
regards,

Br@dley

re: Access and SQL Server - Module Problem

> sent to SQL Server to execute). How is it supposed to know about a VBA

Whereas if the function was written in MS C++, you could
just declare the DLL in SQL Server. Or if it was written
in .Net, you could just declare the assembly in SS Express.

Sad, isn't it.

(david)


"Br@dley" <dontlookforme@google.com> wrote in message
news:e1ej24$gvn$1@news-02.connect.com.au...[color=blue]
> adserte@gmail.com wrote:[color=green]
>> CORRECTION:
>> i recently started using .adp project file in access 2003 with sql
>> server 2000 as backend. when using regular .mdb in access 2003, i
>> could
>>
>> call a module in a query simply by writing for example: Calc:
>> Sum([A],[b])
>> my table has columns named A and B with numbers.
>>
>> i have a module function in VBA for example:
>> Public Function Sum(A,B)
>> Sum=A+B
>> End Function[/color]
>
> But you wouldn't be calling a function the reserved word "sum" now would
> you ? :)
>[color=green]
>> i was wondering how i can do the same thing using Access2003 ADP and
>> SQL Server 2000. when i try it now writing Calc: Sum(A,B) in a view or
>> function i get an error like unrecognized function. any help would be
>> much appreciated.
>> thanks.[/color]
>
> Think about it. Your view is stored in SQL Server (or your SQL statement
> is sent to SQL Server to execute). How is it supposed to know about a VBA
> function you've written in Access?
>
> There are a few ways you can do it. You can use a stored procedure or a
> user-defined function. If you are returning a single value then perhaps a
> UDF might be appropriate.
>
> eg. (may not be the most elegant syntax:)
>
>
> CREATE FUNCTION MySum(@XValue AS INT, @YValue AS INT)
> RETURNS INT
> AS
> BEGIN
> DECLARE @MyTotal AS INT
> @MyTotal = @XValue = YValue
> RETURN @MyTotal
> END
>
> New your old SQL syntax should work as it did in Access.
>
> SELECT XValue, YValue, MySum(Xvalue, YValue) FROM MyTable
>
> --
> regards,
>
> Br@dley
>[/color]

re: Access and SQL Server - Module Problem

Very sad.
MS says that the .Net function will be much faster than the T-SQL
function.
Are we moving to the situation where the use of MS-SQL requires the use
of another MS application in order to be effcient?
Arrrrrrrrrrrggggggggghhhhhhhhh?