Solved

SQL Server Stored Procedure as Recordsource

Posted on 1998-11-30
10
547 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
 

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now