locked
about using MS-Access RRS feed

  • Question

  • User-1588418485 posted

    hi guys....can we write stored procedures in ms-access 2007...if possible how?

    Tuesday, May 18, 2010 6:08 AM

Answers

  • User954927490 posted

    stored procedure can not be created in access 2007. you will have to use sql query for this.

    if you want the syntax  for this this is below:
    using System.Data.OleDb;


    OleDbConnection con=new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" + HttpContext.Current.Server.MapPath("App_data\\yourdb.mdb"));

    if (con.State == ConnectionState.Open)
    {
    con.Close();
    con.Open();
    }
    else
    {
    con.Open();
    }


    OleDbCommand cmd = new OleDbCommand();
    cmd.CommandType = CommandType.Text;
    cmd.Connection=con;
      cmd.CommandText = "insert into tablename (columnname1) values (@Type)";
    cmd.Parameters.Add(new OleDbParameter("@Type", "Bnr"));
    cmd.ExecuteNonQuery();
    cmd.Cancel();
    cmd.Dispose();
     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 18, 2010 9:45 AM
  • User-1199946673 posted

    stored procedure can not be created in access 2007
     

    That's correct

    you will have to use sql query for this.

    Like I said, you can use saved queries like they are a Stored Procedure

    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "name of saved query";

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 18, 2010 9:54 AM

All replies

  • User-1199946673 posted

    No, in Access Stored pocedures are not supported. You can only create single SQL statement and store them as Saved Query. In .NET, you can use those query just like Stored Procedures

    Tuesday, May 18, 2010 9:28 AM
  • User954927490 posted

    stored procedure can not be created in access 2007. you will have to use sql query for this.

    if you want the syntax  for this this is below:
    using System.Data.OleDb;


    OleDbConnection con=new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" + HttpContext.Current.Server.MapPath("App_data\\yourdb.mdb"));

    if (con.State == ConnectionState.Open)
    {
    con.Close();
    con.Open();
    }
    else
    {
    con.Open();
    }


    OleDbCommand cmd = new OleDbCommand();
    cmd.CommandType = CommandType.Text;
    cmd.Connection=con;
      cmd.CommandText = "insert into tablename (columnname1) values (@Type)";
    cmd.Parameters.Add(new OleDbParameter("@Type", "Bnr"));
    cmd.ExecuteNonQuery();
    cmd.Cancel();
    cmd.Dispose();
     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 18, 2010 9:45 AM
  • User-1199946673 posted

    stored procedure can not be created in access 2007
     

    That's correct

    you will have to use sql query for this.

    Like I said, you can use saved queries like they are a Stored Procedure

    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "name of saved query";

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 18, 2010 9:54 AM