Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using and updating SQL data in Access forms

Posted on 2014-03-21
5
Medium Priority
?
378 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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

636 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