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

x
?
Solved

Updating data using a sql stored procedure in an Access Project (ADP) form

Posted on 2011-03-17
4
Medium Priority
?
419 Views
Last Modified: 2012-05-11
I have an adp and I am able to use a pararameterized stored procedure to display data to a user on a form. The form has a subform which is used to add records using comboboxes. When making a selection from the combobox, the user gets an error saying they have insufficcent permissions on the table that the stored procedure (for the main form) is pulling data from. I thought that if the user had execute permissions on the sproc, they didn't need permissions directly on the table. Am I missing something or is this by design. Thanks for any help.
0
Comment
Question by:AkAlan
[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
  • 2
  • 2
4 Comments
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 2000 total points
ID: 35169257
Hi,

I'm not sure if this method will work successfully with a bound form. You're right, the only thing you need on the server is the execution right, example is here:

Using Stored Procedures to Manage SQL Server Security

The problem could be that Access wants to read the row after writing to synchronize the form so you must provide the command to read the current row in the form settings (normally it looks like this:

SELECT * FROM table WHERE id=?

but this will not work in your case because of the missing rights to read the table.)

As Access also tries to fetch the default values by default (you can switch that off in the form setting) this causes another read. Maybe there are more things in the background.

I would recommend to use SQL Server profiler to look what exactly happens in the background and which command causes the error, that's easy with this fantastic tool. Not anything what happens in the background can be seen in Access, it's a lot...
When you found out the problematic command you can set additional permissions to the user.

You will have to go the hard way with this method, you know...;-)

Cheers,

Christian
0
 
LVL 6

Author Comment

by:AkAlan
ID: 35169455
Ok, that all makes sense to me. I just thought it would have been a little more obvious. I gave the users rights to the table for now, like you said it's a hard way to go. I was really hoping for the silver bullet answer on this but it looks like I'm stuck with either messing with queries and passing a WhereClause, yuk, or get creative with how I populate the form. I have an asp.net web app pulling the same data so I really want to use sprocs.
I have never used SQL Server profiler, sounds like I need to bite the bullet and learn how. Thanks for the info, at least I feel like it's not me, it's MS.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 35169921
Hi,

it's really no big thing to learn. Start a new trace and then you get a dialog where you can define what you want to get. Without any filter setting it simply lists all commands from any client in any database of the server (you must of course have admin rights to use it). This is a heavy lot of information so simply filter it to your database and your computer name, maybe application name, too. Then you can see what happens on YOUR computer with the wanted database. It's very comfortable and easy, my opinion. There's of course a lot to learn if you really want to learn the complete profiler with all it's possibilities, but you don't need it for this purpose. For example, it can take a snapshot and analyze if the speed of a SQL Server query is optimum and so on, but that are other stories. Really helpful, I strongly recommend to get in touch with it.

I think if you want to create everything with SPs in Access you are forced to use unbound forms. That means you'll lost any comfort in programming and you must do anything manually, really hard, but possible. I personally wouldn't do it although you're right, it is a better security to use only SPs (especially if you already have done the work before because of your web application). What you can do is load the data of the SP into an ADO recordset and then assign it to the form, but of course this means unbound form. To do this you must create an XML file, I never have done it, much to complicate in my opinion. Too many possibilities to create errors, in most cases you will not have the time to program it on this way.

Maybe a better alternative if you want to keep the security is to use a .NET language. But it's also not easy to learn because of the complex new object structure to work with tables and other database objects. On the other hand you get a lot more possibilities.

Cheers,

Christian
0
 
LVL 6

Author Comment

by:AkAlan
ID: 35172309
Christian, Thanks so much for that last comment. I will give the profiler a shot. Most of the apps I develope are relatively small and for the company I work for so having to tune for performance has never been an issue. So while I understood the concept of query optimization, if the query returned the results in less than a second or two, which is always the case, I was fine with it. I am disappointed that I will have to do a work around, you would think MS would incorporate the sproc functionality to Access a little better. I am going to start working towards a solution to replace all my legacy and current Access front ends with a WPF and/or MVC type solution.

When you say that you need an XML file to create an ADO recordset, I'm not too sure what you mean. I have used that as a solution and will post some example code of how I use it to get around my original issue. Thanks again, very helpful stuff.

I use this to populate the form:

   
   
 
Private Sub Form_Load()

Me.cboOwner.RowSource = "EXEC spSelectHR_ActiveEoaEmployees"
    If Me.OpenArgs = "Edit" Then
        Me.txtDescription = Form_frmTrainingRequirements.Description
        Me.cboFrequency.Value = Form_frmTrainingRequirements.Frequency
        Me.txtInterval = Form_frmTrainingRequirements.Interval
        Me.txtLastReviewDate = Form_frmTrainingRequirements.LastReviewDate
        Me.chkActive = Form_frmTrainingRequirements.Active
        Me.chkHazwoper = Form_frmTrainingRequirements.Hazwoper
        If Not IsNull(Form_frmTrainingRequirements.txtOwnerID) Then
        Dim OwnerID As String
        OwnerID = Form_frmTrainingRequirements.txtOwnerID
        Me.cboOwner.Value = OwnerID
        End If
    Else
        Me.chkActive = 1
        Me.txtLastReviewDate = Date
    End If
End Sub

Open in new window

   
   
And this to do the update (this is on the update button click event), yes it takes a bit longer but it works for my needs.

Dim cmd As New ADODB.Command
        If Me.OpenArgs = "Edit" Then
            With cmd
                .ActiveConnection = CurrentProject.Connection
                .CommandType = adCmdStoredProc
                .CommandText = "spUpdateHR_TrainingRequirements"
                .Parameters.Refresh
                .Parameters("@TrainingRequirementID") = Form_frmTrainingRequirements.TrainingRequirementID
                .Parameters("@Description") = Me.txtDescription
                .Parameters("@Interval") = Me.txtInterval
                .Parameters("@Frequency") = Me.cboFrequency
                .Parameters("@Hazwoper") = Me.chkHazwoper
                .Parameters("@Active") = Me.chkActive
                .Parameters("@OwnerID") = Me.cboOwner.Value
                .Parameters("@LastReviewDate") = IIf(IsNull(Me.txtLastReviewDate), Null, Me.txtLastReviewDate)
                .Execute
            End With 

Open in new window

0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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 …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

618 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