[AccessD] Access 2K to SQL Server

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
>



More information about the AccessD mailing list