Solved Passing variable to SQL Server

[adhoustonj]

Hello AWF,
What is the best way to pass a hidden text box value to a sql server view?
I have a hidden text box on one of my forms that holds the department an employee belongs to.

This is how I have done it in access.

SELECT tblEmployees.emp_id, tblEmployees.department
FROM tblEmployees
WHERE tblEmployees.department=[Forms]![frmHomePage]![department]

Thanks

 

[theDBguy]

One way is to use a stored procedure. Another is to use a passthrough query where you modify the SQL statement in code before sending it. Not sure which would be the "best" one for you.

 

[adhoustonj]

One way is to use a stored procedure. Another is to use a passthrough query where you modify the SQL statement in code before sending it. Not sure which would be the "best" one for you.

Thank you - the pass through query sounds like what I need. The terminology will help me dig into this some more.

 

[Minty]

You can also use the same technique on the view from Access assuming you have linked to it.
This is a perfectly valid Access query

SELECT YourView.emp_id, YourView.department
FROM YourView
WHERE YourView.department=[Forms]![frmHomePage]![department]

 

[adhoustonj]

You can also use the same technique on the view from Access assuming you have linked to it.
This is a perfectly valid Access query

SELECT YourView.emp_id, YourView.department
FROM YourView
WHERE YourView.department=[Forms]![frmHomePage]![department]

Sweet. Will this process the filter in SQL server or will it send all of the data in the view to my local access FE and then apply the filter?
That is what I meant with "best way" - I should really start drinking coffee again.

 

[Minty]

Access should evaluate the form reference and simply send it as a normal where query to the Server.

You can easily test the efficiency yourself. Create a hard-coded version and the form reference version.
Load the saved queries into a recordset and do a rs.movelast, rs.movefirst with a timer on them in VBA and see if there is a noticeable difference.