Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server Stored Procedure as Recordsource

Posted on 1998-11-30
10
Medium Priority
?
555 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
  • 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
Industry Leaders: 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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

877 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