Convert SQL Server Procedure To Access Query

[rn5a]

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....

 

[Albert D. Kallal]

The code in access would look like:

 

[Albert D. Kallal]

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...

 

[rn5a]

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]

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).

 

[rn5a]

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.

 

[rn5a]

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.

 

[Albert D. Kallal]

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..

 

[rn5a]

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?

 

[Norman Yuan]

You probably right on that "CREATE PROCEDURE" statement can be used for Jet
database and some sort of Stored Procedure is supported in Jet DB. However,
Albert's explanation, IMO, is very good, clear, and not harsh. If you read
the OP's posts, you could have known he what to use an Access query in a ASP
or ASP.NET, as with SQL SP. He did not realize the difference of Access and
Jet database. If he want to use jet db in ASP/ASP.NET (web app) it
impossible with jet db to do a query equivalent to that SQL SP, as Albert
clearly pointed out. I wouldn't imagine any better answer as good as
Albert's, in this case.

 

[Albert D. Kallal]

A Jet SQL PROCEDURE is pretty much ideal, then <g>.

Well, it still a single select statement....and thus is not really a
procedure....despite it being called that!!!
(it is select statement with parameters)

If you need procedure code in ms-access...you going to use VBA, or "gasp"
macros!

the problem here is that it was/is only considerably later in the thread
that we find out that OP was not actually using ms-access, but is using a
web server, and the .net tools....a rather large omission....don't you
think?

This is not a big deal one way or the other. It does seems that the OP is
not really using access at all..but, is considering using JET for a .net web
based system. While this is possible, I don't think it is such a great
idea. The time spent to "convent" those sql stored procedures to ms-access
is not really worth it when you have free editions of sql server express
that allows those stored procedures to run, and further...this is a web
based system anyway...the user is not working with ms-access....but only
looking for a data store....

 

[Albert D. Kallal]

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

You can read my other posts here....

However, you an run this code interactive....

You don't really want to bother with a macro as you tried....

That code procedure would be placed in that module (and, the name of the
code procedure must be different then the module name).

if you are in the code editor (or hit ctrl-g), you get the immediate window
(the command prompt window). From there, you can use the command line to
execute querys...or call code.....

You can simply type in

Call Fglobal("parm1","parm2"....etc)

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

The real answer is at what point, and "where" in the application do you want
to execute the above code. Normally, this would occur in the application
side....so, you likely would build a form..place controls on this form...and
then behind a code button go:


Call Fglobal(me.clientName,me.Scrip.....etc)

In the above "clientName" would be the name you use for the text box on
the form. So, the whole procedure (sub) can be called and you only have to
write the above one line of code...and it would be pulling the values for
each parameter from the form you built. This is likely less code then just
about any platform to interface that code routine to run from a form with
user supplied information typed into that form.

 

[Albert D. Kallal]

I don't follow your logic. Why do you think a PROCEDURE (SQL keywords

in uppercase, please) that comprises a single statement is not really a
procedure?

I agree, this is a bit of a semantics, or issue of "English".

However, I don't consider JET sql a procedural system, and by "most"
defines, it is not.

Both JET, and sql server do allow the use of the "procedure" keyword, but in
the case of jet, you actually NEVER run procedural code.

While JET supports the PROCEDURE keyword, JET actually does not support sql
procedural code, and I think that is a big issue, and the reason for me
making the distinction.

I mean, really...so what that JET has a PROCEDURE keyword. All that results
in is a query with parameters for the select statement.

It is RATHER misleading to state that JET supports procedures in sql. It
does not, it supports select query's with parameters. The only issue that
confusedness this is that both sol server and JET allow procedure declares
that is a select statement with parameters. The INSTANT you write any code
in sql, then JET will NOT be useable. It is not just a issue cursors as you
pointed out.

Where I come from, procedural code allows branching, if..then ..else...and
variable declares. JET sql has NONE of these features.

I mean, because we call a Candy dish a soup bowel, that does NOT change what
it is. A Candy dish is that 4.5 inch piece of glass modeled with rims, and
CAN be used for holding soup. The name we give the object DOES NOT CHANGE
ITS NATURE!!! It is still the candy dish, even if we choose to use a
different name. the name does not change what that "thing" is!!

Can you provide
a citation to support your idea that a single step 'process' is not
technically a procedure?

Well, procedural code systems include the following:

if...then else
branching code
looping code
ability to declare variables..etc.

sql server procedures have all of the above
jet procedure has NONE.

JET simply allows the execution of a sql statement.

So, sure...if we look up the definition of procedure...JET procedure would
not make the cut...

http://en.wikipedia.org/wiki/Procedure

