Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 557
  • Last Modified:

SQL Server Stored Procedure as Recordsource

Need to know how to assign an SQL Server parameterized stored procedure as a recordsource on a form through code.  I need to be able to modify the parameters at run-time.  There are multiple users so creating a permanent pass-through and modifying its SQL will not work unless I create it on the fly and use the userid as part of its name.
0
rsellis
Asked:
rsellis
  • 5
  • 3
  • 2
1 Solution
 
rsellisAuthor Commented:
Edited text of question
0
 
CarmyCommented:
rsellis
Create a pass-through query with the store procedure as the text of the query.  So if the stored procedure name is "sp_get_all_records 2" (2 being a paramater) the only text in the query should be sp_get_all_records 2(run the query on its own to test it)  
Next, create a simple procedure to change the text of the query as required to open the form.  Use Querydefs.  You might whant to look into changing the 'Connect' property for the different users.

0
 
CarmyCommented:
P.S the form you are talking about is based on the pass-through query as the record source.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
rsellisAuthor Commented:
I know I can create a permanent pass-through query and then modify its sql through a procedure.  The problem is that all users will be using the same database (we are on citrix with no hard drives) and I have 20 users all using the same permanent pass-through.  I believe when one user changes the pass-through for his parameters and it doesn't execute it before another user modifies it, the first user will get the second users dataset.  I would prefer to use a temporary querydef instead.  If I'm wrong about the multiple user problem, let me know.
Thanks - Rick
0
 
obsrhaydenCommented:
You can't change the type of a querydef in code and therefore cannot use a temporary querydef as a passthrough query without some other things happening first.  With your constraints, no HD, you will need a passthrough query that runs fast and returns an empty recordset.  Assign this to a recordset, r.  You can now do
Dim q as querydef

q=r.CopyQueryDef

This gives you a copy of the querydef that returned r.  You can edit the sql in this temporary querydef and the execute this new query to get your data.
0
 
rsellisAuthor Commented:
TO:  obsyhayden

I couldn't get the procedure to work - Could you tell me why this does not:

Sub ClientServer()

    Dim dbsCurrent As Database
    Dim qdfBestSellers As QueryDef
    Dim rstTopSeller As Recordset
    Dim q As QueryDef

    ' Open a database from which QueryDef objects can be
    ' created.
    Set dbsCurrent = CurrentDb

    ' Create a temporary QueryDef object to retrieve
    ' data from a Microsoft SQL Server database.
    'Set qdfBestSellers = dbsCurrent.CreateQueryDef("")
    Set qdfBestSellers = dbsCurrent.QueryDefs("qptPassThroughTest")

With qdfBestSellers
        .Connect = "ODBC;DATABASE=pubs;UID=sa;PWD=;" & "DSN=Publishers"
        .SQL = "Select * from Authors where AU_ID = '1'"
        Set rstTopSeller = .OpenRecordset()
    End With

    q = rstTopSeller.CopyQueryDef

    rstTopSeller.Close
    dbsCurrent.Close

End Sub
Also ...
I need to assign this recordset to a form's recordset

Thanks - Rick

0
 
obsrhaydenCommented:
Theproblem with the code is you need to go

Set q = rstTopSeller.CopyQueryDef

As for assigning the recordset to the form, I don't believe this is possible.  You'll have to break the binding and do everything behind the scenes.
0
 
rsellisAuthor Commented:
TO:  obsrhayden
Thanks for the info on using the SET (I forgot that)  but I really need to be able to assign the recordset to the form (which is unbound).  The only other way I know how to do it is to assign the values of each field in the recordset to each of the controls.  If that is the only way to do it then I guess I'll have to do that.  Your comment above suggested there may be a way to assign it to the form?
Rick
0
 
obsrhaydenCommented:
You can't assign the recordset to the form.  You have to assign the values from the recordset to the fields and update the database well the user leaves the form or record.  
0
 
rsellisAuthor Commented:
Ok, That's kind of what I anticipated.  Thanks for the help

Rick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now