jwcolby
jwcolby at colbyconsulting.com
Tue Jan 25 21:06:58 CST 2011
;) John W. Colby www.ColbyConsulting.com On 1/25/2011 1:00 PM, David McAfee wrote: > Asger, I'm trying to convert him. He'll eventually come over to the dark > side. ;) > > > > On Tue, Jan 25, 2011 at 2:10 AM, Asger Blond<ab-mi at post3.tele.dk> wrote: > >> David, >> As I read the original question, John wants an updatable bound form based >> on a pass-through query. >> AFAIK this is not possible, since a pass-through query is non-updatable. >> Your solution works fine for a R/O form.´ >> It is of course possible to make updates via this kind of form but then you >> need to call a separate update procedure, e.g an update sp from SQL Server. >> And then in my vocabulary you are essentially working with an "unbound-form >> solution". >> Asger >> >> -----Oprindelig meddelelse----- >> Fra: accessd-bounces at databaseadvisors.com [mailto: >> accessd-bounces at databaseadvisors.com] På vegne af David McAfee >> Sendt: 25. januar 2011 02:29 >> Til: Access Developers discussion and problem solving >> Emne: Re: [AccessD] Access 2K to SQL Server >> >> Cool, make a stored procedure which looks for an input parameter like I >> showed in the first reply then modify the QueryDef when you need to call >> the >> query. >> >> >> You can modify the querydef for a passthrough in the same manner. >> >> This is an MDB, not an ADP, right? >> >> >> >> On Mon, Jan 24, 2011 at 5:19 PM, jwcolby<jwcolby at colbyconsulting.com >>> wrote: >> >>> It was security. I looked at other objects that could be seen through >> the >>> dsn and they all had DISCO and public enabled for reads. I set that for >>> mine and they were visible. >>> >>> >>> John W. Colby >>> www.ColbyConsulting.com >>> >>> On 1/24/2011 5:08 PM, David McAfee wrote: >>> >>>> It does sound like security. >>>> >>>> Can you create a stored procedure on the server? >>>> >>>> >>>> On Mon, Jan 24, 2011 at 2:03 PM, jwcolby<jwcolby at colbyconsulting.com >>>>> wrote: >>>> >>>> I am running into something that I have never seen before. >>>>> >>>>> When I try to create a DSN back to the sql server I end up seeing the >>>>> server, but when I select the server I only see a small set of existing >>>>> views, 8 or so. there are hundreds of tables and dozens of views but I >>>>> can't see any of them. I don't know why, or how SQL Server limits what >> I >>>>> can see for the DSN build process. I am assuming that it has to do >> with >>>>> security but this is new to me. >>>>> >>>>> >>>>> John W. Colby >>>>> www.ColbyConsulting.com >>>>> >>>>> On 1/24/2011 4:25 PM, Rusty Hammond wrote: >>>>> >>>>> John, >>>>>> >>>>>> Have you tried an Access query tied to the linked view where the query >>>>>> provides the filtering? I'm sure I'll be corrected if I'm wrong but >> it >>>>>> should return from SQL only the records you need. >>>>>> >>>>>> If the dataset being returned can be read-only (no editing) then you >> can >>>>>> use a pass-through query in Access to call a stored procedure or send >> a >>>>>> SELECT statement directly to the SQL server. You can edit the SQL of >>>>>> the pass-through query in code just like any other query. To setup a >>>>>> pass-through create a blank query, go to the SQL view, go to the Query >>>>>> menu, choose SQL Specific, then Pass-Through. Then right-click on the >>>>>> title bar of the query window, go to Properties, Use the build button >> on >>>>>> the ODBC Connect Str property to build your connect string to the SQL >>>>>> server, set Return Record to Yes. >>>>>> >>>>>> HTH, >>>>>> >>>>>> Rusty >>>>>> >>>>>> >>>>>> >>>>>> -----Original Message----- >>>>>> From: accessd-bounces at databaseadvisors.com >>>>>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby >>>>>> Sent: Monday, January 24, 2011 3:09 PM >>>>>> To: Access Developers discussion and problem solving >>>>>> Subject: Re: [AccessD] Access 2K to SQL Server >>>>>> >>>>>> And can I link to a stored procedure? How to I send the parameter to >>>>>> the stored procedure. >>>>>> >>>>>> You are just a little too light on the actual details for me to get >> this >>>>>> done. >>>>>> >>>>>> I know how to create stored procedures, and I know the syntax in the >>>>>> stored procedure to pass in a parameter. I do not know the syntax in >> a >>>>>> stored procedure to return a recordset. And I haven't a clue how to >> use >>>>>> any of this on the Access side of things. >>>>>> >>>>>> Thanks, >>>>>> >>>>>> John W. Colby >>>>>> www.ColbyConsulting.com >>>>>> >>>>>> On 1/24/2011 4:00 PM, David McAfee wrote: >>>>>> >>>>>> CREATE PROCEDURE stpSomeNameHere (@AsOfDate AS DATETIME) AS SELECT * >>>>>>> FROM vwSomeView WHERE SomeDate>= @AsOfDate >>>>>>> >>>>>>> Modify the querydef as needed for the input parameter in Access. >>>>>>> >>>>>>> >>>>>>> >>>>>>> On Mon, Jan 24, 2011 at 12:50 PM, >>>>>>> >>>>>>> jwcolby<jwcolby at colbyconsulting.com>wrote: >>>>>> >>>>>> >>>>>>> One of my clients is mired in Access 2K. He is linking to views in >>>>>>> >>>>>>>> SQL Server but these are fixed views that at this point are pulling >>>>>>>> hundreds of thousands of records when he really only needs the last >> X >>>>>>>> >>>>>>>> >>>>>>> days, or for Claim X etc. >>>>>> >>>>>>> >>>>>>>> How can I create a view (or stored procedure) out in SQL Server that >>>>>>>> accepts a parameter such as a date or a claim ID and allow sql >> server >>>>>>>> >>>>>>>> >>>>>>> to perfrom the filter and return a small result set. >>>>>> >>>>>>> >>>>>>>> REMEMBER this is A2K. It is my understanding that A2K does not >> allow >>>>>>>> >>>>>>>> >>>>>>> some of the fancy stuff that later versions of Access allows - like >>>>>> >>>>>>> binding a form to an ADO recordset and having it be R/W. >>>>>>>> >>>>>>>> Any help would be hugely appreciated. >>>>>>>> >>>>>>>> -- >>>>>>>> John W. Colby >>>>>>>> www.ColbyConsulting.com >>>>>>>> -- >>>>>>>> AccessD mailing list >>>>>>>> AccessD at databaseadvisors.com >>>>>>>> http://databaseadvisors.com/mailman/listinfo/accessd >>>>>>>> Website: http://www.databaseadvisors.com >>>>>>>> >>>>>>>> -- >>>>>>>> >>>>>>> AccessD mailing list >>>>>> AccessD at databaseadvisors.com >>>>>> http://databaseadvisors.com/mailman/listinfo/accessd >>>>>> Website: http://www.databaseadvisors.com >>>>>> ********************************************************************** >>>>>> WARNING: All e-mail sent to and from this address will be received, >>>>>> scanned or otherwise recorded by the CPI Qualified Plan Consultants, >>>>>> Inc. >>>>>> corporate e-mail system and is subject to archival, monitoring or >> review >>>>>> by, and/or disclosure to, someone other than the recipient. >>>>>> ********************************************************************** >>>>>> >>>>>> -- >>>>>> >>>>> AccessD mailing list >>>>> AccessD at databaseadvisors.com >>>>> http://databaseadvisors.com/mailman/listinfo/accessd >>>>> Website: http://www.databaseadvisors.com >>>>> >>>>> -- >>> AccessD mailing list >>> AccessD at databaseadvisors.com >>> http://databaseadvisors.com/mailman/listinfo/accessd >>> Website: http://www.databaseadvisors.com >>> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >>