?
Solved

Best Practices for migrating an Access Database to SharePoint 2013 Online

Posted on 2014-08-15
5
Medium Priority
?
523 Views
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...
0
Comment
Question by:VirtualKansas
  • 2
  • 2
5 Comments
 
LVL 40

Expert Comment

by:PatHartman
ID: 40264096
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.
0
 

Author Comment

by:VirtualKansas
ID: 40264179
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?
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 1000 total points
ID: 40264213
> .. 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.

/gustav
0
 
LVL 40

Assisted Solution

by:PatHartman
PatHartman earned 1000 total points
ID: 40264280
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.

YourTable
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.
0
 

Author Closing Comment

by:VirtualKansas
ID: 40264572
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...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Herein one will find an aggregate of some of my experience building and deploying virtualization stacks both in standalone, clustered Hyper-V, clustered Hyper-V with a Scale-Out File Server (SOFS) backend, and Storage Spaces Direct (S2D).
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

612 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