Arthur Fuller
artful at rogers.com
Sun Feb 19 22:08:56 CST 2006
In my experience, this is best achieved with either a view or a table UDF that returns a recordset. This works very nicely in a master-detail form as well, since Access automatically sets up the params to pass to the child. Master DataSource: CustomersByCompanyName_qs (query select) Child DataSource: OrdersByCustomerID_qs (query select) Each of these is a view: SELECT * FROM Customers ORDER BY CompanyName SELECT * FROM Orders ORDER BY CustomerID, OrderID DESC (The last part of the second view I like because it places the most recent orders -- the ones of most interest -- at the top of the list.) I played around for some time with sprocs and stubbed my toes on the same difficulties you are having. Then I discovered that views do the work beautifully, and that Access automatically sets up the params for you when dealing with a master-detail form. I further refined this strategy to deal with situations such as an Orders form in continuous mode that by default shows the orders of the past 30 days, but also has buttons for "Last 60", "Last 90", "Last 120" and "All". My first crack at this used separate views, and all my OnClick code did was swap a new view name into the DataSource attribute. Later I refined this approach, making the DataSource a UDF that accepted a parameter, and changing the parameter (NumberOfDays) to suit. I have had decidedly limited success with calling sprocs from Access forms, and expecting insert-update-delete capabilities. With views and UDFs, this is a cinch. So I tended to go with what worked. HTH, Arthur -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of John Colby Sent: January 28, 2006 10:16 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] SQL 2005 Server Management Studio Build a SP in 2005 (express for me). It is read-only, regardless of whether I try and edit the data it inside of SMSE or in a pass-through out in Access. I am trying to bind a form to a SP recordset. John W. Colby www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Robert L. Stewart Sent: Saturday, January 28, 2006 9:37 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] SQL 2005 Server Management Studio I am already going that way and have been for over a year. The problem is that I essentially have wasted the last year because the difference between VS 2003 and 2005 is so radical. Now I am relearning. John, What is the SPs read-only problem? Can you explain what you are trying to do again? Robert At 12:00 PM 1/27/2006, you wrote: >Date: Fri, 27 Jan 2006 12:19:34 -0500 >From: "Susan Harkins" <harkinsss at bellsouth.net> >Subject: Re: [dba-SQLServer] SQL 2005 Server Management Studio >To: <dba-sqlserver at databaseadvisors.com> >Message-ID: <002a01c62365$e41183e0$aeb3d6d1 at SUSANONE> >Content-Type: text/plain; charset="us-ascii" > > >To be honest, once I get over the learning curve I am leaning towards >abandoning the MDB as a BE. > >========John, if I stay in this market, I am seriously considering the >same thing. > >Susan H. _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com