Avatar of mjelec
mjelec asked on

Using a sql stored proc as an access combobox rowsource

So, I am trying, as the title suggests, to use a SQL stored proc as the rowsource for a combobox, and I am having a great deal of difficulty.  Here is the latest code I have tried:

Private Sub Form_Load()

    Dim conn As ADODB.Connection
    Set conn = SQLConnection()

    conn.Open

    Me!cboProject.RowSource = "EXEC Ren.up_RenManJobSelect"

    conn.Close
   
End Sub

I have also attempted to use a recordset, but that didn't work either.  Is there something I am missing?
Microsoft SQL ServerMicrosoft Access

Avatar of undefined
Last Comment
Scott McDaniel (EE MVE )

8/22/2022 - Mon
SOLUTION
Scott McDaniel (EE MVE )

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
mjelec

It does, and I had tried something like what you have suggested, but without the conn.execute part.

When I tried what you posted, and added parens around the "EXEC Ren.up_RenManJobSelect", I get an "Object doesn't support this property or method" error on the

Me!cboProject.Recordset = rst

line.

I tried to change it to

Me!cboProject.RowSource = rst

but that didn't work either.
Anthony Perkins

Please post your Stored Procedure.
ASKER
mjelec

ALTER PROC [Ren].[up_RenManJobSelect] @Action INT = 0
AS
    IF @Action = 0
        BEGIN
            SELECT  LTRIM(JMMCU) AS JobNo ,
                    JMDL01 AS JobDesc
            FROM    JDE.dbo.F5600006 J
            WHERE   JMRP07 = 81
                    AND JMSTYL LIKE 'J_'
                    AND ( JMRP25 = ''
                          OR RIGHT(JMMCU, 2) = RIGHT(RTRIM(JMRP25), 2)
                        )
        END
    ELSE
        BEGIN
            SELECT  Job AS UserJob
            FROM    MJE.Master.Users U
                    JOIN ( SELECT   LTRIM(JMMCU) AS JobNo ,
                                    JMDL01 AS JobDesc
                           FROM     JDE.dbo.F5600006 J
                           WHERE    JMRP07 = 81
                                    AND JMSTYL LIKE 'J_'
                                    AND ( JMRP25 = ''
                                          OR RIGHT(JMMCU, 2) = RIGHT(RTRIM(JMRP25),
                                                              2)
                                        )
                         ) J ON U.Job = J.JobNo
            WHERE   U.DomainUserID = SYSTEM_USER      
        END
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Anthony Perkins

It looks like the stored procedure is fine.  Please post your VB code as it stands now, so that we can see why it is failing.
ASKER
mjelec

Private Sub Form_Load()

    Dim conn As ADODB.Connection
    Dim rs As New ADODB.Recordset
    Set conn = SQLConnection()

    conn.Open

    Set rs = conn.Execute("EXEC Ren.up_RenManJobSelect")

    Me!cboProject.Recordset = rs

    conn.Close
   
End Sub

And the error I get is Run-time '438':

Object doesn't support this property or method
SOLUTION
Anthony Perkins

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
mjelec

So, I added Set to the line of code you recommended, and I get a different error now:

Run-time error '7965':

The object you entered is not a valid Recordset property.

Here is the current code:

Private Sub Form_Load()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set conn = SQLConnection()

    conn.Open

    Set rs = conn.Execute("EXEC Ren.up_RenManJobSelect")

    Set Me!cboProject.Recordset = rs

    conn.Close
   
End Sub

The error is occurring on the Set Me!cboProject.Recordset = rs line.

I am not certain if a fire hose is what is causing the problem here, but I am open to any advice.  I can say that the data going into the combobox will be static, so the concept of a fire hose seems like it would work.  I have used what I am doing here, or something similar, to populate access forms.  At this point, I am just looking for a good way to get data into a combobox, whether it is similar to this or not.  We are trying to avoid putting the SQL code directly into the VB for Access, to avoid SQL injections and maintain security.  Beyond that, I am open to suggestions.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
mjelec

Yes, that works.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
mjelec

I did try this at one point, but I was uncertain how to add multiple columns using that method, Scott.  I need both JobNo and JobDesc for each record.
Dale Fye

No points please:

          YourCombo.AddItem rs("JobNo") & ";" & rs("JobDesc")

Make sure your combo is setup for 2 columns.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
mjelec

Okay, I actually found something that seems to be working, but I would love to get comments and feedback on it, let me know if I am missing anything.

Private Sub Form_Load()

    Dim qdf As DAO.QueryDef
   
    Set qdf = CurrentDb.QueryDefs("Projects")
    qdf.Connect = "ODBC;DRIVER={sql server};DATABASE=MyDb;SERVER=MyServer;Trusted_Connection=Yes;"
    qdf.SQL = "EXEC Ren.up_RenManJobSelect"

    Me.cboProject.RowSource = "Projects"

End Sub
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.