473,322 Members | 1,610 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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.

Apr 10 '06 #1
7 1847
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.

Apr 10 '06 #2
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

Apr 10 '06 #3
Br
ad*****@gmail.com wrote:
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
But you wouldn't be calling a function the reserved word "sum" now would you
? :)
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.


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
Apr 10 '06 #4
Last time I checked one had to specify the owner of functions as in
SELECT XValue, YValue, dbo.MySum(Xvalue, YValue) FROM MyTable.

Apr 10 '06 #5
Br
Lyle Fairfield wrote:
Last time I checked one had to specify the owner of functions as in
SELECT XValue, YValue, dbo.MySum(Xvalue, YValue) FROM MyTable.


Of course.
--
regards,

Br@dley
Apr 10 '06 #6
> 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" <do***********@google.com> wrote in message
news:e1**********@news-02.connect.com.au...
ad*****@gmail.com wrote:
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


But you wouldn't be calling a function the reserved word "sum" now would
you ? :)
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.


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

Apr 11 '06 #7
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?

Apr 11 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Peter Frost | last post by:
Please help I don't know if this is possible but what I would really like to do is to use On Error Goto to capture the code that is being executed when an error occurs. Any help would be much...
49
by: Yannick Turgeon | last post by:
Hello, We are in the process of examining our current main application. We have to do some major changes and, in the process, are questionning/validating the use of MS Access as front-end. The...
7
by: ddsvi78 | last post by:
I am a complete idiot when it comes to access. Now that said, I work for a computer security company and one of our customers came to us with an access problem. They had been running fine for a...
17
by: DaveG | last post by:
Hi all I am planning on writing a stock and accounts program for the family business, I understand this is likely to take close to 2 years to accomplish. The stock is likely to run into over a...
52
by: Neil | last post by:
We are running an Access 2000 MDB with a SQL 7 back end. Our network guy is upgrading to Windows Server 2003 and wants to upgrade Office and SQL Server at the same time. We're moving to SQL Server...
12
by: rdemyan via AccessMonster.com | last post by:
I'm having a complicated linking problem. Before I get into the particulars, I'd like to know how Access links to the back-end file at startup, AFTER I've distributed my application to the client....
3
by: Sidu | last post by:
Ok well i'm still very new to apache, mysql, and php.. let me explain everything i have set up.. I have installed Appserv, for those of you who are unfamiliar with it.. it installs mysql, php,...
3
by: Paul Brady | last post by:
I have two users, Andrew and Nancy, who each have their own computer and can connect to a common database called "employees.mdb" which resides on a network server. On that same network server,...
2
hyperpau
by: hyperpau | last post by:
Before anything else, I am not a very technical expert when it comes to VBA coding. I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com). Ergo, I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.