74612

Split MS Access Database and store back end in Sharepoint?

Question:

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?

Answer1:

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.

Answer2:

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?

Recommend

  • In Ember.js, what's the difference between store save and store commit?
  • Why can't pass only 1 coulmn to glmnet when it is possible in glm function in R?
  • Matlab Generating a Matrix
  • Error: java.util.Arrays$ArrayList cannot be cast to java.util.ArrayList
  • AWS S3 implementation for serving private files
  • Return null in boolean to checkbox state converter in XAML
  • segue from landscape only to portrait only
  • Azure table query partial partitionkey guid match
  • Copying rows in a database when rows have children
  • Passing information to server-side function in a Google Docs Add On
  • What is this strange character in chrome's resource css viewer?
  • Is there any way to call saveCurrentTurnWithMatchData without sending a push notification?
  • Why isn't my “Fizz Buzz” test in R working?
  • SQL: Getting the physical size of a subset of a table
  • SIP API media codecs
  • Python PIL to extract number from image
  • Why doesnt this Java loop in a thread work?
  • Mixing WebForms and MVC: What should I do with the MasterPage?
  • CodeIgniter URI Parameter is partially bypassing an “if” statement
  • Angular2 - Template reference inside NgSwitch
  • How do I remove all but some records based on a threshold?
  • Does Apportable support to build library binary (.a/.so)?
  • How do I get the list of bad records that didn't load in Bigquery?
  • How to get links to open in the native browser in iOS Meteor apps?
  • Hibernate to update table schema
  • opencv display image without x server
  • Sequential (transactional) API calls in angular 4 with state management
  • Jackson Parser: ignore deserializing for type mismatch
  • Change multiple background-images with jQuery
  • Ajax Loaded meta Tags
  • How to rebase a series of branches?
  • Xamarin Forms - UWP Fonts
  • Is there a javascript serializer for JSON.Net?
  • What is Eclipse's Declaration View used for?
  • Where to put my custom functions in Wordpress?
  • Trying to switch camera back to front but getting exception
  • WOWZA + RTMP + HTML5 Playback?
  • How do I rollback to a specific git commit
  • Buffer size for converting unsigned long to string
  • Binding checkboxes to object values in AngularJs