Stored Procedure in Access

[hoachen]

Hi, Is there a good tutorial on Stored Procedure in Access? I am trying to
learn this function. The reason is, i have a very big db and it take long
time to generate the result and i hope i can use the stored procedure to do
it. I only need to retrived the data (most is use Select clause only). Thanks

 

[Banana]

Hi, Is there a good tutorial on Stored Procedure in Access? I am trying to
learn this function. The reason is, i have a very big db and it take long
time to generate the result and i hope i can use the stored procedure to do
it. I only need to retrived the data (most is use Select clause only). Thanks

Strictly speaking, Access (or more specifically, Jet, the database
engine driving Access) doesn't support stored procedures.

However, this isn't a problem because this is usually fulfilled by VBA
coding behind the form and for that reason, users should be working only
with forms, not tables/queries which are used to form the basis of data
soruce for the forms.

Therefore, what you want is a good tutorial on VBA, then you'll be able
to do anything with the data that you could have done using stored
procedure.

Of course, if you're using Access as a front-end client to a database
server such as SQL Server, MySQL, Oracle, whatever, there is benefit to
be had by using stored procedures, but in pure Access solution is good
enough.

For VBA tutorial, I'd start by googling for 'Access Basic' from a Crystal.

 

[Arvin Meyer [MVP]]

Stored Procedures only work in a server based database engine. If you are
using the Jet engine (any version) you cannot use stored procs.

That said, it should only take a few seconds, at most, to return records of
pravtically any sized database that Access can handle. For instance I have a
database consisting of 6 million records, that takes less than 5 seconds to
find a single record, and less than 8 to find multiple records.

The "secret" is indexing. Make sure that your fields are properly indexed
and that your queries return only the records you need.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[George]

Is the Back End for your data a SQL Server database? If so, that is where
your stored procedures can be created. Access itself, on the other hand,
doesn't use them.

Therefore, if you are connecting to a SQL Server, and need to create SP's
there, I would suggest posting your question in a newsgroup devoted to
support of SQL Server and be sure to include which version of SQL Server you
are using.

If not, you need to stick with Access functionality. The best starting place
is probably going to be looking into the way your SQL Statements are
written. Also give some thought to the indexes on your tables.

HTH

George

 

[Oliver Straub]

Hi,

Banana wrote:
Strictly speaking, Access (or more specifically, Jet, the database engine
driving Access) doesn't support stored procedures.

this is not the whole true. Since Jet 4.0 the engine knows stored
procedures:

[donKarl]