stored procedure in access

[voroojak]

hi
is there any way to use sql stored procedure in access 2003?

i have a tored procedure that want to link it to access database but have no ided that it is possible or not?

thanks alot

[Jim Doherty]

hi
is there any way to use sql stored procedure in access 2003?

i have a tored procedure that want to link it to access database but have no ided that it is possible or not?

thanks alot

Have a look at the .ADP Project file format in Access it is designed to work directly with stored procedures and SQL server side records exposing the stored procedures and view interface for manipulation on the client side.

Regards

Jim

[Jim Doherty]

Have a look at the .ADP Project file format in Access it is designed to work directly with stored procedures and SQL server side records exposing the stored procedures and view interface for manipulation on the client side.

Regards

Jim

If you are committed to the MDB format then you can access the functionality of stored procedures via queries defined as 'pass through' queries and are documented in help.

In considering your options have a look also at the .ADP Project file format in Access it is designed to work directly with stored procedures and SQL server side records exposing the stored procedures and view interface for manipulation on the client side.

A simple example of using a code function within an ADP file to access a stored procedure where it accepts input parameters comprising of a date from and a date to value derived from, shall we say, a main menu screenform and returning a single output value to the client side application might be something like this (seemingly long winded visually speaking for a simple example that could be achieved in SQL I know,... but it merely illustrates the point)

 Function GetMyCount() 
On Error GoTo Err_GetMyCount
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "dbo.usp_MyCountBetweenDates"
cmd.CommandType = adCmdStoredProc
Dim par As ADODB.Parameter
Set par = cmd.CreateParameter("@datefrom", adDate, adParamInput)
cmd.Parameters.Append par
Set par = cmd.CreateParameter("@dateto", adDate, adParamInput)
cmd.Parameters.Append par
Set par = cmd.CreateParameter("@intResult", adInteger, adParamOutput)
cmd.Parameters.Append par
cmd.Parameters("@datefrom") = Format(Forms!frmMainMenu!DateFromCrit, "mm/dd/yyyy")
cmd.Parameters("@dateto") = Format(Forms!frmMainMenu!DateToCrit, "mm/dd/yyyy")
cmd.Execute
GetBookingCount = cmd.Parameters("@intResult").Value & " Records"
Exit_GetMyCount:
Exit Function
Err_GetMyCount:
DoCmd.Hourglass False
DoCmd.Echo True
MsgBox err.Description,vbinformation,"System Function GetCount Error" 
Resume Exit_GetMyCount
End Function
 

The server side stored procedure then might look something like this

 
CREATE PROCEDURE dbo.usp_MyCountBetweenDates
(@datefrom datetime,@dateto datetime,@intResult int output)
AS
SET NOCOUNT ON
SELECT @intResult=COUNT(*) FROM dbo.tblMyTable WITH (NOLOCK) WHERE (MyDate >=@datefrom AND MyDate<=@dateto)
SELECT @intResult
GO

Regards

Jim

[voroojak]

thanks alot
i try to do as you said. i hope it work for me. it seems hard.
thankssssssssss sssssssssssss

[Jim Doherty]

thanks alot
i try to do as you said. i hope it work for me. it seems hard.
thankssssssssss sssssssssssss

Well the learning curve is never simple.. but if you are working with stored procedures predominently then inevitably you going to need to know server side transact SQL language. If you intend to keep your transactions server side mostly you might as well dive straight in, do just that and work server side, and make the application reference and return only the data it needs. It is the optimum method of working with SQL Server

Regards

Jim :)