jwcolby
jwcolby at colbyconsulting.com
Mon Jan 24 19:35:40 CST 2011
Right, an MDB. John W. Colby www.ColbyConsulting.com On 1/24/2011 8:28 PM, David McAfee wrote: > 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 >>