Using and updating SQL data in Access forms
Posted on 2014-03-21
Okay, another SQL in Access question.
I am bringing in a SQL stored proc as a recordset that I then want to be able to edit. I am not saying it has to update the SQL directly, I have no problem calling a second proc on update of the fields, but I currently cannot change the fields, and I get a beep when I try.
Here is the code:
Dim conn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim frm As Form
Dim ProjectID As String
ProjectID = Forms!frmMain.cboProject
Set conn = SQLConnection()
rs.CursorLocation = adOpenKeyset
strSQL = "EXEC Ren.up_JobCrewSelect @BusinessUnit='" & ProjectID & "'"
rs.Open strSQL, conn
If Not (rs.BOF And rs.EOF) Then
Set Me.Recordset = rs
Set rs = Nothing
I thought it might be the parameters on the recordset.open, and I added adOpenStatic, adLockOptimistic and one point, but I cannot seem to find the combination. I appreciate any help you can offer.
Also, if my whole plan here is not the best direction, please feel free to say so and recommend a better path. Thanks!