Best Practices for migrating an Access Database to SharePoint 2013 Online

Posted on 2014-08-15
Last Modified: 2014-11-12
This will spawn multi-part series of questions, but let's respect the rules and start with one...

We're at Enterprise 2013 online.  We have a project database that is well formed.  Beyond simply migrating the tables up to SharePoint and using the traditional front end (split design), we'll want users to do data entry using InfoPath forms.

So, before I get ahead of myself the question is:  Can you guide us to a best practices method for this migration that allows users to do "data entry" in either traditional access front end and/or InfoPath forms?

Assumes that migrating the current tables up to SharePoint is way to go...
Question by:VirtualKansas
    LVL 33

    Expert Comment

    I wouldn't migrate the data to SharePoint lists.  SharePoint lists are not relational so except for the simplest of tasks, I would not use them, period.  A better path would be  to migrate the BE to Azure.  That is the equivalent of SQL Server and is much more robust.  The only problem you'll run into here is if you made the mistake of using one of the new data types that was added to Access in A2007.  Those have never been recommended by experts since they are crutches and not supported by SQL Server so there was no available upsizing path.  To convert to Azure, you will need to get rid of any of these data types.

    Assuming you have the latest version of SharePoint, you can start on the path to making your existing Access app an Access/Web hybrid.  Once you have migrated the BE to Azure and tweaked the FE so that it works responsibly in a client/server environment, you can begin to build Access Web forms.  The Web form has limited functionality but that may be OK if the Client/Server part of the app does the heavy lifting.  Access web forms do NOT support VBA and so you are limited to whatever you can do with macros but they're probably better than Info Path since they are not disconnected.

    That should get the conversation started.

    Author Comment

    Yow.  OK, then.  I would love to think that our little database is simple.  The origin is roughly Y2k so doubt new data types exist.  I inherited the database of course and there were poorly named fields that have since been resolved (i.e. using "?" in field names.)

    The relative ease of developing SharePoint forms using InfoPath is my main attraction.  Again, our data is relatively simple and project life cycles are finite.  We do more with documents and document libraries than actual data entry.  Again, this is the attraction to SharePoint and using one interface for the entire project in this case, SharePoint.

    Would be easy for me to over simplify and say SharePoint lists are fine.  I have connected lists to Access using Lightning Tools and have developed some pretty good web service type renderings of what is essentially list data.  Am only reasonably confident that lists will be OK in limited experience.  

    Let's assume SharePoint lists are sufficient, for now.  A better question is then how to manage what is essentially the one and only primary key of the Access design.  There are only ~7 tables in the design and on field in one table is the PK for whole shooting match.  Does this back story change the tune, at all?
    LVL 48

    Accepted Solution

    > .. migrating the tables up to SharePoint and using the traditional front end (split design)

    You can easily do this by exporting the tables to your Sharepoint. And the old Sharepoint lists are gone; these days they are driven by SQL Server behind the scene. If only seven tables, it should be very fast to check out.

    Don't know about InfoPath except that it going to be phased out. Access 2013 is now the recommended tool for this kind of jobs.

    LVL 33

    Assisted Solution

    The solution you are looking for is not an Access solution at all so I am biased.  The fact that you are using O365 is irrelevant.  If your tables are SharePoint and your forms are InfoPath, what are you doing with Access?

    SharePoint lists were always hosted in SQL Server databases.  Nothing about that has changed.  The issue is how lists are actually stored.  The technique makes for easy development but poor performance unless your tables are never more than a few thousand rows.  I'll give a superficial example of the architecture of a list.

    SomeID (autonumber)
    FirstName (text)
    LastName (text)
    BirthDate (date)
    PhoneNum (text)

    SharePoint list of YourTable
    UniqueID (identity)
    TableName (text)
    TableKey (text)
    ColumnName (text)
    ColumnValue (text)

    So the 5 columns in your original table each end up as a separate row in the list.  The data is stored as text and is converted as needed.  This way EVERY SharePoint list has exactly the same structure.  You can stuff any other table into it without design changes.  That's the whole point.  You don't need someone to create new tables for you since the underlying table is always the same.  I don't know whether all your lists end up in the same table or if SharePoint actually creates a new template table for each list.

    So, if the original table has 20 columns and 5,000 rows, the SharePoint list has 5 columns and 100,000 rows.

    Also as gustav mentioned, Access is probably going to have a longer life than Info Path so check that out before you commit one way or the other.

    Now, the way lists are constructed is really quite clever and useful if you need to support the constant creation of new lists and you don't want a DBA involved.  I created an application using this exact technique for a specialty lines insurance company.  They were constantly coming up with new policy types and therefore new data fields that needed capturing.  The IT department was taking 6 months to implement each new line of business.  I created an Access app, that allowed users to define their data, just the way SharePoint does and with my app, they could create the infrastructure for a new LOB in one or two days.

    Author Closing Comment

    I'm going to close this one and split the points.  Will open another to dig deeper.  Warnings about SharePoint lists as tables and the end of life for InfoPath are both helpful responses.  On to the next, with my thanks...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    We had a requirement to extract data from a SharePoint 2010 Customer List into a CSV file and then place the CSV file into a directory on the network so that the file could be consumed by an AS400 system. I will share in Part 1 how to Extract the Da…
    Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 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

    18 Experts available now in Live!

    Get 1:1 Help Now