? Convert Access Query to SQL Query

[eforce]

Hello All,

Here is my issue:

I've upsized my Access database to SQL and thus I'm using the link table thingy.

I have my form's record source is based on a access query. My access query uses a "WHERE" clause which is based on a form's field. This works fine in access but when I tried to use the query analyzer to build a view and I tried using the "WHERE" clause that I use in my access query; it gave me an error. I'm assuming that this is because I'm referencing a form but how can I make this work if I want to use SQL views instead of access queries to base my forms on?

Note: When my users open up the application, they are prompted with the user login mdw security pop-up. I have another form that is hidden called "login" that captures the "Current User" and this is what I use to restrict the data the users can view which is based on their username.

My "WHERE" clause in my access query references this hidden form.

Thanks!

E-force

 

[pdx_man]

If you want the processing to take place on SQL Server, use Stored Procedures instead of Views. You can pass parameters to them, but you have to update the Pass-Through query dynamically. Let's say your Pass-Through query is named Qry_Tester. On the Before Update event, have:

Dim qdfLoop As QueryDef
Dim dbs As Database

Set dbs = CurrentDb
dbs.QueryDefs!Qry_Tester.SQL = "exec dbname.dbo.sp_proc_name " & Forms![Frm_Hidden]![The_Ref]


Now, you could have a View returning ALL the possibilities and then have the View as the datasource for a query where you reference your form, but the above is really not that difficult to do. I have dozens of queries where I do this. Just get your Stored Procedure working first with the input parameter hard coded, then word towards the above example.

 

[eforce]

Thanks for responding but I've read much about people saying use stored procedures but what the crap is it and what is the syntax. I'm new to sql but a fast learner.

Is it a sql statement that is called like a function in a module?

thanks,

E-force