?
Solved

Using and updating SQL data in Access forms

Posted on 2014-03-21
5
Medium Priority
?
374 Views
Last Modified: 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()
    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
Comment
Question by:mjelec
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 39945421
>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
 

Author Comment

by:mjelec
ID: 39945464
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
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 2000 total points
ID: 39945491
>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
 

Author Comment

by:mjelec
ID: 39945523
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39945547
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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question