[AccessD] Access 2K to SQL Server

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