Solved

Using and updating SQL data in Access forms

Posted on 2014-03-21
5
360 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
  • 3
  • 2
5 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 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 65

Assisted Solution

by:Jim Horn
Jim Horn earned 500 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 65

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

912 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now