[AccessD] Access 2K to SQL Server

Asger Blond ab-mi at post3.tele.dk
Tue Jan 25 04:10:38 CST 2011


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





More information about the AccessD mailing list