From: uli.achilles@googlemail.com   
      
   Hallo,   
      
   I am new to SQL Server and I want to migrate an MS Access application   
   (Backend, Frontend) to SQL Server. More precise:I want to put the   
   backend to SQL Server and use Access as Frontend.   
   The application deals with courses with a duration of 4 to 8 weeks and   
   the students participating in these courses.   
      
   Coming from Access I wanted to have an updateable view like:   
      
   SELECT CourseID, CourseName, Teacher, CoursePlace, CourseBegin, CourseEnd   
   FROM courses   
   WHERE CourseBegin <= @somedate + 6 AND CoursEnd >= @somedate   
      
   (@somedate is a monday and the view selects the courses that are actual   
   for that week).   
      
   Of course I found soon that it is not possible to pass parameters to a   
   view in SQL server. As a solution I created a table "parameters" with   
   just one row to hold the parameter and joined it to the courses-table:   
      
   SELECT CourseID, CourseName, Teacher, courses.CoursePlace, CourseBegin,   
   CourseEnd   
   FROM courses   
   INNER JOIN   
   parameters ON courses.CoursePlace = parameters.CoursePlace   
   WHERE CourseBegin <= parameters.somedate + 6 AND CourseEnd >=   
   parameters.somedate   
   (CoursePlace is the same for all courses)   
      
   So the clients set their 'somedate' in the parameters-table and get the   
   wanted selection of courses. Of course each client has to set the   
   "parameter" each time he runs the view.   
      
   I came across that construction in the net, but it was commented as   
   horrible, ugly and so forth.   
      
   My question: Is it really that bad? And why?   
      
   Based on that view and a table with students and a table which records   
   the students of each course is a view showing all the students of the   
   courses of the selected week. This view gives typically 150 to 300   
   students in 10 to 25 courses.   
   There are at most 5 or 6 users working with the application.   
      
   I tried also the recommended solution with a table valued function. With   
   Access as frontend this has the severe disadvantage, that I have to use   
   pass through queries which are not updateable.   
      
   I also tried Access-queries on the linked tables filtering by their   
   WHERE-clauses, but they ran significantly slower.   
      
   Thank you in advance for any suggestions.   
      
   Ulrich Achilles   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)