Can someone please help me in converting the following stored procedure
(in SQL Server 2005) to its equivalent query in MS-Access?

ALTER PROCEDURE dbo.FGlobal
@ClientName varchar(150),
@Scrip varchar(150) = NULL,
@Quantity int = 0,
@Price money,
@TTime datetime = NULL,
@ContNoteNo varchar(50),
@AddQty int
AS
DECLARE
@Qty int

SET @Qty = (SELECT Quantity FROM tblFG WHERE ClientName = @ClientName
AND Scrip = @Scrip AND ContNoteNo = @ContNoteNo)

IF (@Qty IS NULL)
BEGIN
INSERT INTO tblFG (ClientName, Scrip, Quantity, Price, TTime,
ContNoteNo)
VALUES (@ClientName, @Scrip, @Quantity, @Price, @TTime,
@ContNoteNo)
END
ELSE
BEGIN
UPDATE tblFG SET Quantity = @AddQty + @Qty WHERE ClientName =
@ClientName AND Scrip = @Scrip AND ContNoteNo = @ContNoteNo
END

Since last 3 hours, I have been trying to convert the above stored
procedure which exists in SQL Server 2005 into a MS-Access query but
have been encountering problems.

Can someone please help me convert the above stored procedure into a
MS-Access query?

PLEASSSSSSE.......I need a solution desperately....

The code in access would look like:

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Can someone please help me in converting the following stored procedure
> (in SQL Server 2005) to its equivalent query in MS-Access?
>
> ALTER PROCEDURE dbo.FGlobal
> @ClientName varchar(150),
> @Scrip varchar(150) = NULL,
> @Quantity int = 0,
> @Price money,
> @TTime datetime = NULL,
> @ContNoteNo varchar(50),
> @AddQty int
> AS
> DECLARE
> @Qty int
>
> SET @Qty = (SELECT Quantity FROM tblFG WHERE ClientName = @ClientName
> AND Scrip = @Scrip AND ContNoteNo = @ContNoteNo)
>
> IF (@Qty IS NULL)
> BEGIN
> INSERT INTO tblFG (ClientName, Scrip, Quantity, Price, TTime,
> ContNoteNo)
> VALUES (@ClientName, @Scrip, @Quantity, @Price, @TTime,
> @ContNoteNo)
> END
> ELSE
> BEGIN
> UPDATE tblFG SET Quantity = @AddQty + @Qty WHERE ClientName =
> @ClientName AND Scrip = @Scrip AND ContNoteNo = @ContNoteNo
> END
>
> Since last 3 hours, I have been trying to convert the above stored
> procedure which exists in SQL Server 2005 into a MS-Access query but
> have been encountering problems.
>
> Can someone please help me convert the above stored procedure into a
> MS-Access query?
>
> PLEASSSSSSE.......I need a solution desperately....
>

You can use sql, or even a reocrd set. I think in this case, I would use a
reocrdset

(since you have to "find" the reocrd...and if we find it..then we can update
the reocrd in palce instead of haivng to execute a "update" command that
also ahve the "where cluase". In your sql procueded, the "where" clause is
executed two times (one for the instianly qty, and again for the update --
in the follwign example...if the reocrd is exists...and is retinved...we
don't execute a 2nd where clause in a 2nd update command...).



The follwing is "air code", but does give you the general idea:

Public Sub FGlobal(ClientName As String, _
Scrip As String, _
Quanity As Integer, _
Price As Currency, _
TTime As Date, _
ContNoteNo As String, _
AddQty As Integer)

Dim strSql As String
Dim rstQty As DAO.Recordset


strSql = "select * from tblFG " & _
"where ClientName = '" & ClientName & "'" & _
" and scrip = '" & Scrip & "'" & _
" and ContNoteNo = '" & ContNoteNo & "'"


Set rstQty = CurrentDb.OpenRecordset(strSql)

If rstQty.RecordCount = 0 Then
rstQty.AddNew
rstQty!ClientName = ClientName
rstQty!Scrip = Scrip
rstQty!Quantity = Quantity
rstQty!Price = Price
rstQty!TTime = TTime
rstQty!ContNoteNo = ContNoteNo
Else
rstQty.Edit
rstQty!Quantity = AddQty + rstQty!Quanitity
End If

rstQty.Update
rstQty.Close

End Sub


You could also use "sql udpate commands", but I think the above is bit more
easy...and more usefull...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)

Albert, thanks for your suggestion but I guess you haven't understood
what I am looking out for.

The SQL Server stored procedure that I have shown in post #1 in this
thread - I want to convert that stored procedure into a MS-Access
Query. What you have suggested would probably work in a ASP or ASP.NET
script but can you create a sub, use a recordset etc. in Access to
create a Query?

I am not very much familiar with Access; hence I may be wrong but I
have never come across anything like what you have suggested in
MS-Access queries. Forgive me if I am wrong.

In fact, I just copy-pasted your code in a MS-Access Query (in SQL
View) but when I tried to save it, Access generated the following
error:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT', or 'UPDATE'.

pointing to the very first word, which is 'Public', in the query you
have cited.

Now when I execute the stored procedure in SQL Server, I am first
prompted to enter a value for the @ClientName variable, then a value
for the @Scrip variable, then a value for the @Quantity variable, then
a value for the @Price variable, then a value for the @TTime variable,
then a value for the @ContNoteNo variable & finally a value for the
@AddQty variable. Depending upon the values fed to the stored
procedure, the procedure retrieves/updates records from/in the table
named tblFG.

I want exactly the same to happen when I execute the Query in
MS-Access.


Albert D. Kallal wrote:
> You can use sql, or even a reocrd set. I think in this case, I would use a
> reocrdset
>
> (since you have to "find" the reocrd...and if we find it..then we can update
> the reocrd in palce instead of haivng to execute a "update" command that
> also ahve the "where cluase". In your sql procueded, the "where" clause is
> executed two times (one for the instianly qty, and again for the update --
> in the follwign example...if the reocrd is exists...and is retinved...we
> don't execute a 2nd where clause in a 2nd update command...).
>
>
>
> The follwing is "air code", but does give you the general idea:
>
> Public Sub FGlobal(ClientName As String, _
> Scrip As String, _
> Quanity As Integer, _
> Price As Currency, _
> TTime As Date, _
> ContNoteNo As String, _
> AddQty As Integer)
>
> Dim strSql As String
> Dim rstQty As DAO.Recordset
>
>
> strSql = "select * from tblFG " & _
> "where ClientName = '" & ClientName & "'" & _
> " and scrip = '" & Scrip & "'" & _
> " and ContNoteNo = '" & ContNoteNo & "'"
>
>
> Set rstQty = CurrentDb.OpenRecordset(strSql)
>
> If rstQty.RecordCount = 0 Then
> rstQty.AddNew
> rstQty!ClientName = ClientName
> rstQty!Scrip = Scrip
> rstQty!Quantity = Quantity
> rstQty!Price = Price
> rstQty!TTime = TTime
> rstQty!ContNoteNo = ContNoteNo
> Else
> rstQty.Edit
> rstQty!Quantity = AddQty + rstQty!Quanitity
> End If
>
> rstQty.Update
> rstQty.Close
>
> End Sub
>
>
> You could also use "sql udpate commands", but I think the above is bit more
> easy...and more usefull...
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> (E-Mail Removed)

> The SQL Server stored procedure that I have shown in post #1 in this
> thread - I want to convert that stored procedure into a MS-Access
> Query.

Your miss-using the term "query" and "stored" procedure here. you
somehow seem to think that a sql query is the same as a procedure.
That is GRAND CANYON of difference.

You can't turn a stored procedure in sql server to sql in sql server

(how can you do that???? - what you are asking makes no sense at
all).

>What you have suggested would probably work in a ASP or ASP.NET
> script but can you create a sub, use a recordset etc. in Access to
> create a Query?

We not creating a query...we are creating a procedure that runs code...
big big big difference here. but, the answer is yes..that is how
it is normally done in ms-access.

In oracle, the stored procedure language is not sql, but in pl-sql.

In ms-access, procedure code is written in code modules.

In sql server, it is called t-sql. that stored procedure you posted is
MOST CERTAINLY NOT sql, but is a program written in a
programming language. In your case, that language was t-sql.

So, my point is that you have to use the programming language
of the platform you are using to write procedures.

When you write procedures and
code in ms-access, you use VBA. So, that procedure code I posted
was written in the language you use in ms-access.

You have to convert that program code to something compatible
in ms-access. And, you use the syntax, coding and programming
languages of the platform you are using.


>
> I am not very much familiar with Access; hence I may be wrong but I
> have never come across anything like what you have suggested in
> MS-Access queries.

You did not post a query...you posed a program. Once again, you
seem to think that procedural sql code for sql server is going to work
on Oracle, MySql, sybase, or ms-access.....nothing could be further
from the truth.

Once again:
You did not post a sql select, or update query, you
posted a procedural piece of code written in a programming
langue for a particular platform. In your case, the langue
used was t-sql....

I think it goes without saying that code posted for FoxPro, or c++ is
not going to work in ms-access. I just assumed this issue was
quite obvious. You do realize that have to change the syntax of
the code you have written.

> In fact, I just copy-pasted your code in a MS-Access Query (in SQL
> View) but when I tried to save it, Access generated the following
> error:

You have to place procedure access code in a code module.
The query builder does not let you write code in ms-access. You can
write sql update, or selects in the query builder, but you have to use
the code editor and VBA code for procedural stuff (that is what it is
for)....


> Now when I execute the stored procedure in SQL Server, I am first
> prompted to enter a value for the @ClientName variable

where are prompted? What application? You mean your web server prompts?
Your accounting package? Who is doing this prompting? What application
are you talking about that is able to make these prompts? What happens
if a web server call that stored procedure? do the prompts magically
travel across the web?

If you need some type of user interface to prompt for data, then you
likely should build a form and some
type of interface for that purpose. You can then have a button on the form
and it can call that procedure, and pass the required values.

I was not aware that the code you posed would actually create user
input prompts. If a web server, or accounting package, or vb6
program executes that stored procedure you posted, how does
the sql serer know to make the accounting package prompt the user??
(answer...it can't!!!).

How is sql server express going to know "where" to send the prompts
to? I am not aware that sql server express is
capable of prompting the user, or even knowing "where" to prompt the
user.

You going to be using some type of "client" tool to connect to sql server
in this case. Perhaps that client tool is able to supply the parameters, but
I not aware of what type of interface that sql server express will supply.
does it prompt with a nice form? Does it allow cancel?

Anyway, the procedure I posted will work...you have to place it in a code
module. And, it will NOT prompt you for the parameters, you have to
*supply* the parameters when you call that code. So, you have to build a
form, or some type of interface. I would assumed that you had to
provide some type of interface prior to using ms-access also....

So, to clarify this, code written in ms-access is written in the code edit.
The query builder, and sql in ms-access is ONLY used for building sql
statements (they can be select, delete, update, and even ddl
statements...but, you can't run code in the ms-access query builder...you
have to use VBA).

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)

> where are prompted? What application? You mean your web server prompts?
> Your accounting package? Who is doing this prompting? What application
> are you talking about that is able to make these prompts? What happens
> if a web server call that stored procedure? do the prompts magically
> travel across the web?

> How is sql server express going to know "where" to send the prompts
> to? I am not aware that sql server express is
> capable of prompting the user, or even knowing "where" to prompt the
> user.

> You going to be using some type of "client" tool to connect to sql server
> in this case. Perhaps that client tool is able to supply the parameters, but
> I not aware of what type of interface that sql server express will supply.
> does it prompt with a nice form? Does it allow cancel?

Open SQL Server Management Studio & navigate to that stored procedure.
Right-click the stored procedure & then select 'Execute Stored
Procedure'. A new window opens up which lists all the input parameters
(@ClientName, @Scrip, @Type etc.). wherein a value for each input
parameter has to be supplied. This is what I meant by "prompt" (& yes,
it allows cancelling as well). As far as a web application is
concerned, you supply the input parameter values the stored procedure
expects (the values can be Form field values, querystrings etc.).

>Once again, you
> seem to think that procedural sql code for sql server is going to work
> on Oracle, MySql, sybase, or ms-access.....nothing could be further
> from the truth.

> I think it goes without saying that code posted for FoxPro, or c++ is
> not going to work in ms-access. I just assumed this issue was
> quite obvious. You do realize that have to change the syntax of
> the code you have written.

Yup...it definitely goes without saying that a code snippet that works
in, say, SQL Server, the same code won't work in ForxPro or C++ etc. &
that's exactly the reason why I have put forth my question. Had the SQL
Server stored procedure worked in Access, then I would have
copied-pasted the stored procedure from SQL Server to Access & wouldn't
have started this thread. What I am looking out for is the syntax in
Access which is why I started this thread.

> You have to place procedure access code in a code module.
> The query builder does not let you write code in ms-access.

That's exactly what I wasn't aware of. I was under the impression that
the code has to be written in the query builder.

Thanks for your inputs.


Albert D. Kallal wrote:
> > The SQL Server stored procedure that I have shown in post #1 in this
> > thread - I want to convert that stored procedure into a MS-Access
> > Query.
>
> Your miss-using the term "query" and "stored" procedure here. you
> somehow seem to think that a sql query is the same as a procedure.
> That is GRAND CANYON of difference.
>
> You can't turn a stored procedure in sql server to sql in sql server
>
> (how can you do that???? - what you are asking makes no sense at
> all).
>
> >What you have suggested would probably work in a ASP or ASP.NET
> > script but can you create a sub, use a recordset etc. in Access to
> > create a Query?
>
> We not creating a query...we are creating a procedure that runs code...
> big big big difference here. but, the answer is yes..that is how
> it is normally done in ms-access.
>
> In oracle, the stored procedure language is not sql, but in pl-sql.
>
> In ms-access, procedure code is written in code modules.
>
> In sql server, it is called t-sql. that stored procedure you posted is
> MOST CERTAINLY NOT sql, but is a program written in a
> programming language. In your case, that language was t-sql.
>
> So, my point is that you have to use the programming language
> of the platform you are using to write procedures.
>
> When you write procedures and
> code in ms-access, you use VBA. So, that procedure code I posted
> was written in the language you use in ms-access.
>
> You have to convert that program code to something compatible
> in ms-access. And, you use the syntax, coding and programming
> languages of the platform you are using.
>
>
> >
> > I am not very much familiar with Access; hence I may be wrong but I
> > have never come across anything like what you have suggested in
> > MS-Access queries.
>
> You did not post a query...you posed a program. Once again, you
> seem to think that procedural sql code for sql server is going to work
> on Oracle, MySql, sybase, or ms-access.....nothing could be further
> from the truth.
>
> Once again:
> You did not post a sql select, or update query, you
> posted a procedural piece of code written in a programming
> langue for a particular platform. In your case, the langue
> used was t-sql....
>
> I think it goes without saying that code posted for FoxPro, or c++ is
> not going to work in ms-access. I just assumed this issue was
> quite obvious. You do realize that have to change the syntax of
> the code you have written.
>
> > In fact, I just copy-pasted your code in a MS-Access Query (in SQL
> > View) but when I tried to save it, Access generated the following
> > error:
>
> You have to place procedure access code in a code module.
> The query builder does not let you write code in ms-access. You can
> write sql update, or selects in the query builder, but you have to use
> the code editor and VBA code for procedural stuff (that is what it is
> for)....
>
>
> > Now when I execute the stored procedure in SQL Server, I am first
> > prompted to enter a value for the @ClientName variable
>
> where are prompted? What application? You mean your web server prompts?
> Your accounting package? Who is doing this prompting? What application
> are you talking about that is able to make these prompts? What happens
> if a web server call that stored procedure? do the prompts magically
> travel across the web?
>
> If you need some type of user interface to prompt for data, then you
> likely should build a form and some
> type of interface for that purpose. You can then have a button on the form
> and it can call that procedure, and pass the required values.
>
> I was not aware that the code you posed would actually create user
> input prompts. If a web server, or accounting package, or vb6
> program executes that stored procedure you posted, how does
> the sql serer know to make the accounting package prompt the user??
> (answer...it can't!!!).
>
> How is sql server express going to know "where" to send the prompts
> to? I am not aware that sql server express is
> capable of prompting the user, or even knowing "where" to prompt the
> user.
>
> You going to be using some type of "client" tool to connect to sql server
> in this case. Perhaps that client tool is able to supply the parameters, but
> I not aware of what type of interface that sql server express will supply.
> does it prompt with a nice form? Does it allow cancel?
>
> Anyway, the procedure I posted will work...you have to place it in a code
> module. And, it will NOT prompt you for the parameters, you have to
> *supply* the parameters when you call that code. So, you have to build a
> form, or some type of interface. I would assumed that you had to
> provide some type of interface prior to using ms-access also....
>
> So, to clarify this, code written in ms-access is written in the code edit.
> The query builder, and sql in ms-access is ONLY used for building sql
> statements (they can be select, delete, update, and even ddl
> statements...but, you can't run code in the ms-access query builder...you
> have to use VBA).
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> (E-Mail Removed)

Albert, how do I invoke the sub in the module & supply the input
parameters after creating it?

Actually the records that the module will return (which will depend on
the input parameters supplied) - I want a ASP.NET page to retrieve
those records & display them to the user. Can I supply the input
parameters from the ASP.NET page itself?

Thanks once again.

(E-Mail Removed) wrote:
> > where are prompted? What application? You mean your web server prompts?
> > Your accounting package? Who is doing this prompting? What application
> > are you talking about that is able to make these prompts? What happens
> > if a web server call that stored procedure? do the prompts magically
> > travel across the web?
>
> > How is sql server express going to know "where" to send the prompts
> > to? I am not aware that sql server express is
> > capable of prompting the user, or even knowing "where" to prompt the
> > user.
>
> > You going to be using some type of "client" tool to connect to sql server
> > in this case. Perhaps that client tool is able to supply the parameters, but
> > I not aware of what type of interface that sql server express will supply.
> > does it prompt with a nice form? Does it allow cancel?
>
> Open SQL Server Management Studio & navigate to that stored procedure.
> Right-click the stored procedure & then select 'Execute Stored
> Procedure'. A new window opens up which lists all the input parameters
> (@ClientName, @Scrip, @Type etc.). wherein a value for each input
> parameter has to be supplied. This is what I meant by "prompt" (& yes,
> it allows cancelling as well). As far as a web application is
> concerned, you supply the input parameter values the stored procedure
> expects (the values can be Form field values, querystrings etc.).
>
> >Once again, you
> > seem to think that procedural sql code for sql server is going to work
> > on Oracle, MySql, sybase, or ms-access.....nothing could be further
> > from the truth.
>
> > I think it goes without saying that code posted for FoxPro, or c++ is
> > not going to work in ms-access. I just assumed this issue was
> > quite obvious. You do realize that have to change the syntax of
> > the code you have written.
>
> Yup...it definitely goes without saying that a code snippet that works
> in, say, SQL Server, the same code won't work in ForxPro or C++ etc. &
> that's exactly the reason why I have put forth my question. Had the SQL
> Server stored procedure worked in Access, then I would have
> copied-pasted the stored procedure from SQL Server to Access & wouldn't
> have started this thread. What I am looking out for is the syntax in
> Access which is why I started this thread.
>
> > You have to place procedure access code in a code module.
> > The query builder does not let you write code in ms-access.
>
> That's exactly what I wasn't aware of. I was under the impression that
> the code has to be written in the query builder.
>
> Thanks for your inputs.
>
>
> Albert D. Kallal wrote:
> > > The SQL Server stored procedure that I have shown in post #1 in this
> > > thread - I want to convert that stored procedure into a MS-Access
> > > Query.
> >
> > Your miss-using the term "query" and "stored" procedure here. you
> > somehow seem to think that a sql query is the same as a procedure.
> > That is GRAND CANYON of difference.
> >
> > You can't turn a stored procedure in sql server to sql in sql server
> >
> > (how can you do that???? - what you are asking makes no sense at
> > all).
> >
> > >What you have suggested would probably work in a ASP or ASP.NET
> > > script but can you create a sub, use a recordset etc. in Access to
> > > create a Query?
> >
> > We not creating a query...we are creating a procedure that runs code...
> > big big big difference here. but, the answer is yes..that is how
> > it is normally done in ms-access.
> >
> > In oracle, the stored procedure language is not sql, but in pl-sql.
> >
> > In ms-access, procedure code is written in code modules.
> >
> > In sql server, it is called t-sql. that stored procedure you posted is
> > MOST CERTAINLY NOT sql, but is a program written in a
> > programming language. In your case, that language was t-sql.
> >
> > So, my point is that you have to use the programming language
> > of the platform you are using to write procedures.
> >
> > When you write procedures and
> > code in ms-access, you use VBA. So, that procedure code I posted
> > was written in the language you use in ms-access.
> >
> > You have to convert that program code to something compatible
> > in ms-access. And, you use the syntax, coding and programming
> > languages of the platform you are using.
> >
> >
> > >
> > > I am not very much familiar with Access; hence I may be wrong but I
> > > have never come across anything like what you have suggested in
> > > MS-Access queries.
> >
> > You did not post a query...you posed a program. Once again, you
> > seem to think that procedural sql code for sql server is going to work
> > on Oracle, MySql, sybase, or ms-access.....nothing could be further
> > from the truth.
> >
> > Once again:
> > You did not post a sql select, or update query, you
> > posted a procedural piece of code written in a programming
> > langue for a particular platform. In your case, the langue
> > used was t-sql....
> >
> > I think it goes without saying that code posted for FoxPro, or c++ is
> > not going to work in ms-access. I just assumed this issue was
> > quite obvious. You do realize that have to change the syntax of
> > the code you have written.
> >
> > > In fact, I just copy-pasted your code in a MS-Access Query (in SQL
> > > View) but when I tried to save it, Access generated the following
> > > error:
> >
> > You have to place procedure access code in a code module.
> > The query builder does not let you write code in ms-access. You can
> > write sql update, or selects in the query builder, but you have to use
> > the code editor and VBA code for procedural stuff (that is what it is
> > for)....
> >
> >
> > > Now when I execute the stored procedure in SQL Server, I am first
> > > prompted to enter a value for the @ClientName variable
> >
> > where are prompted? What application? You mean your web server prompts?
> > Your accounting package? Who is doing this prompting? What application
> > are you talking about that is able to make these prompts? What happens
> > if a web server call that stored procedure? do the prompts magically
> > travel across the web?
> >
> > If you need some type of user interface to prompt for data, then you
> > likely should build a form and some
> > type of interface for that purpose. You can then have a button on the form
> > and it can call that procedure, and pass the required values.
> >
> > I was not aware that the code you posed would actually create user
> > input prompts. If a web server, or accounting package, or vb6
> > program executes that stored procedure you posted, how does
> > the sql serer know to make the accounting package prompt the user??
> > (answer...it can't!!!).
> >
> > How is sql server express going to know "where" to send the prompts
> > to? I am not aware that sql server express is
> > capable of prompting the user, or even knowing "where" to prompt the
> > user.
> >
> > You going to be using some type of "client" tool to connect to sql server
> > in this case. Perhaps that client tool is able to supply the parameters, but
> > I not aware of what type of interface that sql server express will supply.
> > does it prompt with a nice form? Does it allow cancel?
> >
> > Anyway, the procedure I posted will work...you have to place it in a code
> > module. And, it will NOT prompt you for the parameters, you have to
> > *supply* the parameters when you call that code. So, you have to build a
> > form, or some type of interface. I would assumed that you had to
> > provide some type of interface prior to using ms-access also....
> >
> > So, to clarify this, code written in ms-access is written in the code edit.
> > The query builder, and sql in ms-access is ONLY used for building sql
> > statements (they can be select, delete, update, and even ddl
> > statements...but, you can't run code in the ms-access query builder...you
> > have to use VBA).
> >
> > --
> > Albert D. Kallal (Access MVP)
> > Edmonton, Alberta Canada
> > (E-Mail Removed)

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...

> Albert, how do I invoke the sub in the module & supply the input
> parameters after creating it?

In any other code module, or any form, you simply call that procedure.

The syntax would be:

Call Fglobal(....list of values suppled here)

> Actually the records that the module will return (which will depend on
> the input parameters supplied) - I want a ASP.NET page to retrieve
> those records & display them to the user. Can I supply the input
> parameters from the ASP.NET page itself?

You can't use ms-access with asp.net at all.

so, the posted code will not work. That code can only be used from
ms-access, and you will have to have ms-access running...

You can use object automaton....launch ms-access, and then call the code
from asp.net, but I don't think there is any developer that recommend such a
task.

I would NOT recommend that you use ms-access on a web server. Why not use
the free edition of sql server express.

Since your running asp.net, then that suggests you are already running a
server and really don't need, nor want to use ms-access anyway. since you
can't use the forms, and code of ms-access with asp.net, then I think
installing access 2003, and having the asp.net pages have to try and launch
ms-access is not a very good setup. Further, ms-access should not be used as
a automation object on a server, no more then word, or excel should be

Remember, ms-access, excel, word, PowerPoint etc are all simply applications
of the Microsoft office suite. You are perhaps confusing ms-access which is
a desktop application with a user interface with that of a database server,
or database engine. They are remarkably different animals.

If you actually not planning to use ms-access as the form builder, report
writer, and code building...then really, you not needed, nor using ms-access
here. If you are going so far as to be able to setup a whole machine with
web services to work with asp.net, then I see little, if any reason to try
and throw the needs for ms-access to be installed into this mess....

If you are using asp.net, then just write the procedure in asp.net...and
call it from there. You don't need, nor want ms-access in this case at all.

You using the WRONG tool........

Note that you can consider using the JET database engine on your computer
WITHOUT ms-access. (this is the database engine that ms-access uses, and
often VB6 developers also used JET). This simply means that your procedure
code would be placed in the asp.net side of things, not in the JET database
engine.

So, you can't use JET to run procedural code, you have to use the client
side. In our case, that is ms-access, or whatever tool you are using. So,
you have to write the procedural code in the client application, be it
ms-access, or asp.net. So, use a ado.net recordset in your asp.net
code....and make a procedure out of that.

ms-access supports procedural code, and so does most of the office
suite...but, the JET database engine that stores the data does not support
procedural code...you have to write that client side.

It sounds like you not actually trying to covert this application code from
sql server to ms-access, but in fact are trying to move the data to a mdb
"jet" database file. In this case, you don't need to even install ms-access
on the target machine, since a copy of the JET database engine ships with
every copy of windows xp.

However, if you were using vb.net, then I would certainly continue to
consider using JET. However, since you are using asp.net, then you are
talking about a web based system, and I see little, if ANY advantage to
dropping sql express. I don't think it is such a great idea to try and use
the JET database engine for a asp.net web site. You have to convert all of
the procedure code in sql to asp.net code since it can't run in JET.

You *could* as mentioned have your asp.net code call the ms-access
code..but, it going to be slow...and take too many resources..


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)

OK..Albert...after creating the module which I named 'FG' (without the
quotes), I created a macro named 'AutoExec' (again, without the
quotes). In the Design View of the macro, I selected 'RunCode' from the
dropdownlist under the 'Action' column & then entered the following in
'Function Name' (using the 'Expression Builder'):

FGlobal («ClientName», «Scrip», «Quanity», «Price», «TTime»,
«ContNoteNo», «AddQty»)

But when I try to run the macro, Access generates the following error:

Microsoft Access can't find the name '«ClientName»' you entered in
the expression.

Could you please point out where I might be going wrong?


(E-Mail Removed) wrote:
> Albert, how do I invoke the sub in the module & supply the input
> parameters after creating it?
>
> Actually the records that the module will return (which will depend on
> the input parameters supplied) - I want a ASP.NET page to retrieve
> those records & display them to the user. Can I supply the input
> parameters from the ASP.NET page itself?
>
> Thanks once again.
>
> (E-Mail Removed) wrote:
> > > where are prompted? What application? You mean your web server prompts?
> > > Your accounting package? Who is doing this prompting? What application
> > > are you talking about that is able to make these prompts? What happens
> > > if a web server call that stored procedure? do the prompts magically
> > > travel across the web?
> >
> > > How is sql server express going to know "where" to send the prompts
> > > to? I am not aware that sql server express is
> > > capable of prompting the user, or even knowing "where" to prompt the
> > > user.
> >
> > > You going to be using some type of "client" tool to connect to sql server
> > > in this case. Perhaps that client tool is able to supply the parameters, but
> > > I not aware of what type of interface that sql server express will supply.
> > > does it prompt with a nice form? Does it allow cancel?
> >
> > Open SQL Server Management Studio & navigate to that stored procedure.
> > Right-click the stored procedure & then select 'Execute Stored
> > Procedure'. A new window opens up which lists all the input parameters
> > (@ClientName, @Scrip, @Type etc.). wherein a value for each input
> > parameter has to be supplied. This is what I meant by "prompt" (& yes,
> > it allows cancelling as well). As far as a web application is
> > concerned, you supply the input parameter values the stored procedure
> > expects (the values can be Form field values, querystrings etc.).
> >
> > >Once again, you
> > > seem to think that procedural sql code for sql server is going to work
> > > on Oracle, MySql, sybase, or ms-access.....nothing could be further
> > > from the truth.
> >
> > > I think it goes without saying that code posted for FoxPro, or c++ is
> > > not going to work in ms-access. I just assumed this issue was
> > > quite obvious. You do realize that have to change the syntax of
> > > the code you have written.
> >
> > Yup...it definitely goes without saying that a code snippet that works
> > in, say, SQL Server, the same code won't work in ForxPro or C++ etc. &
> > that's exactly the reason why I have put forth my question. Had the SQL
> > Server stored procedure worked in Access, then I would have
> > copied-pasted the stored procedure from SQL Server to Access & wouldn't
> > have started this thread. What I am looking out for is the syntax in
> > Access which is why I started this thread.
> >
> > > You have to place procedure access code in a code module.
> > > The query builder does not let you write code in ms-access.
> >
> > That's exactly what I wasn't aware of. I was under the impression that
> > the code has to be written in the query builder.
> >
> > Thanks for your inputs.
> >
> >
> > Albert D. Kallal wrote:
> > > > The SQL Server stored procedure that I have shown in post #1 in this
> > > > thread - I want to convert that stored procedure into a MS-Access
> > > > Query.
> > >
> > > Your miss-using the term "query" and "stored" procedure here. you
> > > somehow seem to think that a sql query is the same as a procedure.
> > > That is GRAND CANYON of difference.
> > >
> > > You can't turn a stored procedure in sql server to sql in sql server
> > >
> > > (how can you do that???? - what you are asking makes no sense at
> > > all).
> > >
> > > >What you have suggested would probably work in a ASP or ASP.NET
> > > > script but can you create a sub, use a recordset etc. in Access to
> > > > create a Query?
> > >
> > > We not creating a query...we are creating a procedure that runs code....
> > > big big big difference here. but, the answer is yes..that is how
> > > it is normally done in ms-access.
> > >
> > > In oracle, the stored procedure language is not sql, but in pl-sql.
> > >
> > > In ms-access, procedure code is written in code modules.
> > >
> > > In sql server, it is called t-sql. that stored procedure you posted is
> > > MOST CERTAINLY NOT sql, but is a program written in a
> > > programming language. In your case, that language was t-sql.
> > >
> > > So, my point is that you have to use the programming language
> > > of the platform you are using to write procedures.
> > >
> > > When you write procedures and
> > > code in ms-access, you use VBA. So, that procedure code I posted
> > > was written in the language you use in ms-access.
> > >
> > > You have to convert that program code to something compatible
> > > in ms-access. And, you use the syntax, coding and programming
> > > languages of the platform you are using.
> > >
> > >
> > > >
> > > > I am not very much familiar with Access; hence I may be wrong but I
> > > > have never come across anything like what you have suggested in
> > > > MS-Access queries.
> > >
> > > You did not post a query...you posed a program. Once again, you
> > > seem to think that procedural sql code for sql server is going to work
> > > on Oracle, MySql, sybase, or ms-access.....nothing could be further
> > > from the truth.
> > >
> > > Once again:
> > > You did not post a sql select, or update query, you
> > > posted a procedural piece of code written in a programming
> > > langue for a particular platform. In your case, the langue
> > > used was t-sql....
> > >
> > > I think it goes without saying that code posted for FoxPro, or c++ is
> > > not going to work in ms-access. I just assumed this issue was
> > > quite obvious. You do realize that have to change the syntax of
> > > the code you have written.
> > >
> > > > In fact, I just copy-pasted your code in a MS-Access Query (in SQL
> > > > View) but when I tried to save it, Access generated the following
> > > > error:
> > >
> > > You have to place procedure access code in a code module.
> > > The query builder does not let you write code in ms-access. You can
> > > write sql update, or selects in the query builder, but you have to use
> > > the code editor and VBA code for procedural stuff (that is what it is
> > > for)....
> > >
> > >
> > > > Now when I execute the stored procedure in SQL Server, I am first
> > > > prompted to enter a value for the @ClientName variable
> > >
> > > where are prompted? What application? You mean your web server prompts?
> > > Your accounting package? Who is doing this prompting? What application
> > > are you talking about that is able to make these prompts? What happens
> > > if a web server call that stored procedure? do the prompts magically
> > > travel across the web?
> > >
> > > If you need some type of user interface to prompt for data, then you
> > > likely should build a form and some
> > > type of interface for that purpose. You can then have a button on theform
> > > and it can call that procedure, and pass the required values.
> > >
> > > I was not aware that the code you posed would actually create user
> > > input prompts. If a web server, or accounting package, or vb6
> > > program executes that stored procedure you posted, how does
> > > the sql serer know to make the accounting package prompt the user??
> > > (answer...it can't!!!).
> > >
> > > How is sql server express going to know "where" to send the prompts
> > > to? I am not aware that sql server express is
> > > capable of prompting the user, or even knowing "where" to prompt the
> > > user.
> > >
> > > You going to be using some type of "client" tool to connect to sql server
> > > in this case. Perhaps that client tool is able to supply the parameters, but
> > > I not aware of what type of interface that sql server express will supply.
> > > does it prompt with a nice form? Does it allow cancel?
> > >
> > > Anyway, the procedure I posted will work...you have to place it in a code
> > > module. And, it will NOT prompt you for the parameters, you have to
> > > *supply* the parameters when you call that code. So, you have to build a
> > > form, or some type of interface. I would assumed that you had to
> > > provide some type of interface prior to using ms-access also....
> > >
> > > So, to clarify this, code written in ms-access is written in the codeedit.
> > > The query builder, and sql in ms-access is ONLY used for building sql
> > > statements (they can be select, delete, update, and even ddl
> > > statements...but, you can't run code in the ms-access query builder....you
> > > have to use VBA).
> > >
> > > --
> > > Albert D. Kallal (Access MVP)
> > > Edmonton, Alberta Canada
> > > (E-Mail Removed)

On Jan 21, 11:50 pm, "Albert D. Kallal"
<PleaseNOOOsPAMmkal...@msn.com> wrote:
> When you write procedures and
> code in ms-access, you use VBA.

Well, that's one interpretation but it's not the whole story, otherwise
how would you explain the following?

http://office.microsoft.com/en-gb/ac...322191033.aspx

"CREATE PROCEDURE Statement

"Applies to: Microsoft Office Access 2003

"Creates a stored procedure ."

I think what is missing from your descriptions of SQL procedure
programming languages is the fact that, while it is possible to create
a Jet SQL PROCEDURE with arguments, such a proc can only contain one
SQL statement, meaning there is no place in Jet SQL for control-of-flow
statements (scalar expressions such as IIF() and SWITCH() aside).

> Your miss-using the term "query" and "stored" procedure here. you
> somehow seem to think that a sql query is the same as a procedure.
> That is GRAND CANYON of difference.

I think this is overly harsh on the OP ("Pot calling kettle...come in
kettle..." <g>). If you execute a CREATE PROCEDURE statement in Jet SQL
it creates a Query object in Access. Further consider that the term
'query' is used more loosely in the ms-access community than in the
wider SQL community, where usage such as 'UPDATE query' (which you used
above) is oxymoronic (hint: UPDATE SQL DML is a *statement*).

Jamie.

--