[AccessD] Access 2K to SQL Server

John, it's just like modifying an existing query in Access:

'This Sets the query Def:
  Dim db As DAO.Database
  Dim qd As DAO.QueryDef
  Dim sSQL  As String

   sSQL = "EXEC dbo.stpSomeNameHere '" & Me.txtAsOfDate & "'"
   Set db = CurrentDb
   db.QueryDefs("SomeQueryName").SQL = sSQL

   Set db = Nothing


Run the query as you would (via a recordset, bound to a form...)

This is assuming of course they already have an ODBC link to the view that
we are talking about,
 which I am assuming they do since you said they are linking to views.

Just run the code above before calling the view.






On Mon, Jan 24, 2011 at 1:08 PM, jwcolby <jwcolby at colbyconsulting.com>wrote:

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