Results 1 to 11 of 11

Move Backend databases to SharePoint? Y/N? Need advice

  1. #1
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    49

    Move Backend databases to SharePoint? Y/N? Need advice

    I have about a dozen Access (2013/2016) split databases. Each of them use a significate amount of VBA code to run the data entry forms, reports, and process the data (no macros) in the local frontend Access databases. The backends are stored on a server to allow access for up to 50 different users. I am starting to get some pressure from management to move the backend databases to SharePoint. I have ZERO experience with SharePoint. Is this going to be a painful process or is it just a matter of exporting the tables from the backend to individual SharePoint lists then reconnecting the linked tables in the frontend files?


    potential issues:


    • one of the backend databases uses an attachment field (I know!! tried to fight it but management insisted at that point in time)
    • another frontend stores attachments external to the database but at the location of the database with the mapping stored in a table
    • I have code developed to relink to the backend files via file selection - allows users to connect to different versions of the backend as needed
    • as Admin, I do have to correct data directly in the backends from time to time which is not an issue in Access tables - not sure about doing this in SharePoint lists??
    • some of the frontends are linked to tables in multiple backends


    I would prefer that they just stay with the file server but that may be out of my control. if it is a bad idea or major issue to move the backends to SharePoint, I need some reasons why so I can argue the point. At this point I don't know if it is a PROBLEM or not as I said, I have never used SharePoint for anything.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,103
    I'm no SP expert either, but have come across forums and such that indicate SP and Access are not the marriage made in Heaven as was once touted. Kind of like ADO was going to replace DAO (development of ADO has ceased) and replication was the cat's meow (or some other part). Now you can't even open a replicated db using 2016. Would not surprise me if integrating SP with Access falls by the wayside as well. If it were me, I'd be looking for anything I could dig up about SP and Access. Remember, you will find info that says it's the next best thing - just like those who say multi-value fields and storing objects in db's is great. If you don't win the argument, just make your opinions known by email while professing to support the decision. Then politely push any fallout uphill where it will belong.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    (humor about talking to yourself apparently was too much for some)

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,376
    I have zero experience with SP, but if the organization is big enough to have SP it may also have SQL Server. You might explore whether that would be an acceptable alternative. Like Micron it seems to me that SP has fallen out of favor at MS after that initial push (as a back end for Access). Either way, you'll find a lot more of us using SQL Server as a back end that can help with the transition.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    49
    thanks! I am going to push back, i think the "attachments" requirement is the big issue that we would have to get around. Hopefully I can convince the right people to not move to SP.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,345
    Don't know if this will help you, but I found this article... https://venturebeat.com/2017/03/31/m...in-april-2018/
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,103
    AFAIK, SQL Server Express is still free and probably robust enough to deal with your attachments if storing them is a must. Alternatively, those who stick with Access typically store files in network folders and the path in a db table, not the file itself. The former can provide more security for the files though, as network files are exposed according to the db user rights in an Access setup. For example, if they can delete from the network folder they can render the stored path useless.

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,376
    Express is still free, I'm helping a client test upsizing to it now.

    I'm not sure using attachment data type is any more secure; a user could probably delete the Access back end file just as easily as the files from a folder. Those pesky users find ways to do anything, usually by accident. Frankly either method should have a robust backup procedure in place.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    49
    Micron, i have done it both ways (internal and external) for the reasons you mentioned. I really dont like the internal as i have to advise users to limit what they attach so the db doesnt bloat too much.

    the big issue is having automated backups of the backends which is not happening currently. I have code that makes a copy when specific users open the frontend databases but it is saving to their local machines and IT is not happy with that solution. Think I will recommend that the automated backup put the file on the SharePoint site. then we will have IT backups but still use the Access DB as the backend on a shared computer. thoughts??? (best answer would be a IT managed file server but don't think I can get the funding for it.)

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,103
    You have an IT department but no file server??
    If I misunderstood, then why aren't the be backups on the server?

  10. #10
    Sck is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2010
    Posts
    49
    using a shared desktop.

  11. #11
    AdrianG001 is offline Novice
    Windows 10 Access 2007
    Join Date
    Mar 2018
    Posts
    9
    Moving the Access Web App in SharePoint will be painful because it requires lot of re-designing to make it compatible with sharepoint. If you are willing to do that then the recommendation would be PowerApps rather Access because Access Web App is deprecated and you may not get the required support.

    Regards,
    Adrian

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

Similar Threads

  1. Frontend to Backend Databases
    By raychow22 in forum Access
    Replies: 6
    Last Post: 08-11-2017, 04:47 PM
  2. Frontend Backend Databases Forms to Tables
    By raychow22 in forum Access
    Replies: 2
    Last Post: 07-08-2017, 03:49 AM
  3. 2 backend databases
    By jwallace in forum Access
    Replies: 2
    Last Post: 12-10-2015, 02:48 PM
  4. Move Data to Sharepoint
    By raffi in forum SharePoint
    Replies: 3
    Last Post: 02-28-2015, 12:18 PM
  5. Move to SharePoint Site issues
    By Mercator in forum SharePoint
    Replies: 4
    Last Post: 12-27-2012, 05:52 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
  •  
Tech Forums: Microsoft Office Forums