A procedure produce is not a "action"..but a series of actions..it is
"many", or plural. A procedure system can have ONLY one step in it, but that
does not make a system that allows ONLY ONE step a procedural system. All
this means is that a procedure system with one step can look like a single
JET sql statement, but the reverse is not the case..and thus the definition
also does not apply to the reverse.

So, procedural sql systems can have one step, but a system that allows one
sql step still DOES NOT make it a procedural system at all (dispute even
giving it that name). We can call a house a car..but, you can't drive that
house. It is not the name that changes what something is!!!

It can be but is not limited to that definition. For Jet, a PROCEDURE
can be any SQL statement, DML, DDL or DCL, and the use of parameters is
not a prerequisite.

Ok, sure...I was not 100% sure that JET allowed anything else then select,
or updates for the procedure declare. So, ok, sure, the above seems ok, but
we are still limited to ONE statement, and the term "procedure", or
"procedural" still does not apply by any reasonable definition here.

OK so your grammar is not 100% - not that I consider that to be an
issue - so did you mean to say "procedural code"? If that is the case
then I agree: Jet SQL does not support cursors so procedural code must
be performed elsewhere

No, it not even a issue of cursors. Why is that a issue? You can write
code in t-sql that don't even use a table, and loops. The issue is do
we consider a system hat allows ONE statement to execute a
procedural system? Answer: no, we do not. It is not a issue
of cursors at all. And, most definitions you look up for what
defines a procedure would not fall under one sql statement that
jet allows.

So, a system without branching, or the ability to execute more
then ONE statement is not a procedural in my books...even if you give
it that name. I don't think many would see this issue much different.

Surely you realise that most people, Access MVPs included, not only use
the terms 'Access' and 'Jet' interchangeably but aren't always aware of
the boundaries between the two? For example, consider this thread from
the Usenet archive:

Yes, you are absolute correct. For 99% of the cases, a person
coming into this newsgroup, and says I am converting something from
sql server to ms-access mean I *USUALLY* can assume that the person will
have ms-access as a tool to use here.

Obviously in this case, I was
100% dead wrong. My assumption that this person had ms-access
as a solution was simply wrong. Not really a big deal.

Perhaps I should have asked if
this was the case, but I did not. Really, just not a big deal either way,
since 99% of the time, I *can* assume that the person has
ms-access available as a solution.

If the OP had simply said, I am building asp web site, and
moving data from sql server to ms-access, then I would have
keyed in instantly on this issue..

I quote the above thread because, Albert, you seem to be saying the
opposite i.e. that a database is considered to be 'Jet' and not
'Access' unless they have used something - what? - from the Access user
interface.

Yes, I most certainly am. If you come here ask where can I download JET
or where can I download ms-access, you going to get two VERY different
answers.

So, sure, I actually do consider the data store to be a JET database, and I
consider ms-access the developers tool. So, yes...I do think that way!.
And, a very large portion of the community also thinks this way.

WHEN WE DO NEED to make the distinguish, among developers, there
is LITTLE diversity on this issue.

I man, what would you suggest as a term to used to make the distinguish?
Often WE DO need to make a distinguish between JET and ms-access.

So, most (many) people here use the term JET, or ms-access,
or database interchangeably. This is generally not a problem for 99%
of the questions here. So, it is not being so right, or so wrong
here, it is just simple question of what terms people use to define
what. Most of the time, it don't matter. Unfortunate, in this
case it did!!!

I consider ms-access the development part, and the JET the
database part. In fact, the whole confusing for this user on this
issue is a result of this (or, shall I say lack of).

Remember, you can actually use ms-access, and NOT USE
jet! If you use a adp project, you not using jet, and are using a 100%
native oleDB connection to sql server...there is not even local tables, nor
are they allowed.

So what I am saying is, without a strict definition from the vendor or
even a consistency in usage among Access MVPs (perhaps also in absence
of Jet newsgroups and corresponding Jet MVPs), I simply see no value in
differentiating between Jet and Access in the way you seem to advocate.

We do for the most part make that difference known when it is needed.
Remember, every copy of windows xp ships with the JET database installed,
However, windows does not ship with ms-access.

What this means is that any windows box can open and read a mdb file
without having to install any software!!! I think that is quite a
significant
issue!! ms-access is NOT jet., no more so then is VB6, or a c++ application
that happens to use JET as its data store. (Simply accounting uses JET, and
that means you can open simply accounting files with ms-access, but ONLY
because simply accounting uses JET...it was not written in ms-access - so,
you can open Simply accounting tables in ms-access, but you certainly can't
modify the forms and code using in simply accounting with ms-access).

We do need to difference. This thread and post is a *perfect* example
of this. And, most technical documents and writing DO MAKE the
distinction WHEN IT matters.

So, actually,if you look at posts here, for the most part, people do use the
term JET and ms-access interchanble.

As mentioned, MANY
time, the distinction really don't matter. However, in many cases it
DOES matter...

