StrikeEagleII
Technical User
I've been tinkering around with an access database I have the task being to change the backend to sql server. I programmed a simple stored procedure that takes one parameter and runs a select query using that parameter in the where clause.
Back in my front end, I want the results of the stored procedure to be the rowsource of a combo box.
What's the best way to add a parameter for the sp? I've got two ways working:
'this method requires rowsource of combo box to be set to a saved pass through query which is then updated:
This is using ADODB:
Both of these seem long and clumsy to set the row source of 1 control--is there not a better way or do would I need to do this for all my combo and list boxes on the form?
Back in my front end, I want the results of the stored procedure to be the rowsource of a combo box.
What's the best way to add a parameter for the sp? I've got two ways working:
'this method requires rowsource of combo box to be set to a saved pass through query which is then updated:
Code:
Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qry = db.QueryDefs("pt_APByModel")
strSQL = "Exec usp_APByModel '" & ModelID & "'"
strSQL = Replace(strSQL, "''", "") 'to account for Nulls
qry.SQL = strSQL
qry.Close
Set qry = Nothing: Set db = Nothing
Me.cboEffectivity.Requery
This is using ADODB:
Code:
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim strCNN As String
strCNN = "Provider=sqloledb;" & _
"Data Source=JASON-DESK\SQLEXPRESS;" & _
"Initial Catalog=TOLADV;" & _
"Integrated Security=SSPI;"
cnn.Open strCNN
cnn.CursorLocation = adUseClient
With cmd
.ActiveConnection = cnn
.CommandType = adCmdStoredProc
.CommandText = "usp_APByModel"
.NamedParameters = True
.Parameters("@ModelID") = ModelID
Set rs = .Execute
End With
Set Me.cboEffectivity.Recordset = rs
cnn.Close
Both of these seem long and clumsy to set the row source of 1 control--is there not a better way or do would I need to do this for all my combo and list boxes on the form?