locked
"Stored Procedure" or "Query"? RRS feed

  • Question

  • Hello, me again!

    I found myself a bit confused about "query"  and "stored procedure" in access. Are they the same thing?

    However, I do suspect this is ture as after some googling I found that varies articals mentioned "stored procedures" are actually created in MS Access for Sql server. So what you are doing when calling a "stored procedure in Access" is actually invoking the stored procedure stored in the underlying Sql server. Access is more like an interface designing tool in this case. Am I getting this idea right or not?   

    If I do get the point right (which means there's no way to create "stored procedures" for an Access database), is there any way to call a "query" in an Access database programmtically (from a C# class, I want to simplify some common database opreations, i.e. insert, update, delet and select)? In another words, can a "query" in an Access database serve the same purpose (in a C# class) as a "stored procedure" does for the database in Sql server?

    I don't know if I have mentioned my confusion clearly, but I'll try to explain it in more detail if you don't get what I am trying to say here.

    Thanks in advance!
    Wednesday, May 25, 2005 2:40 PM

Answers

  • The Jet engine supports pre-defined queries (called QueryDefs in the old DAO object model) that can take parameters.  The stored queries can only contain simple SELECT queries, unlike the robust stored procedure languages supported by larger databases like SQL Server and Oracle.

    If I remember correctly, in schema queries the Jet OLE DB reports stored queries that contain parameters as stored procedures and stored queries without parameters as views.

    I hope this information proves helpful.

    David Sceppa
    Microsoft

    Wednesday, May 25, 2005 4:17 PM

All replies

  • I forgot to mention an artical I went through, it's called "Working with MS Access Stored Procedures in VB.Net".

    The author used some vb .net code to create "stored procedures" for Access. He accomplished this by executing "Create Proc" sql statement againest the underlying Access database so that other applications can call the stored procedures programmtically.

    He mentioned that "When a stored procedure is added to an Access Database, the Jet Engine reworks the stored procedure syntax into a query object". Is this ture? And is this the way of creating and calling "store procedures" of an Access database? Is there any visual tool available to create and later modify the stored procedures for Access as the author also gave the following statement, "Unlike other objects in Access, stored procedures have no interface and cannot be created or run through the Access User Interface"? It seems to me once you have created some stored procedures in this way, it might be hard to track, manage and modify for what you have created. 
    Wednesday, May 25, 2005 2:53 PM
  • The Jet engine supports pre-defined queries (called QueryDefs in the old DAO object model) that can take parameters.  The stored queries can only contain simple SELECT queries, unlike the robust stored procedure languages supported by larger databases like SQL Server and Oracle.

    If I remember correctly, in schema queries the Jet OLE DB reports stored queries that contain parameters as stored procedures and stored queries without parameters as views.

    I hope this information proves helpful.

    David Sceppa
    Microsoft

    Wednesday, May 25, 2005 4:17 PM