Best Practices for migrating an Access Database to SharePoint 2013 Online: Part Two

Posted on 2014-08-15
1 Ratings
Last Modified: 2014-11-12
Have a legacy Access database, small but well formed.  The database manages information for a project with a short finite life cycle (oil & gas pipeline projects, one to three years and done.)

Want to use SharePoint 2013 Online (we're enterprise version) particularly for data entry.  Best outcome is that we use a split design, where access tables are in SharePoint.

Best way to form the question is:  What is best practice direction to tackle the task at hand of migrating the database and developing rich SharePoint forms to do data entry and manage data views?
Question by:VirtualKansas
    LVL 84
    What version of Access are you using? There are different options, depending on the version of Access.

    For example, 2010 could be "published" to Sharepoint, which meant everything was run from the SP machine. Forms and Reports appeared in the browser window. You could also create "hybrids", where some objects were on SP, and other were on the web portion.

    2013 introduced Web Apps, which are essentially Access databases that run from a server (re: Office 365). They are much, much different from the 2010 version. If your needs are simple (very basic CRUD operations) then you could very likely do that.

    Or you could use a standard desktop Access database, and point it to a SQL Server - either Azure (cloud) or one on your local network. If all your users are local, then this is often the best solution.

    Author Comment

    Hi Scott and thank you.  Here's the thing, I went all in on this project with a commitment to 100% Microsoft products.  We're a small organization managing critical functions of nine to ten figure projects, with teams and mobile users spread across large geographies.

     I started with SharePoint 2010 foundation on prem. using VPN's and a small, legacy Accesss database that had been evolved over twelve years (used as a template for each new project) and several third party tools.

    The 2010 system was good but got very excited about O365 for the team and moved us all in on Enterprise including email migration and Office 2013 (we love Lync.)

    But now am knee deep into developing the database to interact with SharePoint and getting all kinds of conflicting feedback on how well/not well Access web apps work and then find that InfoPath, which I just got a handle on for creating great web forms for SharePoint, is now end of life in 2013 release!

    I'm a one man show.  Simply thought I could rev. us up to SP2013/O365 Enterprise and let Microsoft handle the back end overhead and then migrate Access 2013 split database tables to SharePoint, create data entry forms in subsequent SharePoint lists and then use the Access 2013 for running reports, including original forms.

    Apologies for all the backstory, but this is lay of the land (we're buying land rights, so literally lay of the land.). Just need to know if I'm heading for a serious set back in choosing to the route of SharePoint for data entry using InfoPath forms and Access 2013 FE for running reports?

    If so, what is best practice based on all this investment that will achieve the goal, reliably and while leaving the door open for all the future Microsoft will offer us?
    LVL 84
    Just need to know if I'm heading for a serious set back in choosing to the route of SharePoint for data entry using InfoPath forms and Access 2013 FE for running reports?
    My take would be yes, you are heading for troubles. As you mention, InfoPath is no longer in active development (although MSFT has said they'll "support" it until around 2020), and that always scares me, since you never know what they mean by "support". In my experience, it means they'll leave it at 'point in time' - meaning they'll guarantee you it'll work on a platform that was valid at the time they discontinued support (January of this year). Newer versions of OS, Server, etc etc could break your Infopath, and MSFT would have no obligation to assist in the fix (although they may).

    Sounds like you need something web-based, since you're working with a scattered userbase. A 2013 Web App would work, if your needs are somewhat on the simple side (basic data entry and reporting). That seems to be the way Access is moving (i.e. web-based), so I'd expect there to be improvements and changes on a regular basis, at least for the foreseeable future. 2013 Web Apps are big improvement from the 2010 offering, but IMO they're still not ready for prime time - unless, again, your needs are somewhat simple.

    My take on the whole thing is this: If you need a robust, easily usable web-based application, then bite the bullet and learn a true web language like ASP.NET or PHP, or one of the many other flavors. You can still leverage your investment in the various MSFT technologies, but you won't run into roadblocks like this in the future.

    Author Comment

    Well, I'm hating life right now but I'll not shoot the messenger.  May I add a little more to see if i can hold out hope on the current path?

    Assume 90% of users (<100 per project/SharePoint site), will be simply reading data posted in SharePoint lists and less than 10% will entering/maintaining data.

    For now (and I have weeks to days to get this working prototype in front of client), would I simply create lists in SharePoint that essentially represent the data in a form same as Access tables?  ...and allow users to maintain the data in SharePoint lists, using Access to query the SharePoint lists to run traditional reports (there are really only a few)?

    InfoPath deprecation aside; this method will get us through the next twelve to thirty six months of projects, buying time for next level of development.

    Understand if I should open a new question, it seems that my question is now "Is it OK to build and users maintain data in SharePoint lists that Access 2013 database uses as a source for queries?"  and another "Is InfoPath a good tool for creating forms for routine data entry of SharePoint lists that will be used as source data for Access 2013 queries?"

    This is way more risk than I thought I was taking, really appreciate you and EE community, rather know now than later...

    Author Comment

    Well, I've soldiered on and have built InfoPath library to SharePoint site and frankly am liking the looks of things.  Still have serious fear & loathing about all the warnings am getting from community about the method.

    Doesn't seem like the original question is relevant any longer, so we'll drop this thread with my thanks with one last request:

    In earlier response "you are heading for troubles."  Set aside InfoPath and just applying SharePoint lists data as data collection form that is a source for Access.  What kind of troubles would we be looking at?
    LVL 33

    Accepted Solution

    Given Microsoft's track record with Access/web tools, it isn't going to make much difference what you do, you won't have an upgrade path.  The fact that you are starting with deprecated tools just shortens the lifespan of the app and ensures that you will not be able to upgrade it since we already know there is no conversion from InfoPath to any other tool.  MS started with DAP's and then abandoned them.  It then went to web forms using SharePoint lists and abandoned them.  The current webification of Access continues to depend on SharePoint but has abandoned SharePoint Lists in favor of SQL Azure (a much sounder choice IMO).  The current incarnation, although still not ready for prime time is almost good enough, especially if you are planning on building a hybrid app so you can retain client/server features.

    You've never said why you have to switch to a web interface.  What has changed in your business that is forcing you to abandon client/server?

    I tried InfoPath when it first came out and was very disappointed in its capabilities.  I don't know if it has gotten better since I never looked again.  From what I know of A2013 Web forms, they seem to be at least as good an option as InfoPath.  We don't know yet whether MS will just abandon them as they have all previous attempts but at least they aren't deprecated yet so there is hope for a future.  And personally, I believe SQL Azure is a far better option than SharePoint lists so for that reason alone, I would go with Access web apps.  If it turns out that there is something you can't do that needs to be done, you can fall back to ASP to solve the problem.  I'm not sure how you would hook it in but hopefully there is a way.

    Author Closing Comment

    Too long a story for this forum.  Suffice to say, we're migrating from stone age to bleeding edge as quick as we can.  Business is booming and we're spread across the continent.  Cloud based data is best way to manage the growth.  InfoPath, Access Web Apps. & Azure it has to be MSFT as our business depends on MS Office products.

    So far, the feedback from the community has been pretty Spartan.  Yours is the most thorough response to questions posed on this topic and we thank you for that.  Hate to be penny wise & pound foolish, but need progress now and can't fear two to three to five years from now if tools will change/disappear.  The good news for me is project life cycles are dropping from years to months.  Each data set to date is project specific, there is no one store for all projects, forever.  As long as the rug isn't pulled out during a project, we believe we'll be OK.

    Have to say I've been wrenching on InfoPath and O365/SharePoint with good success.  Only found one bug and one (not so minor) limitation in Rich Text fields and that is a SharePoint limitation more than InfoPath.  I did kick the tires on an Access Web app and frankly InfoPath XML was easier to get my head around.  I hope we're not hurtling down a blind alley, but for me the dead end is not life of the tools as it is limitations in managing the information with the tools of the day.  So far, so good...
    LVL 33

    Expert Comment

    It sounds like you're committed to InfoPath.  I hate to see you truck on down this road since it is a known dead end but you have a few years before the final plug will be pulled and that may be enough so best wishes.

    My personal choice is to keep the Access FE for the richness and flexibility of client/server and use Citrix so that people across the country can share the same app.  You can always switch to SQL Server for the BE if database size or concurrent users get to be too much for ACE.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Before you can digitally sign infopath forms, you must have a digital certificate. Microsoft Certificate Services will need to be enabled on a Windows Server 2008 to facilitate the creation and verification of the digital certifciates on the web ser…
    Monitoring systems evolution, cloud technology benefits and cloud cost calculators business utility.
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    761 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

    13 Experts available now in Live!

    Get 1:1 Help Now