SQL Server and SharePoint

1

CONTEXT:
We currently use an Excel spreadsheet application that connects to an Access Database through VBA using ADO.

Separately, we are licensed for O365 and have SharePoint. We can log into our SharePoint via web remotely (ie, from home). There is a requirement for the ability to work remotely when required.

PROBLEM:
To enable Users of the Excel application be able interact with the centrally-held (currently Access) back-end database remotely (eg, from home). I am pretty sure you can't place an Access database on SharePoint as it won't work - (eg, can't be connected to with a hyperlink address and/or work normally?)
If Access won't work, would SQL Server?

Can you install/have a SQL Server instance on SharePoint for this purpose (if this is even a sensible question)? If you can, is it also true for SQL Server Express?

By the ability to work remotely, I don't just mean file sharing or the ability to access individual files, I mean being able to connect to the central database via ADO using the full power of code to govern operations.

The reason I mention SharePoint is that I know (parts of) it can be viewed remotely as it has its own security protections. That being so, then would a database (like SQL Server) sitting in that same SharePoint location be accessible? If not, what is the recommended alternative way to solve this issue?

Thx, JonS

2

No - SQL Server is a server based product and you would need access to that server. The recommended way of getting remote access is to setup and use a VPN to connect the remote workstation to the corporate domain.

All Sharepoint is doing for external access is allowing access to a website (essentially). It is not allowing access to other systems on the network.

3

Thank you jeffw8713

4

O365 might have what you need since that is online and accessible via a browser and might have both access and excel. Have you explored that route?

5

Thx yosiasz, but I am under the impression that the 'browser' version of O365 does not support vba, only JavaScript. The client version (ie, one that is installed on your device) continues to support vba.

The major disappointment is that Microsoft has not made Access (and vba) web enabled. If had done that, that database would have taken over the world as a rapid application development environment. Another lost opportunity...

6

Do you folks not have vpn?

7

We do, and that would work fine. Except it is restricted for the number of Users it is granted to, whereas the benefit with Teams, SharePoint, O365 is that it is generally available to everyone.

8

That is very unusual that it has to be limited.