IF the person had said I am using a JET database, I can 100% bet you
that my first response would have been to explain to the user that
JET supports the procedure keyword, but does NOT support
procedural code as posted.

So, sure...we tend to overlook, or often not make much of a
point to difference on JET vs ms-access. Often, we don't, and often
things are a bit loose in this regards, but for the most part, it is
reasonable distinction to make, especially WHEN it makes a difference.

All of the access developer team will use the term JET, or now
ACE (that is the name of the new JET for a2007) when speaking
of JET features. In fact, the database engine has a new name
for a2007. We did not call is ms-access 2007, but the data
engine is now called ACE. You can WELL bet that any
poster using the term ACE in place of JET, or in place
of ms-access will MOST CERTAINLY be more clear
as to what they are speaking about.

I mean, JET is shipped, and pre-installed on very copies of windows.

I can write windows scripts to open, and read data on any windows xp
box, and I DO NOT need to have ms-access installed to read that mdb
file. Further, virally any user of a web site who places a mdb file on that
web site..and connects to it (via ADO+JET) is using that mdb file, and is
NOT running a copy, nor has NOT installed ms-access on that web server.
(most web providers will advertise that they support ms-access, but in fact
they mean JET...since ms-access is never installed on that web server).

So, parts of the industry, often don't make the distinction, but among the
developer crowd, the distinction between JET and ms-access is rather a
solid and traditional used term to difference the two products...

 

[David W. Fenton]

The issue is do
we consider a system hat allows ONE statement to execute a
procedural system? Answer: no, we do not. It is not a issue
of cursors at all. And, most definitions you look up for what
defines a procedure would not fall under one sql statement that
jet allows.

So, a system without branching, or the ability to execute more
then ONE statement is not a procedural in my books...even if you
give it that name. I don't think many would see this issue much
different.

If all a Jet "procedure" can do is execute a SQL statement, there is
nothing procedural about it -- it is nothing more than a standard
SQL set operation. That's a key issue that many people miss when
coming from procedural programming environments and database
environments that don't support SQL -- SQL is set-based, and
operates on a set of records all at once. Procedural code is, well,
procedural, and operates on one record at a time, and walks through
them.

Now, of course, a stored procedure on a server database that
supports real stored procedures can include the
least-common-denominator of a single SQL statement, a mere set
operation (why one would make that a stored procedure, I can't say,
though). It can also include code that branches and behaves
differently with different inputs. SQL statements can't really do
that -- you can change the sets operated on, and with limited
capability you can conditionally return or update different values
on a row-by-row basis, but you can't branch based on information
outside the data in the records themselves.

My bet is that Jet SQL includes a PROCEDURE keyword simply for ODBC
compatibility and nothing else. The Jet procedure is nothing more
than that least-common-denominator procedure, a single SQL
statement.

 

[David W. Fenton]

[the terms "Jet" vs. "Access":]

Most of the time, it don't matter. Unfortunate, in this
case it did!!!

I would say it ALWAYS matters, but I"m something of a curmudgeon on
this issue. Microsoft obfuscates the distinction by continually
using the two interchangeably. And then you run into cases where it
does matter, as in Jet Replication, where you can safely replicate
Jet objects (tables and queries) but you can *not* safely replicate
Access objects (forms/reports/modules/etc.). Microsoft won't quite
admit to this fact, but it *is* a fact, and it's due to the fact
that Replication is a *Jet* technology and just doesn't know a
damned thing about Access's objects and their properties. The
monolithic save model made this even worse, since previous to it,
each object was a record in a table and conflicts could be resolved
record-by-record (i.e., object-by-object), but with the monolithic
save model, it's a BLOB in a single record, and any change to any
object is a change to that record, and can create conflicts on
something as simple as saved filters in two different replicas.

Microsoft should not have *expected* Jet replication to work well in
this scenario, if they'd taken the time to consider what was going
on.

But they, like everyone else, seem not to consider the Jet/Access
distinction important enough to place it first and foremost in all
their documentation and feature discussions. That mistake on MS's
part is exactly why I consider it so crucial to *always* maintain
the distinction:

When you're talking about an Access project, say Access.

When you're talking about a data store created with Access, say Jet.

Given the fact of everyone needing a split architecture, this
distinction should not be difficult. You'll have one Access database
(the front end) and one Jet database (the back end) in every app.

And I believe that's the way they should be referred to in all
cases.

 

[David W. Fenton]

Remember, you can actually use ms-access, and NOT USE
jet! If you use a adp project, you not using jet, and are using a
100% native oleDB connection to sql server...there is not even
local tables, nor are they allowed.

Jet is loaded anyway, because it handles certain operations internal
to Access.

The whole idea of a Jet-free Access was completely silly to begin
with, and MS is now realizing this -- that's why ADPs are on the way
out.