[AccessD] Access 2K to SQL Server

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.
**********************************************************************