Link to home
Start Free TrialLog in
Avatar of VirtualKansas
VirtualKansas

asked on

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

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?
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

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.
Avatar of VirtualKansas
VirtualKansas

ASKER

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?
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.
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...
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?
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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...
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.