David McAfee
davidmcafee at gmail.com
Tue Jan 25 12:00:17 CST 2011
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 >