Results 1 to 11 of 11
  1. #1
    shinydiamond is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2016
    Posts
    31

    Multiple Users Accessing ONLY their data using Access Web App?

    Hi All,
    I've been tasked with building a web based sales form that my company's sales force can use to enter their lead and prospect information.
    I'm fairly new with Access having designed a couple of basic stand alone databases using Access 2013 and I'm familiar with my limited security options using the desktop client. What I need to understand is if I would be able to build something using the Access Web app that would allow each sales person to login and enter/modify and view only their prospect information. Is this possible with the Access Web app and if so, what's the best way to approach it? Would each salesperson have their own table or could they all use the same table somehow?

    Apologies if this has been covered in another post, but I was unable to find anything.

    Thanks for any help.

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    Yes it is possible. On sever levels. You could track users in the database and provide a login functionality. You could also authenticate with other authentication services like windows or maybe even some OAuth providers.

    I user just a table with users and a table with control groups. the users belong to control groups. when a form is opened it checks to see if a user is in that controlgroup and handles accordingly.

  3. #3
    shinydiamond is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2016
    Posts
    31
    Thank you for the reply. I'm glad to hear it can be done with Access and we don't have to buy another tool.
    What is best practice for database design here...would each user have their own table with only their records or can there be one master table with a column that corresponds to their login and somehow they could only view and edit their records? We have about 30 reps so creating a separate table for each rep would be time consuming.

  4. #4
    shinydiamond is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2016
    Posts
    31

    Still looking for advice

    Hi All,
    I'm still looking for some advice on the best way to approach designing this database so multiple users can only access and modify their records. Should I be using one table to store all records or one table per user? Thanks for any advice.

  5. #5
    nhorton79 is offline Advanced Beginner
    Windows 8 Access 2007
    Join Date
    Apr 2015
    Posts
    34
    One table for all leads (tblLead) then have a separate table (tblStaff) that holds the various staff members.
    The tblLead would have a field in it that is a foreign key linking that table to the tblStaff primary key.
    That way each lead is assigned to a staff member and when they log in using credentials stored in the tblStaff table you can set blocks so that they can only see leads relation to themselves.

    There would likely need to be a lot more tables/fields etc but that is the basics of linking those two tables.

  6. #6
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Look at this tutorial and determine if it can be adapted to your situation:

    http://www.mrexcel.com/forum/microso...ns-system.html

  7. #7
    shinydiamond is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2016
    Posts
    31
    Awesome - thank you both for your responses. I can now get busy building this thing next week. The sample VBA code from the link that alansiman provided will definitely save me some time. Thanks again!

  8. #8
    shinydiamond is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2016
    Posts
    31
    alansidman, I don't think I can use the code you've referenced on my webapp database. There doesn't seem to be a way to enter VBA - only canned set of macros deemed safe to execute from a web browser. Am I missing something here?

  9. #9
    shinydiamond is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2016
    Posts
    31
    Help! I've been scouring the internet trying to find examples of limiting record editing in a table to the individual user's records in an Access web app and have only found information saying it's not possible.

    From one site:

    • Security setup is basic and somewhat limited. First, it isn't possible for users to own records. Second, there isn't a way to separate who can add a record vs. who can edit a record. Third, there is no way to apply edit vs readonly permissions to specific tables in an app. If you can edit records in one table, you can edit records in all the tables within the app.

    http://go.limeleap.com/community/bid...-2013-Web-Apps

    Does anyone have information saying that this is possible in the access web app?

  10. #10
    shinydiamond is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2016
    Posts
    31
    Hi, I'm trying to renew interest in this thread in hopes of someone explaining to me how to enter code that will allow a user of the Access Web App to see only their records, based on their login as mentioned above by Perceptus, nhorton or alansidman. The Access Web app only seems to allow the use of built in macros and not VBA. I'm running out of time to get a database up and running and really hope that I don't have to reset with a different web based solution. Thanks all.

  11. #11
    shinydiamond is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Mar 2016
    Posts
    31
    Good news for all of you struggling to help me - I was able to find an article which has screen shots that walk you through how to use the built in macros to set up record level security. Here's the link:
    http://www.johnluangco.com/2015/08/a...ser-login.html

    I tried it out and it worked perfectly! It just takes some time to get used to selecting the code (functionality) you need instead of typing it in.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 09-19-2014, 03:58 PM
  2. Two different users accessing Access database
    By ultimateguy in forum Access
    Replies: 3
    Last Post: 06-25-2014, 03:43 PM
  3. Replies: 5
    Last Post: 08-20-2013, 09:37 AM
  4. Replies: 2
    Last Post: 04-16-2012, 10:24 AM
  5. Multiple users accessing same database
    By 4ACE in forum Access
    Replies: 0
    Last Post: 02-28-2008, 03:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums