mjelec
asked on
Using and updating SQL data in Access forms
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()
conn.Open
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
Else
End If
rs.Close
Set rs = Nothing
conn.Close
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!
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()
conn.Open
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
Else
End If
rs.Close
Set rs = Nothing
conn.Close
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
As a follow up, the passed T-SQL statement would just be moving what code is in the stored proc into the VB in Access correct? I have both a concern adn a question about that. First, I am trying to keep SQL code out of Access since I have users who dig into Access and I am trying to keep some security around our SQL data, hence my plan for the stored proc. Second, if I were to simply replace the proc with the T-SQL, would that allow updating of the form?
I'll fully respond to the above questions later today, as I have to take off to meet with a client.
Thanks for the grade.
Thanks for the grade.
ASKER
Would it make any sense to use the stored proc to load a local table, do the work there and update back to SQL, or am I insane? I am just trying to minimize the amount of data and calculating in Access to keep things fast and secure. I have noticed that sometimes it seems like iffy Access programming in Access with linked SQL tables can lead to Async Network IO issues, and I want to minimize that if at all possible.