jwcolby
jwcolby at colbyconsulting.com
Tue Jan 25 10:26:52 CST 2011
Thanks to everyone for the input.
I got it working. I wrote a function as follows but this is just a first pass. By passing in the
name of the querydef, and stored procedure I can do querydefs for different uses. This specific
querydef is for a readonly recordset for a set of check data to be displayed in a subform
in a JIT tab.
'---------------------------------------------------------------------------------------
' Procedure : sp_QDFByClaimant
' Author : jcolby
' Date : 1/25/2011
' Purpose : Initializes a specific querydef to execute a specific Stored Procedure
'passing in a specific claimant ID
'---------------------------------------------------------------------------------------
'
Function sp_QDFByClaimant(strQDFName As String, strSPName As String, lngCLMTID As Long)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
On Error GoTo Err_sp_QDFByClaimant
Set db = dbDAO()
strSQL = "EXEC dbo." & strSPName & " " & lngCLMTID
Set qdf = db.QueryDefs(strQDFName)
qdf.SQL = strSQL
db.QueryDefs.Refresh
'DoCmd.OpenQuery strQDFName
Exit_sp_QDFByClaimant:
On Error Resume Next
Exit Function
Err_sp_QDFByClaimant:
Select Case Err
Case 0 '.insert Errors you wish to ignore here
Resume Next
Case Else '.All other errors will trap
Beep
LogErr Err.Number, Err.Description, Erl, cstrModule, "sp_QDFByClaimant"
Resume Exit_sp_QDFByClaimant
End Select
Resume 0 '.FOR TROUBLESHOOTING
End Function
John W. Colby
www.ColbyConsulting.com
On 1/25/2011 7:29 AM, Asger Blond wrote:
> 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
>>>