Best Practices for migrating an Access Database to SharePoint 2013 Online

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...
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
VirtualKansasAuthor Commented:
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?
Gustav BrockCIOCommented:
> .. 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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
VirtualKansasAuthor Commented:
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...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.