October 2, 2007 at 10:44 am
Hi all.
I've been using Access (in this case ver 2002) for quite some time now and I am getting more involved with SQL Server 2000. I would like to know if it is possible to use the value of a text field in a form as Criteria for a view in the same way you can an Access query?
I tried this myself and it would not accept the syntax,
forms!frmMyForm.txtMyText.value so I would like to know if there is some special syntax I should be using or if it is not possible to do at all ? And if not, what do developers do in SQL Server in such a situation ?
Thanks in advance,
Mitch.....
October 2, 2007 at 10:54 am
You must concatenate the control form into the query itself.
One step better would be to use stored procs.
Can you post the code that calls sql server? We'll be able to get you on your way.
October 2, 2007 at 11:11 am
I think I may have set it up incorrectly !
I have the View from SQL Server linked into the Access mdb as a table via ODBC. I could of course do all this in Access but ideally i'd like SQL server to do most of the work for this application and use Access as the front end to allow users to input their values.
Am I going about this the wrong way ?
Thanks again,
Mitch.....
October 2, 2007 at 11:15 am
I don't know about the best way to do this. Personally, I usually setup a connection directly to the server in the access code, then run my queries that way.
But I live in an environement where all the info is kept on SQL Server. Access is only used as a GUI, so I don't really relate to your setup.
I'm not sure I can be of much more help for you. Maybe if you post the code, I'll be able to point out a few things.
October 2, 2007 at 11:29 am
About the only way to emulate this is to create a passthrough query object, which you repopulate with a somewhat dynamic call. Of course - this will create a read-only dataset (passthrough results cannot be updated). Otherwise - you're building ADO calls to get recordsets back, etc....
A few things to remember: SQL Server doesn't really do parameterized views, so the concept you're looking at is implemented through either a stored procedure or through a table-value function. I've personally found that Access tends to prefer the function approach., but I haven't had the pleasure of playing against Access 2007 + SQL yet, so I can't help you there.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 2, 2007 at 11:31 am
Where did he talk about access 2007?
October 2, 2007 at 11:51 am
Oh I just now caught the v 2002 in the initial post. I hadn't noticed ANY version mentioned until then.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 2, 2007 at 4:08 pm
Cheers Ninja.
Sounds like I do have a different setup, probably because I'm building this from an Access perspective and SQL Server came into the mix after.
All my tables are upsized to SQL and the Views are created in SQL Server but are then linked into Access mdb via ODBC as SQL Server tables.
I was just hoping I could use forms to manipulate the criteria in the views.
Thanks,
Mitch.....
October 2, 2007 at 4:19 pm
Consider going to a full ADP (Access Data Project). You can use all the usefull tools to create the interface and access still takes case of most of the DML generation. It's really the best of both worlds.
October 2, 2007 at 4:31 pm
What I said earlier does apply to your scenario. SQL doesn't have parameterized views. What does allow for "parameterizing" would be a SQL function or a stored procedure if you wish to have the processing done on the SQL side, which can be done using a pass-through query object, or ADO calls. Either way - the preferred method from within Access 2002/2003 would be to use a table-valued function.
As ninja said though - post the code - let's see if we can help.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply