Moving from MS Access to SharePoint

i have a simple (MS Access) table with about 20 data fields.columns, and less that 10 million rows of SAP user security data.  Under one of the audit policies, I need to periodically present this data to the designated data owners, and need to capture their input as to the changes & additions to this data. Based on the data owner's input, I engage the  SAP Security team to implement the changes within SAP user roles, etc, and then repeat the same process next quarter/period. I do need to maintain the history of all changes (only) made by the data owners for tracking purposes.
I have the process working in MS Access 2016, however, I am asked to move the process to our SharePoint. That is where my problem begins, and I have the following questions/issues:

1- Is SharePoint capable of handling this kind of project. My initial concerns are the # of rows in the table (10 M),  & the # of rows of data being displayed or returned from a query for display, which is limited to 5,000 rows. Is that true?
2- AT the moment, I am using VBA to do most of the back-end processing required to maintain the history of changes, replication of tables, etc. and do not know, how to accomplish that in SharePoint? In other words, how, with which tools, I can replace my VBA code with, in the SharePoint?
3- If SharePoint is not my solution, what would be the best fit for replacing my MS Access solution that is web based, with robust data security features?

Thank you,

John
john saleemSr. Systems AnalystAsked:
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.

John TsioumprisSoftware & Systems EngineerCommented:
If you are looking for security along with ease of access you can't go wrong with MSSQL or MySQL as Back End
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<1- Is SharePoint capable of handling this kind of project. My initial concerns are the # of rows in the table (10 M),  & the # of rows of data being displayed or returned from a query for display, which is limited to 5,000 rows. Is that true?>>

  SharePoint is not a relational database system, but rather a workflow collaboration tool.  As such it does have limits in terms of number of records and one of those is how many entries can go in a list.   5,000 entries is really about it before you will start to have performance issues.  It will hold more, but even docs on SharePoint say that after 50,000, you should be looking at something else.

  You really need a RDBMs to store data if your talking 10M rows.  Working with 10M rows and storing data about that is different though.  I'm not clear on which it is for you.

<<2- AT the moment, I am using VBA to do most of the back-end processing required to maintain the history of changes, replication of tables, etc. and do not know, how to accomplish that in SharePoint? In other words, how, with which tools, I can replace my VBA code with, in the SharePoint?>>

  Custom applications can be written in SharePoint, but again it's a tool to be used to bring things together and not so much about building applications with SharePoint itself.  This article:

https://msdn.microsoft.com/en-us/library/office/gg454784(v=office.14).aspx#bb1_UnderstandingSharePointApplications

 Gives a good overview of what it means to develop with SharePoint.   Make sure you read the section "Comparing and Contrasting SharePoint Development with Other Forms of Development" and "Comparing SharePoint Applications to Database Applications" right after that.

<<3- If SharePoint is not my solution, what would be the best fit for replacing my MS Access solution that is web based, with robust data security features?>>

   I would say it is not, but the only thing that would replace Access really in respect to this project is a traditionally developed web app using ASP.Net or PHP, with a mySQL or SQL Server backend for data.

  Moving from the desktop world to the web world is an apples to oranges type of thing.  They are just developed fundamentally differently and there is no straight translation from one to the other.

Jim.
0
PatHartmanCommented:
I don't think this is a process I (as a user) would ever want to do on a web page.  I'm pretty sure that no one is actually concerned with the 10 million rows except you.  Each person who will be doing the review will be handling far fewer items.  Does this 10 million rows include history or is it just current data?  If it includes history, can you extract current data?  because that is all that would ever be changed.  Who says this needs to be a web process?  Is the problem simply that the users are distributed?  If no user needs to work with more than 100,000 rows, you can do this by creating spreadsheets and emailing the data to the user.  It should be pretty easy for them to filter and update the spreadsheet.  They can then send it back to you to apply the final updates.

If they need more than 100,000 rows, then you can distribute an Access app with embedded tables that will allow them to filter and update.  When done, they send the database back and you apply the updates.

This isn't a real time need  and  It isn't interfacing with anonymous users so since it is essentially a batch process, that is what I would create.  On your end, you would create log tables so you can keep track of whose data is still outstanding.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

john saleemSr. Systems AnalystAuthor Commented:
Thank you so much everyone. You have understood my question very well, and have answered it in detail. I have a better sense of how to go about putting a solution together. I think I will take SQL server route & will have to find a replacement for my VBA code. I do need to clarify a couple of questions above, and here is my feedback:

1- Current data & change history - It is my intention to maintain the change history. User changes will be captured in 2 added columns as the "Review Status" with a click, and "Review Action" with a delete answer, only when indicated by a check. The delete action is the only history that will be kept/appended in a ditto history table. That will be a permanent table. Each month these data owner actions will be plowed back into the source system, and then the main data table will be refreshed next review/change.
The "Review Status" check will be saved in a backed up & a dated table as an evidence that the data was reviewed.

2- Number of Rows to be Reviewed -  The data is at a very low level detail, and in many cases a filter/query for a User Group, etc. will bring back 200-300K lines of data. This works fine in a stand alone desktop based solution. Rightly indexed fields accomplish this without a problem. But, distribution of this amount of data without a database is an issue. That plus the security are the main reasons I am looking towards a SharePoint solution. But I understand now that SharePoint is not my solution.

3- Why not Use the Spreadsheets? - I think I have answered that question in (2).

This is my first question/experience on a forum like this, and I must say all of you guys are very helpful, and have answered my question.

Thanks again,

John
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Your welcome.

Jim.
0
John TsioumprisSoftware & Systems EngineerCommented:
Don't worry too much about the VBA code...just migrate your database to SQL ...link the tables and continue as usual (almost)...when everything returns to normal and everything is working as it should then its time to consider to replace the VBA code with SQL code (functions)
0

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
john saleemSr. Systems AnalystAuthor Commented:
John,

Thank you for a quality answer. I get it, and that would/should have been my first approach.  I may have to contact someone like you for the next stage.

John S.
0
John TsioumprisSoftware & Systems EngineerCommented:
Feel free to do...
0
PatHartmanCommented:
200-300K lines of data in a spreadsheet will be easier to work with than if you present it with a web page.  Distributing monolithic Access databases also works if you want more control over the update process.
0
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.