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
92
