Create A Stored Procedure In Access

  • Thread starter Thread starter Seth
  • Start date Start date
S

Seth

I am trying to model an access DB after a SQL Server DB that I currently
have and I need to be able to create procedures in Access compatible with
the ones I have in my SQL Server DB. I have seen an article that describes
how to do it using a VB program and I have tried that, but it is not giving
me the desired results.

Does anyone have some examples of how I can do this?

TIA

Seth
 
Dear Seth:

The easiest way to do this is simply not to do it at all.

Just leave the data in a SQL Server or MSDE database. Use the
existing stored procedures to do the job by either creating an Access
ADP or by using the pass-through query methodology. This may depend,
in part, on whether your SP requires parameters.

If you really want to do it the hard way, we can probably still help
here. Post the text of the SP. You may also need to explain the
relevant columns in your tables and the relationships between the
tables.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
The reason I must convert my DB to Access is simply for cost benefit needs.
We have an Enterprise edition of our software and a standalone. To reduce
the cost to us for a standalone installation, we will use Access. I cannot
use SQL Server in any way that will add additional costs...
 
Dear Seth:

If you own a copy of Access for at least one computer, you can install
MSDE from Access on that computer. There is no additional cost
associated with this. This will run anything you have in SQL Server,
incuding the SP involved, up to the limits of MSDE. One of these
limits is 2GB of database, which is the same limit as an Access Jet
database. So, assuming you have Access 2000, XP, or 2003 you are set
to use this at no additional cost (other than setting it up).

The information that Access works with SQL Server and includes the
MSDE version of SQL Server seems to not be getting out to people like
you, but that's certainly something you need to know! Especially if
you already know how to work with SQL Server, this is likely the best
route for you to take.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I am trying to model an access DB after a SQL Server DB that I currently
have and I need to be able to create procedures in Access compatible with
the ones I have in my SQL Server DB. I have seen an article that describes
how to do it using a VB program and I have tried that, but it is not giving
me the desired results.
Click to expand...

Access JET databases do *not support stored procedures*, or have only
just started to do so - and they're very limited.

Typically Access developers use VBA code in Form events to accomplish
the same ends as stored procedures in SQL. Obviously the mapping from
one to the other is not exact!
 
Tom:

Is there any limit to the number of concurrent users that can be connected?

I saw somewhere about the information for the MSDE, but I cannot install it
on a system that has SQL Server already installed it...right?

We have used the MSDE version of SQL Server 2000. This is not the same as
what you are talking about, is it?

Seth
 
Dear Seth:
Is there any limit to the number of concurrent users that can be connected?
Click to expand...

Not directly, but there is a concurrent thread "throttle" that limits
performance. However, it will generally out-perform an Access MDB as
to the number of user it can support. So, there's no "down side" to
making this switch in nearly any case you can think of. It is more
likely you would need to switch TO MSDE from Jet if you need more
performance or more users.

Your license for SQL Server may also permit you to install this. Or
perhaps you users could use an existing server for this, simply adding
another database to it.

I'm not certain about whether you can install MSDE on a server with an
existing install of Standard, Enterprise, or Developer Edition SQL
Server. By why would you want to? Your license for SQL Server would
allow you to add another database to the existing install, or if you
want more isolation, make a separate instance of SQL Server on the
same server. If that's the case, forget MSDE and its (probably
irrelevant) limitations.

The MSDE version of SQL Server, whether from a CD of SQL Server or the
Access/Office CD is the same thing, provided they are the same version
of MSDE.

Please do not take anything I said about licensing as "gospel". I've
stated things as I understand them, but you should consult your EULA
for details. I don't pay much attention to them for my purposes, as I
have MSDN Universal and can use just about anything I need through
that license within my own office. I rarely have to worry about
production database licensing, except that my clients can use MSDE
through my license as a developer. Both Office Developer Edition and
SQL Server Developer Edition convey that right - to distribute MSDE
with my own applications (and both are in the MSDN Universal - Pretty
good deal!)

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom:

We also have the MSDN Universal Subscription.

I thought that there was a limitation to the number of users that could
connect to the MSDE. Is this not true?

Seth
 
Dear Seth:

The limit is not technically "connections" but "threads" where a
thread is (roughly) a pending request. As I said before, MSDE will
generally outperform Access Jet under similar circumstances including
with respect to number of users. Client/Server is a more efficient
technology than file sharing, and especially can reduce network
loading.

If you have an existing installation of SQL Server available on the
LAN where the computers to run your Access application are situated,
then you could just add a database to that instance or install an
additional instance. Then you wouldn't even need to think about the
(probably irrelevant) limations of MSDE compared with Jet. In
addition, there would be much less to learn and certainly less
conversion to be done.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Seth said:
Tom:

We also have the MSDN Universal Subscription.

I thought that there was a limitation to the number of users that could
connect to the MSDE. Is this not true?

Seth
Click to expand...

There is not a limitation,but after 5 users, the engine is purposely slowed
down, or limited to I believe 5 executing threads. That usually gives enough
performance for 5 to 10 users before you have to run out and purchase the
full version of sql server.
 
Tom:

Thank you for the information. I will take what you have said into
consideration and figure out which
direction to go.

Thanks again.

Seth
 
The reason I must convert my DB to Access is simply for cost benefit needs.
We have an Enterprise edition of our software and a standalone. To reduce
the cost to us for a standalone installation, we will use Access. I cannot
use SQL Server in any way that will add additional costs...
Click to expand...

Even though (if you open the SQL folder on your Office/Access CD and
install) you have MSDE for no additional cost? It's complete
SQL/Server, with some limitations on concurrent uses.
 
This depends very much on what the database engine has to do.

For typical interactive forms I expect each set of requests (due to
the user opening or navigating a form) to take about .2 seconds. The
average user requires such action for the engine only about twice per
minute during the time the user is at the computer then it will be
rare for the engine to have even two pending requests simultaneously
until you got several dozen users.

If you have even 2 users simultaneously printing long, very complex
reports, then this may result in competition for the engine.

Under these same circumstances, using Jet instead of MSDE has (for me)
typically resulted in much more rapid deterioration in response times.

You do not want to run into this 5 user limit at all if you can help
it. We plan this by adding sufficient memory to the server and by
adding more processing power. When the interactive response time is
dropped from .2 seconds to .05 seconds, you can accomodate 4 times as
many users, usually more, and not face the penalty.

Because, it will penalize you. It doesn't just stop accepting new
requests. It deliberately wastes large amounts of CPU and bogs down
horribly.

But, as CPU power has gone up and memory prices down, avoiding this
with hardware has been a good response.

My experience has been that (for my applications) using a 2 processor
2.4 GHz Xeon server and a gigabyte of RAM, you can get far more than 5
to 10 users on MSDE. In fact, much more than 10 times that many
users. But it certainly DOES depend on the application, the activity
of each user, and what activities they choose to do.

Alberts experience may be quite different from mine in some of these
particulars. I am not saying he's wrong in any way, but that I have a
different experience altogether.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top