Solved

SQL Server Stored Procedure as Recordsource

Posted on 1998-11-30
10
551 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
Technology Partners: 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 100 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

710 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