March 27, 2008 at 3:28 am
Morning All
Im currently converting an Access MDB over to an ADP with SQL Backend. One of the things it did as an MDB was Dynamically change a Query's SQL via the QueryDef method, depending which person was using the database.
Obviously in an ADP we use Views/SPs and not Querys, therefore i presume (and i tested) QueryDefs dont work on Views.
Is there anyway to change the SQL of a View from within Access?
Thanks in advance
Scott
March 28, 2008 at 8:06 am
Scott,
There are several approaches you can take:
1. Call a different stored procedure from within your frontend for each session user id, or
2. Pass the session user id to a stored procedure and execute a different SELECT statement within the stored procedure based on this user id, or
3. Create a parameterized stored procedure on the backend. Change the values of the parameters within your frontend based on the session user id and pass them to the stored procedure. Your stored procedure in this case would have only one SELECT statement.
4. A derivation of 3. If you have many users, create a parameters table on the backend keyed by user id. Do a join to this table by user id in your SELECT statement and use the parameter values as your SELECT criteria. You only need to pass the session user id to the stored procedure.
Tom
March 28, 2008 at 8:10 am
Hi Tom
Thanks very much for your reply. Im going to have a try with Number 3, it seems at face value to be the best for me!
Id never even thought of a Stored Procedure, im too "spoiled" by Access 2000, need to bring myself into SQL ways i think!
Scott
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply