Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server Stored Procedure as Recordsource

Posted on 1998-11-30
10
Medium Priority
?
553 Views
Last Modified: 2010-05-18
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
Comment
Question by:rsellis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 

Author Comment

by:rsellis
ID: 1969624
Edited text of question
0
 
LVL 1

Expert Comment

by:Carmy
ID: 1969625
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
 
LVL 1

Expert Comment

by:Carmy
ID: 1969626
P.S the form you are talking about is based on the pass-through query as the record source.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rsellis
ID: 1969627
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
 

Expert Comment

by:obsrhayden
ID: 1969628
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
 

Author Comment

by:rsellis
ID: 1969629
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
 

Expert Comment

by:obsrhayden
ID: 1969630
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
 

Author Comment

by:rsellis
ID: 1969631
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
 

Accepted Solution

by:
obsrhayden earned 400 total points
ID: 1969632
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
 

Author Comment

by:rsellis
ID: 1969633
Ok, That's kind of what I anticipated.  Thanks for the help

Rick
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question