• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

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!
0
mjelec
Asked:
mjelec
  • 3
  • 2
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>SQL stored proc as a recordset that I then want to be able to edit.
Stored Procs connected to Access / anything are read-only, so you can't change the data.

>please feel free to say so and recommend a better path.
If you need to change the data, it has to be a table, view, or SQL statement.
0
 
mjelecAuthor Commented:
Thanks!

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.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Would it make any sense to use the stored proc to load a local table, do the work there and update back to SQL

Depends.  This could be a lot of work on the SQL side to take the revised table, figure out what the changes are, and implement those changes in SQL.  

If we're talking not a lot of logic here, meaning take the SP and translate it to either a single SQL view, or Access VBA parses together a SQL statement using '@BusinessUnit='" & ProjectID & "'"', then it would be updatable, and a lot less work on your part.

>it seems like iffy Access programming in Access with linked SQL tables can lead to Async Network IO issues,

Linked SQL tables would take a lot of network traffic to load, so if you're only going up against a subset of the table data, then a view or passed T-SQL statement is a far better approach.

>or am I insane?
I haven't observed you enough to make a determination, but keep talking and maybe I'll know in a couple of minutes.
0
 
mjelecAuthor Commented:
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?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now