Solved

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

Posted on 2011-03-17
4
362 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
  • 2
  • 2
4 Comments
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 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

10 Experts available now in Live!

Get 1:1 Help Now