[AccessD] Access 2K to SQL Server

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
>