August 22, 2005 at 2:49 pm
I'm an SQL guy and I could use a quick pointer on MS Access. We don't build MS Access apps very often - and when we have, we have typically just linked tables to SQL or AS400.
I want to change that a bit and start using pass-through queries and proc calls to keep my programming in SQL.
So, with that said, I am trying to create a query in Access that will allow me to execute a proc while passing parameters.
So far, I've find a can create a pass through and simply call the proc (EXEC DB.owner.spProcName) I just haven't found a way to declare and pass parameters to the proc.
I tried a quick test with an SQL proc that returned a dataset only. I created the passthrough execution as MSAccessQuer1 and then created another standard query (where it allows me to create variables to be used as seletion criteria). However, the SQL proc is returning the ENTIRE dataset to MS Access before evaluating the criteria. This is expected, of course...
So, can anyone offer some quick Access pointers for this?
Thanks in advance.
Ryan
August 22, 2005 at 2:52 pm
Here's an exemple :
SP:
CREATE PROCEDURE [dbo].[GetFormsByADP] @FkADP as int
AS
SET NOCOUNT ON
SELECT TOP 100 PERCENT PkObjADP, ObjName
FROM dbo.ObjADPS
WHERE FkTypeObj = 1 AND FkADP = @FkADP
ORDER BY ObjName
SET NOCOUNT OFF
vb6 call :
Private Function exec_GetFormsByADP(ByVal FkADP As Integer, Optional ByRef ReturnValue As Integer) As Integer
On Error GoTo Gestion
Dim MyCmd As ADODB.Command
Set MyCmd = New ADODB.Command
MyCmd.CommandText = "dbo.GetFormsByADP"
MyCmd.CommandType = adCmdStoredProc
Dim MyParam As ADODB.Parameter
Set MyParam = New ADODB.Parameter
MyParam.Direction = adParamReturnValue
MyParam.Name = "@Return"
MyParam.Type = adInteger
MyCmd.Parameters.Append MyParam
Set MyParam = New ADODB.Parameter
MyParam.Name = "@FkADP"
MyParam.Value = FkADP
MyParam.Size = 4
MyParam.Direction = adParamInput
MyParam.Type = adInteger
MyCmd.Parameters.Append MyParam
MyCn.Open
MyCmd.ActiveConnection = MyCn
MyCmd.Execute exec_GetFormsByADP
MyCn.Close
ReturnValue = CInt(MyCmd.Parameters("@Return").Value)
Set MyParam = Nothing
Set MyCmd = Nothing
Exit Function
Gestion:
ErrHandler ModuleName, Me.Name, "exec_GetFormsByADP", Err
MsgBox Err.Description & " : " & Err.Number
End Function
August 23, 2005 at 3:16 am
Ryan,
I found an interesting article by Danny Lesandrini on this topic at:
http://www.databasejournal.com/features/msaccess/article.php/3407531
His last paragraph provides a magically simple solution to calling stored procedures.
Richard
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply