I have a pretty common setup, local database Access 2013 .accde that clients run, with some local tables but mostly linked tables and views running on their company's Microsoft SQL Server 2005.
One of these views should filter orders based on order_type, a field in the order table. It currently does not, the filter control is to be implemented.
The view, let's call it viewOrderFilter, must be "dynamic". I thought I'd implement that by making it match this field, order_type in the view query, to a database variable.
How do I set @var_OrderFilter to a value with VBA code, so that I can add WHERE order_type=@var_OrderFilter to the view's query?
Edit: Is this valid syntax? Can these be executed queries or must they be stored procedures or somesuch? In the code, I was hoping to test whether an int can be set to NULL, but unfortunately, if I enter this in the Microsoft Server Management Studio anywhere, the program crashes, so I can't "try" anything there.
DECLARE @var_OrderFilter INT
SET @var_OrderFilter=NULL
Thankful for advice.
You can't parameterize a view like that. A view has to be fully defined when you create it.
To defer decisions or filtering to runtime, using parameters, you should create a function instead.