Improve company productivity with a Business Account.Sign Up


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

Posted on 2011-03-17
Medium Priority
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.
Question by:AkAlan
  • 2
  • 2
LVL 24

Accepted Solution

Bitsqueezer earned 2000 total points
ID: 35169257

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...;-)



Author Comment

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 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.
LVL 24

Expert Comment

ID: 35169921

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.



Author Comment

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
        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("@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)
            End With 

Open in new window


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

595 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