Create Stored Procedure Access 2007

Question

User-285805751 posted

Hi,

i am developing asp.net project my backend tool using ms access 2007 database.i need how to create procedure in access.pls help me

Answer 1

User-821857111 posted

You can either do this using Access itself (search for an Access tutorial) or you can use DDL to create a procedure programmatically eg:

var sql = "CREATE PROCEDURE GetProductsByCategory AS SELECT * FROM Products WHERE CategoryId = @categoryId";
using (var conn = new OleDbConnection(connectionstring))
{
  using (var cmd = new OleDbCommand(sql, conn))
  {
    conn.Open();
    cmd.ExecuteNonQuery();
  }
}

 

Answer 2

User143067745 posted

Where to write Query in access 2007 or 2010 : click here

How to write stored procedure : Click Here

Answer 3

User3866881 posted

i need how to create procedure

There's no real "Stored procdure" in Access……However I think you can just write something query of CRUD with parameters in the Query window，and save it as a "Query" in Access file，in fact you can directly use it just like using a Stored procdure。

For more you can refer this：http://social.msdn.microsoft.com/Forums/en/accessdev/thread/33f3f6bc-03b2-4f64-84ca-cef65bbc0eee

Reguards！

Answer 4

User-1199946673 posted

However I think you can just write something query of CRUD with parameters in the Query window，and save it as a "Query" in Access file，in fact you can directly use it just like using a Stored procdure。

Once again, you're just repeating what others already explained

For more you can refer this：http://social.msdn.microsoft.com/Forums/en/accessdev/thread/33f3f6bc-03b2-4f64-84ca-cef65bbc0eee

I don't think this is a good resource at all. A lot of non-sense is told in this thread!!!! Because they keep saying that you can not execute a stored query just like a Stored Procedure, but we know better!

To execute a stored query, the commandtext should be set to the name of the stored query. The CommandType should be set to StoredProcedure, and if the query contains parameters, they must be added to the parameter collection in the same order they (first) appear in the stored query. However, in the  stored query, you can also specify a parameter collection. If that is done, than that's the order the parameters should be added.

Answer 5

User3866881 posted

Hello hans_v:)

In order to give a full answer to the question asked by the questioner officially, sometimes I have to offer a whole answer, maybe there's something the same as what you've said, so here's a conclusion, so please don't challenge me.

As to your saying, You think this link isn't good, however this offers the same situation of Update Query and how to use that, The questioner has solved the problem successfully, this is a good sample in my mind.

Kindly correct me if you have anything else.....

Reguards!

Answer 6

User-1199946673 posted

however this offers the same situation of Update Query and how to use that

Who says we're talking about an Update query?

The questioner has solved the problem successfully

Yes, by NOT creating a stored query (because this wouldn't work according to some of the people in that thread), but to write the query in the commandtext.

this is a good sample in my mind.

It doesn't answer to the question being asked.... Also, as I said before, a lot of non-sense is being told in that thread!