Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634

    Unanswered: back end on sharepoint

    I need to develop a new db that stores the data on SharePoint and uses an Access front end. It looks like I can either use SharePoint lists or upload related tables on SharePoint. I'm completely new to SharePoint and was wondering if anyone has some experience with this type of setup or can recommend any good books or resources. I haven't found a comparison between the list and table options. Both seem simple enough to get set up, but I have some concerns about controlling access to the data on the SharePoint site. Any thoughts or suggestions would be most welcome.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I don't want you to feel that we're ignoring you, but I suspect that the reason over 60 members have looked at your post, but not responded, is that the vast majority of the experienced developers who help here simply do not use SharePoint. The problem is that most serious Access development is done with VBA code, and you cannot use code with web-based Forms. Those who do work in web-based apps usually use an Access Front End with the web part being handled with another language, such as ASP.

    Perhaps someone who's been experimenting with SharePoint will come along and help, the first of the week (weekends tend to be slow, here) but I'd continue searching online, using a search string such as 'moving Access to SharePoint.'

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Thanks for the input Linq. I knew it was a long shot, but I thought I'd see if anyone else had done this. The company I'm with is 'globalizing' and using a network shared drive for an access back end is horribly slow for users in other countries. Since we're also 'globalizing' the process that the db will be supporting, we need to be able to make changes quickly (both front end and back end) so we don't want to use SQL server yet. One point of clarification - this will not use web forms, the front end will be an accde on the users desktops - SharePoint will only house the back end data. One the process and application are solidified and the users are content, the back end will probably move to SQL server and I'd like to minimize the amount of recoding.

  4. #4
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    Oddly enough, I have had some experience with tables linked between Access and SharePoint. I do not have any reference material suggestions, but I could probably help you out with any specific questions you may have.

    It really just sounds like you would have a series of lists in SharePoint that would be linked to your Access application.

    Is one of your concerns regarding a user opening table information where they may not have the relevant permissions in SharePoint?

  5. #5
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Thanks for the response, Brian.

    Table security is at the top of the concerns list. SharePoint authenticates via AD and the users will have to have read/write access. I only want them to have access to the data through the front end accde. I'm open to using SharePoint lists or setting up the site with Access Services and creating tables. I've found plenty of How-Tos for the set up, but the only suggestion I've found to secure the lists or tables is to not give the site URL to the users - and I'm not really comfortable with that.

  6. #6
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    Do the users operate in SharePoint for anything else?

    Where you going to set up some sort of user restricted interface in the Access front end?

    I have not tried it, but you could always test creating a linked table in Access and have a user who does not have permissions to the SharePoint list try to enter a record through the Access end of it.

  7. #7
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    There are many SharePoint sites throughout the company. Each site has it's own unique URL and set of users. I'd be surprised if any of the users are not currently using SharePoint for something. I will be setting up a SharePoint site specifically to house the back end data for this application.

    Yes, the user front end will be in Access - all locked down and compiled as an accde. There will not be a front end on SharePoint - no web forms or reports.

    SharePoint is set up to authenticate using Active Directory, so the users' ID has to have permissions on the SharePoint site. If a user tries to access a SharePoint site that they don't have permissions on, they receive an access denied error.

    I just noticed that I've neglected to mention that we're using Office 2010 and SharePoint 2010.

  8. #8
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    I think we agree on the initial setup. Unfortunately, the nature of SharePoint would allow those users to access the list if they found the URL.

    If the intent is to restrict updates, there are a couple of methods. The first involves stepping out the process with a local redundant table to push updates to the linked table. Any changes made directly in SharePoint would be overwritten by the local Access table. Not exactly the ideal solution.

    You could also create a field in the list to designate the information source (I was initially thinking a choice, but you could probably do it with a flag). Information entered within SharePoint would have a default value. Records entered through Access would have that value set differently. You could track any issues through a view in SharePoint.

    If an available resource to you is SharePoint Designer, there may be some additional options involving making changes to the AllItems page.

  9. #9
    Join Date
    Mar 2014
    Posts
    4

    Why split the database?

    Why split the front and back end? Just put the full database on SharePoint and set it up for multiple users. Only real benefit you get from splitting the front to back ends is that it allows you to do work on the front end while your team is working on the backend. Access is really stretched when you put it on the Web... like on SharePoint. Its never the best solution, though it does seem to work ok.

  10. #10
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Sorry for the delayed response....was out of town.

    It looks like we're going with a SQL Server back end rather than SharePoint.

    Drand11, a SharePoint interface doesn't offer all the functionality we need. The biggest issue being the users' ability to work off-line then feed data back - there will be times when they won't have access to the SharePoint site. There are also limitations with reporting and implementing row and field level security (some users can only see some fields of records from their region only, others need to see all fields for records from all regions). The only way I found to even come close to handling the row and field level security involved breaking tables apart for field level security and creating multiple tables to mimic row level security, that's a bit more cumbersome than I want to deal with and would add quite a bit of recoding when this outgrows SharePoint.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •