does Access support stored procedure

  • Thread starter Thread starter Utada P.W. SIU
  • Start date Start date
U

Utada P.W. SIU

I think I really weak in Access

I have try to do 2 sql statement in the query

example:
Insert .......;

update .....;

but it prompt me I cannot add more query after first ";"

I think I can apply sql server stored procedure concept, then I using
BEGIN - END
but it cannot...any idea?

thx~
 
I think I really weak in Access

I have try to do 2 sql statement in the query

example:
Insert .......;

update .....;

but it prompt me I cannot add more query after first ";"

I think I can apply sql server stored procedure concept, then I using
BEGIN - END
but it cannot...any idea?
Click to expand...

Perhaps you should install MSDE (from the SQL folder on your Access
CD) - it's a full version of SQL Server and supports multiple SQL
statements in a query and stored procedures. Access JET does not
support either.

You can run multiple Queries sequentially by storing them as separate
queries and executing them from code or from a macro; in Access,
typically one would use VBA code in Form events to accomplish the same
goals as stored procedures in SQL.

If you're expecting Access to behave exactly like SQL/Server... you'll
be preplexed and blocked frequently. They are both relational
databases, but have many, many differences in programming and
implementation!
 
Utada P.W. SIU said:
sorry...would you please tell me what is Access ADPs? what it stand for?
thx~
Click to expand...

ADP = Access Data Project.

This feature become part of ms-access in a2000 (and later). When you create
and use a ADP project, you are using SQL server as the back end. So, when
you create a table in the table design view, you are in fact sending sql
table create commends to the sql server.

This means that ALL of your sql queries are executed on the server side.
There is no local tables. You must use either the free desktop version of
sql (included on the office cd), or use sql server with ADP projects.

So, if you create a small application on your desktop using the desktop
edition of sql server, you can then migrate the application to big full sql
server with NO code changes required. When you use ms-access this way, you
do have stored procedures.

Albert K.
 
thx a lot..
then I think this not suit for me as I need using JAVA with Access
Originally, I think Access can support SP so I can send a parameter to
Access to execute to perform higher security and performance (as I know if
we using SP in SQL server, it can been done, right?)
But...um...I think I need hardcode in the java program

thx~
 
thx a lot~ John
I am not using Access Form, I just use it as database and using Java as
front end
hehe~~
 
Access queries are not the same as SQL Server stored procedures, but they do
support parameters. Here's an example (still under construction) from a
current project.

The Access query ...

PARAMETERS [MinDate] DateTime, [MaxDate] DateTime;
SELECT Accident.*
FROM Accident
WHERE (((Accident.AccidentDate)>=[MinDate] Or [MinDate] Is Null) AND
((Accident.AccidentDate)<=[MaxDate] Or [MaxDate] Is Null))
ORDER BY Accident.AccidentDate DESC , Accident.LocationCode
WITH OWNERACCESS OPTION;

Some C# code that sets the parameter values and retrieves the result of the
query ...

private void btnGetData_Click(object sender, System.EventArgs e)
{
try
{
this.oleDbSelectCommand1.CommandText = @"qryAccidentGetFiltered";
this.oleDbSelectCommand1.CommandType =
System.Data.CommandType.StoredProcedure;
this.oleDbSelectCommand1.Parameters.Clear();
System.Data.OleDb.OleDbParameter param =
this.oleDbSelectCommand1.CreateParameter();
param.DbType = System.Data.DbType.DateTime;
param.Direction = System.Data.ParameterDirection.Input;
param.OleDbType = System.Data.OleDb.OleDbType.Date;
param.ParameterName = @"[MinDate]";
param.IsNullable = true;
if (this.EarliestDateTextBox.Text.Trim().Length != 0)
{
param.Value =
System.DateTime.Parse(this.EarliestDateTextBox.Text.Trim());
}
Else
{
param.Value = System.DBNull.Value;
}
this.oleDbSelectCommand1.Parameters.Add(param);
param = this.oleDbSelectCommand1.CreateParameter();
param.DbType = System.Data.DbType.DateTime;
param.Direction = System.Data.ParameterDirection.Input;
param.OleDbType = System.Data.OleDb.OleDbType.Date;
param.ParameterName = @"[MaxDate]";
param.IsNullable = true;
if (this.LatestDateTextBox.Text.Trim().Length != 0)
{
param.Value =
System.DateTime.Parse(this.LatestDateTextBox.Text.Trim());
}
Else
{
param.Value = System.DBNull.Value;
}
this.oleDbSelectCommand1.Parameters.Add(param);
this.dataSet11.Clear();
this.oleDbDataAdapter1.Fill(this.dataSet11);
}
catch (System.Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
}
 
Utada P.W. SIU said:
thx a lot..
then I think this not suit for me as I need using JAVA with Access
Click to expand...

Ah...yes you are correct. In fact, when using any kind of application like
VB, or C++ or JAVA, you in fact do NOT need to install ms-access on the pc,
but only the JET database engine. So, in effect, you are not really using
ms-access.

You should not think of much differences when using ms-access to connect and
use the JET engine, or JAVA. In both cases, you are connecting to, and using
the JET database engine.
Originally, I think Access can support SP so I can send a parameter to
Click to expand...

No, it does not. You can certainly use some sql, and pass parameters to that
sql, but this works for saved quires when using ms-access. It turns out that
JET4 and later does allow you to connect to JET and actually create/save
sql commands via DLL.
Access to execute to perform higher security and performance (as I know if
we using SP in SQL server, it can been done, right?)
But...um...I think I need hardcode in the java program
Click to expand...

Yes...you might as well put your sql in the java side. You will in general
not get any better performance then if you had used saved queries in
ms-access.
 
Back
Top