[AccessD] Access 2K to SQL Server

Asger Blond ab-mi at post3.tele.dk
Tue Jan 25 06:29:36 CST 2011


In that case David's solution should work just fine.
In SQL Server create a sp (quotation from David):
>CREATE PROCEDURE stpSomeNameHere (@AsOfDate AS DATETIME) AS 
> SELECT *
> FROM vwSomeView WHERE SomeDate>= @AsOfDate

In Access create a stored query "SomeQueryName" without selecting any table, but choose Pass-Through in the query designer (in Access 2000 I think this option is found in menu Query | SQL-specific | Pass-Through) - then open the Properties Window and supply the ODBC Connection String for your SQL Server database - in the Query pane type: EXEC dbo.stpSomeNameHere '20110101' - save and close the query.
Then create a form bound to the saved query "SomeQueryName". On the top of this form insert a textbox txtAsOfDate and a commandbutton with this code (quotation from David):
>  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
>   
>   Me.RecordSource = "SomeQueryName" 'added by AB
>
>   Set db = Nothing

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af jwcolby
Sendt: 25. januar 2011 12:13
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Access 2K to SQL Server

As it happens, I am not looking for a bound form at all.  I know that A2K can not do updateable 
bound forms.  Really I just have to learn how to call the SP from Access, passing the parameter and 
getting the recordset into something.

John W. Colby
www.ColbyConsulting.com

On 1/25/2011 5:10 AM, Asger Blond wrote:
> David,
> As I read the original question, John wants an updatable bound form based on a pass-through query.
> AFAIK this is not possible, since a pass-through query is non-updatable.
> Your solution works fine for a R/O form.´
> It is of course possible to make updates via this kind of form but then you need to call a separate update procedure, e.g an update sp from SQL Server.
> And then in my vocabulary you are essentially working with an "unbound-form solution".
> Asger
>
> -----Oprindelig meddelelse-----
> Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af David McAfee
> Sendt: 25. januar 2011 02:29
> Til: Access Developers discussion and problem solving
> Emne: Re: [AccessD] Access 2K to SQL Server
>
> Cool, make a stored procedure which looks for an input parameter like I
> showed in the first reply then modify the QueryDef when you need to call the
> query.
>
>
> You can modify the querydef for a passthrough in the same manner.
>
> This is an MDB, not an ADP, right?
>
>
>
> On Mon, Jan 24, 2011 at 5:19 PM, jwcolby<jwcolby at colbyconsulting.com>wrote:
>
>> 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
>>>>
>>>>   --
>> 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





More information about the AccessD mailing list