Solved

Using and updating SQL data in Access forms

Posted on 2014-03-21
5
354 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
Comment Utility
>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
Comment Utility
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
Comment Utility
>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
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

743 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

8 Experts available now in Live!

Get 1:1 Help Now