Access 2010 / Split Database / backend on SharePoint

Question

I have a split database on my local drive and uploaded the backend database to a sharepoint Document Library.  When I try to update the link from my local drive to the backend now on sharepoint, it doesn't show the file in the folder (although it's the correct file format).  Any ideas why I can't update that link?  Thank you

Answer 1

Simply placing the access database on a SharePoint site will NOT allow you to linked tables in that acccDB file.  In fact placing an access database on a web server will NOT allow you to link to the tables.  Linking to an access database back end mdb or accDB file ONLY works if the back end is placed in a STANDARD windows folder.

However what you can do is send the tables from access up to SharePoint, and in this scenario you'll not be placing the mdb or accDB file on SharePoint but MIGRATING the data from the accDB file to what are called SharePoint lists. So if you do this, then you CAN LINK to those tables on SharePoint, but you will NOT be linking to the mdb or accdb file anymore.

Last but not least, in access 2010 you can also use Access Web services and publish your tables to SharePoint site. Again this will allow you to link a standard Access front end to these data table and ALSO allows you to build web forms.

So, just keep in mind the BIG difference between uploading your data tables SharePoint and that of simply placing or saving the mdb/accDB file on the server.

So:

Place/save mdb/accDB file on SharePoint = no linking from Access front ends

(this includes uploading the mdb – uploading is NOT same as exporting the data tables to SharePoint)

Upsize/upload tables to SharePoint lists = yes, you can link to these tables from Access.

(this means you are exporting or publishing the TABLES to SharePoint and NOT the accDB file). If you do this, then as noted you can link to the data which will now be in SharePoint and NOT in the accDB/mdb file anymore.

Albert D. Kallal  (Access MVP)

Edmonton, Alberta Canada

Answer 2

A great question.

And yes, a GREAT setup is to use the low cost office 365, and then keep/use your VBA front end.

So, your question and idea is in fact correct.

It is most important to distinguish between a web application (Access Web services), and that of keeping your existing Access VBA front end, and up-sizing your tables out to some kind of "back end".

Office 365 (or even SharePoint) gives you both of the above choices. For an existing application where you need to deploy a few users around the country with internet, and keeping your front end "as is".

You can most certainly consider using something like SharePoint or the very low cost office 365  p1 plan. The office P1 plan starts at $6. And you can "hang" a few users off of this one plan to share your application and allow it to run anyplace and any time.

Keep in mind that some table limitations exist when using Access with 365 this way. And you need Access 2010 (or later) due to the new "caching" system. However, this setup means you keep your existing VBA application, and only place the tables up in 365. You then simply install your application on those laptops. The result is a great low cost system in which all users are updating and editing the data, and they can do so anywhere in the country with an internet connection.

As noted, some applications are not suited – you have to do some performance testing and learn what works well, and what does not. However, the concept of placing your tables up in the cloud as a back end works very well, and I currently doing this for a number of clients with great success.

How you up-load related data and tables to office 365 is outlined in the following video of mine:

http://www.youtube.com/playlist?list=PL27E956A1537FE1C5&feature=plcp

So, I find this a very exciting option, and as noted is very low cost to get going if you use office 365 for this.

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 3

Yes, the features I speak of remain in-tact.

So you actually don't need nor use web publishing, but simply up-size the tables to SharePoint.  I don't recommend the starting table sizes be more then 5000 rows. You can successful "grow" to quite beyond 5000 rows, say perhaps 10,000.

So performance limitations do exist. For many applications this row issue is not too big of a deal.

If the tables are significantly larger, then you can use 2013, or 2016 web publishing and the tables wind up in SQL Azure (the cloud edition of SQL server). You then can link your VBA front end to this back end database. This setup does NOT have off line mode, but can handle larger data sizes.

Regards

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 4

Albert--I do not know if you will monitor this since it is several years old.  However, I am needing to set up a split database as you described and since there have been a lot of changes in both Access for 2016 and the new Sharepoint, I want to know if what you described is still applicable?  I have not been able to search and find current information on this question.

Answer 5

| I am not pleased the web compatible equates to inability to set a field for time (I need 24-hr time such as 19:30).

Hi KarenTT, I've encountered this issue too.

Did you find a workaround for the 24-hour time field?