Could it be possible to create stored procedure in Access 2003 ?

[Dirk Goldgar]

(blank message)

Better to elaborate on your question in the body of the message.

The Jet database format (used in a normal Access database) supports
stored procedures only in the sense of stored action queries, such as
you can create on the Queries tab of the database window. Each such
query can contain only one SQL statement. They can have parameters, and
can be invoked in code by DAO, or by the user interface. The can be
also be created via the SQL CREATE PROC statement, but I think that
statement must be executed via ADO, not DAO.

 

[chris.nebinger]

From the help:

CREATE PROCEDURE Statement, PROCEDURE Clause Example

This example names the query CategoryList.

This example calls the EnumFields procedure, which you can find in the
SELECT statement example.

Sub ProcedureX()
Dim dbs As Database, rst As Recordset
Dim qdf As QueryDef, strSql As String
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb")
strSql = "PROCEDURE CategoryList; " _
& "SELECT DISTINCTROW CategoryName, " _
& "CategoryID FROM Categories " _
& "ORDER BY CategoryName;"
' Create a named QueryDef based on the SQL
' statement.
Set qdf = dbs.CreateQueryDef("NewQry", strSql)
' Create a temporary snapshot-type Recordset.
Set rst = qdf.OpenRecordset(dbOpenSnapshot)
' Populate the Recordset.
rst.MoveLast
' Call EnumFields to print the contents of the
' Recordset. Pass the Recordset object and desired
' field width.
EnumFields rst, 15
' Delete the QueryDef because this is a
' demonstration.
dbs.QueryDefs.Delete "NewQry"
dbs.Close
End Sub

I fail to see how this works any better than without the PROCEDURE
statement.


Chris Nebinger

 

[Dirk Goldgar]

Thanks a lot!

Could you please give me a sample through ADO to create SP?

CurrentProject.Connection.Execute _
"CREATE PROCEDURE MyProcedure AS " & _
"PARAMETERS [ID to Delete?] INTEGER; " & _
"DELETE FROM Table1 WHERE ID = [ID to Delete?];"

 

[chris.nebinger]

I admit, I've been developing Access applications since Access 95, and
I never thought/knew about using procs in Access. I am testing using
the following:

CREATE PROCEDURE MyProcedure AS
PARAMETERS [USER] Text ( 255 );
DELETE [ID]
FROM users
WHERE USERNAME=[USER];


Then calling it using Exec:

Exec MyProcedure "UserName"

it seems much easier to use the SQL Server method of using stored
procedures, even if just one SQL statement is allowed, then using
parameter queries that are harder to pass parameters into.

Then, using ADO:

PARAMETERS [USER] Text ( 255 );
SELECT *
FROM USERS
WHERE USERNAME=[USER];


Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
Set rst = CurrentProject.Connection.Execute("Exec SelectUser
""AdminUser""")
For Each fld In rst.Fields
Debug.Print fld.Name, fld.Value
Next fld

This makes code much more concise.

Chris Nebinger

Thanks a lot!

Could you please give me a sample through ADO to create SP?

CurrentProject.Connection.Execute _
"CREATE PROCEDURE MyProcedure AS " & _
"PARAMETERS [ID to Delete?] INTEGER; " & _
"DELETE FROM Table1 WHERE ID = [ID to Delete?];"

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

it seems much easier to use the SQL Server method of using stored
procedures, even if just one SQL statement is allowed, then using
parameter queries that are harder to pass parameters into.

In Jet, AFAICT, they're just alternative ways of looking at and invoking
the same thing. But ...

Dim rst As New ADODB.Recordset
Set rst = CurrentProject.Connection.Execute("Exec SelectUser
""AdminUser""")

.... does seem cleaner and more self-documenting than

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("SelectUser")
qdf.Parameters(0).Value = "AdminUser"
Set rst = qdf.OpenRecordset

I never thought of invoking parameterized querydefs as stored procedures
before. There may be efficiency or functionality considerations
involved in using DAO vs. ADO, but they're likely to be insignificant.
Still, I've been using DAO for so long, I don't know if I'll change now.

 

[chris.nebinger]

I'm more into .NET & T-SQL now than Access, so it probably won't be
that big of deal for me. I'm excited to use the different methodology,
but I'm not sure when I'll get the next chance. There doesn't seem to
be as big of a Access consultant market, with the snobbish C#
developer's laughing at the "Access Programmer" moniker. They sell
internal IT execs on spending more money on overblown systems that
don't work as well as an Access app.

Sorry, that was my rant for the day.


Chris

 

[Dirk Goldgar]

I'm more into .NET & T-SQL now than Access, so it probably won't be
that big of deal for me. I'm excited to use the different
methodology, but I'm not sure when I'll get the next chance. There
doesn't seem to be as big of a Access consultant market, with the
snobbish C# developer's laughing at the "Access Programmer" moniker.
They sell internal IT execs on spending more money on overblown
systems that don't work as well as an Access app.

Sorry, that was my rant for the day.

'Sokay, I know how you feel.