Split MS Access Database and store back end in Sharepoint?

2
votes

I have an MS Access database with a lot of VBA code. If I create an Access Web App I will lose the functionality provided by my code. Is there any way to store the tables of my database in SharePoint (not in lists) and link them to the front end to be kept on each users computer?

2
ms-accesssharepoint

2 Answers

4
votes

You can up-size the data from Access to SharePoint (or low cost office 365). SharePoint tables now support relational data, so “basic” master to child tables (and cascade delete) is supported when you move Access tables up to SharePoint (you are however limited to PR of auto number, and child records (foreign keys) must be a long number.

Depending on the size and number of rows of data, the above solution is OFTEN better then moving tables up to SQL server. A VERY nice feature of this setup is that your application can continue to run without any internet connection. The instant you find some Wi-Fi etc., then your data syncs.

So I would not write off the SharePoint table’s option, as they can work VERY well with many kinds of Access applications, including ones with related tables. And the “off line” mode can be great for those laptops in the field without an internet connection.

If you are running Access 2013, then you can import the data tables into a 20103 Access web based application, then publish to office 365 and Access will push the tables up into SQL Azure in the cloud. And like the first suggestion, you can now link your traditional “VBA” rich front end to the back end data. Keep in mind that this approach does not have the “off-line” mode, and in some cases more application optimization is required to obtain good performance, but the table sizes and limits are considerably higher then using SharePoint tables.

0
votes

Albert,

You mentioned "Depending on the size and number of rows of data, the above solution is OFTEN better then moving tables up to SQL server." What is the scale of the size and rows of data which is still good by Sharepoint? How about 500,000 rows of data in one single table with other 10,000 rows data in 100 tables?