[AccessD] Access 2K to SQL Server

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