David McAfee
davidmcafee at gmail.com
Mon Jan 24 19:28:46 CST 2011
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 >