MS Access and SharePoint Queries

Question

Hi Access Gurus

I have questions concerning two existing split access databases and the prospect of either the back ends or linked files being located in SharePoint.
Currently the backend of each database is in Azure on a Windows server - the performance is just about tolerable.
However, corporate IT want to move the content of the Azure server to SharePoint.

So the questions are:

1. From existing posts I understand that simply relocating the backend of an Access database to SharePoint will not work although I'm not sure why. Also, there are posts which describe migration of the backend to SharePoint in a way that will work, but I don't have a feel for the complexity of this exercise or what the performance is likely to be (our business unit has only relatively recently started using SharePoint and it seems to be on the ponderous side compared to on-premise setup). Comments on this lot appreciated.
2. One of our databases currently has records with links to external PDF files on the Azure server. If these PDF files are moved to SharePoint, will it simply require an update query to modify the path to these files to point at the new SharePoint location? Or are SharePoint hyperlink-style links not supported in Access?

Thanks for any assistance

Phil

Answer 1

relocating the backend of an Access database to SharePoint will not work although I'm not sure why.

Well, these so called web based "one drive" etc.? They are web based, and NOT windows "file" networking which Access requires.

Think of using Word, or Excel. You can (have to) "save" the file after working on it. Note how when you work on a Access database, there is no "save" the file after you are done.

This is because if you launch an access application with a table of 10 rows, or 100,000 rows, note how access loads in the same time!!!

Access has (and requires) the ability to update ONLY bits and parts of a file. Unlike Excel, or word, thus the WHOLE file is not re-written to disk EACH time. So, web based systems don't allow you to update JUST one row, but ONLY can allow you to update the whole file. And if you updating the whole file, then you would wind up over-writing any changes made by any other user (just like what occurs say when using Excel).

So, Access is "very" different then most desktop programs in that Access has the "amazing" ability to update ONE row out of 100,000 rows. The result is blistering performance compared to word or excel, but the "big" downside is that the system that holds/hosts the data file must have this "update only part" of the file ability. Web based "file" systems can't do this - they are limited to WHOLE file operations, and thus for any kind of multi-user, you can't use say "drop box" or "SharePoint" or any other web based system (since they are limited to ONLY whole file operations). This also means that the WHOLE Excel or Word document must be loaded into memory. Access only loads the ONE row you request that is "inside" of the file. Hence, you can pull a row out of a 100,000 rows, and it is it instant without delay. this is why database systems are so much faster (and often better) then say using a Excel spreadsheet. (you have data operations at the row level, and not "whole file" resolution. Web based file systems can't update "bits" and parts of a file - only a whole file.

I don't have a feel for the complexity of this exercise or what the performance is likely to be

So, since you can't just "drop" the whole data file for above reasons? Then you CAN migrate the access tables to what are called SharePoint lists. From the Access point of view, those lists look like regular tables, and you "gain back" that all important ability to update single rows.

However, SharePoint lists are not "really" a high performance database, dispite access being able to "fool you" into thinking as such.

what does the above mean?

Well, in most cases, then those so called SharePoint "lists" as square pegged into a Access database system?
you don't want the row count in a table to go much beyond say 10,000 rows. and if you using hosted SharePoint (not on-site), then I suggest that tables start out as LESS then 5,000 rows. They can grow to say 15,000 rows, maybe 20,000 rows tops. So, for some applications, if the largest table row count is < 10,000 rows, then that setup can work.

However, for Access applications that have to update a lot of rows, or deal with a lot of rows? Then SharePoint so called "lists" are not a great choice. So, this is kind of the right horse for the right course so to speak!

Some Access applications are thus ideal for SharePoint, and some are not!

Larger row counts, and say lots of VBA code that updates lots of rows? Not going to work all that great with SharePoint.

However, for some Access applications, this setup is ideal, since then for a rather low cost, you can have 10 users, at any location with a internet confection, and they all are working on, and updating the one same database.

Currently the backend of each database is in Azure on a Windows server

Ok, is that just a file share, or are you talking about having migrated the Access back end data to SQL server running on Azure? this without question is the best setup, but requires a good amount of love and care from the Access developer. That love and care will be all about "epitomizing" the speed and ensuring that forms don't pull un-necessary records from the SQL server database. And this setup while can be VERY good, and perform well? it does require good SQL server skills on your part to make the Access front end (the application part installed on each computer with the forms, and reports and code) to run well.

Another possible choice?

Move both the data file and access front end application part to the cloud, and then use remote desktop. This works well, but means you need to have/buy the resources to run desktop's in the cloud - that can be expensive, but it works well, since the data stays on that remote computer - and you only "remoting" in with a remote desktop setup.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta, Canada

Answer 2

Wow Albert

Thanks sooo much for your detailed response.

Not only answering the posted question but including supporting info too.
You are right in thinking that the backend is currently a standalone database and not migrated to SQL.

I suspect that the short-term solution for us will be to place the backend on a local server (for performance purposes) and backup the backend to SharePoint or wherever for security.
Unlikely to have the time to look at the SharePoint list or SQL options before the general move away from Windows server to SharePoint.

What are your thoughts on part 2? Can I have records with links to SharePoint files/locations rather than Windows files/folders, to open PDFs?

Thanks

Phil