MS Access getting data from SQL server databases using stored proc

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am trying to use a stored procedure created in MZ Access to retieve data
from an SLQ server databse.

I have the query working fine with parameters, but I need it to get the
input parameter from a user form with a combo box on it.

Stored Procedure:ALTER PROCEDURE dbo.StoredProcedure1
(@Assignee varchar(25))
AS SELECT Call, Status, Assignee, LoggedBy, DateRaised, DateCompleted,
Request, Action, SysDateChanged
FROM dbo.SKYSQL02_Envisage_dbo_HD_Call
WHERE (Assignee = @Assignee) AND (NOT (Status IN ('completed',
'Decline/Cancel')))
ORDER BY DateRaised DESC
 
You create a pass-through query with the parameter. Then use code to modify
the SQL property of the saved query to update the parameter.

CurrentDb.QueryDefs("qsptYourPT").SQL = "EXEC StoredProcedure1 " &
Me.txtAssignee
or
CurrentDb.QueryDefs("qsptYourPT").SQL = "EXEC StoredProcedure1 '" &
Me.txtAssignee & "'"
 
Back
Top