SQL server and SharePoint

[JonS]

SharePoint and databases

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

 

[NauticalGent]

I am pretty sure you can't place an Access database on SharePoint as it won't work

I would disagree. As long as each user has a front end on their local machine, you should be fine. Performance will depend heavily on the user's connection, but the issue of corruption should be a non-factor.

 

[theDBguy]

Hi. Welcome to AWF!

SharePoint is pretty much a big Excel application. I am a little confused by your statement. You said you have an Excel file and use Access to connect to it. Later, you said you have an Access backend file. So, where actually is the data stored? In Access or Excel? If Excel, you can move your data into SharePoint Lists and then use Access to connect to it, and your users will be able to remotely access that data using Access from their machines.

 

[Isaac]

I would disagree. As long as each user has a front end on their local machine, you should be fine. Performance will depend heavily on the user's connection, but the issue of corruption should be a non-factor.

You're talking about having the back end tables be linked SharePoint tables, right? Which does work...sort of ok. (Although this was the project I did in my previous job over the past couple years and eventually I threw up my hands at a lot of problems but it did work in a generic sense).

 

[NauticalGent]

You're talking about having the back end tables be linked SharePoint tables, right?

Exactly

 

[JonS]

Thank you guys for your responses. I'm sorry I was unclear.

I have an VBA Excel spreadsheet that connects to a single central back-end Access database. All the data for the spreadsheet is stored in the Access database. The front-end spreadsheet stores no data and contains the VBA code. It is like a font-end app. Many Users can use a copy of the spreadsheet to interact with the Access database at the same time.

This works quite OK in a local office network but you can't access that network outside the organisation (without VPN - access to which is restricted).

We have adopted O365 and SharePoint (and also OneDrive. I find the boundaries between SharePoint and OneDrive unclear - they are essentially both cloud storage locations). Increasingly, Users now store their files on SharePoint because those files can then be accessed remotely (ie, from home, as SharePoint maintains it's own security layer)

While the Excel application works fine in the office, you can't put the Access database on SharePoint. (Yes, you can literally store the file there, but you can't interact with it via ADO in the usual way as VBA does not seem to like hyperlink web address). I think the problem is that Access doesn't work (or work well) over the web?

So what to do?

Thanks, I have heard of SharePoint 'lists' but I think they have serious limitations, for starters, the number of lines that can be stored in a list? I am pretty clear I need a proper functioning database with views, multi-User capability and enforced data integrity etc etc.

What I want to be able to do is have Excel font-end Users be able to access the back-end central database remotely from home.

So here is my dumb question: Can you put SQL Server on SharePoint? Thinking is, if SharePoint is accessible outside the organisation, then maybe anything placed in it is similarly available?

It is likely a stupid question as perhaps SharePoint is not designed to do this.

(As an aside, I have been able to put Access on OneDrive and interact with it normally (as a sole User), but only if you map (sync) the OneDrive location to Windows File Explorer and then use the normal Windows Explorer file address (as opposed to the hyperlink). I don't know if this works in a multi-User situation. Another problem is that each User's device may map the OneDrive location with a potentially slightly different path in Windows File Explorer, depending on their device/situation. - so you can't program in just one 'file path' into the Excel application and expect it will work for everyone).

 

[Isaac]

Exactly

Thank you guys for your responses. I'm sorry I was unclear.

I have an VBA Excel spreadsheet that connects to a single central back-end Access database. All the data for the spreadsheet is stored in the Access database. The front-end spreadsheet stores no data and contains the VBA code. It is like a font-end app. Many Users can use a copy of the spreadsheet to interact with the Access database at the same time.

This works quite OK in a local office network but you can't access that network outside the organisation (without VPN - access to which is restricted).

We have adopted O365 and SharePoint (and also OneDrive. I find the boundaries between SharePoint and OneDrive unclear - they are essentially both cloud storage locations). Increasingly, Users now store their files on SharePoint because those files can then be accessed remotely (ie, from home, as SharePoint maintains it's own security layer)

While the Excel application works fine in the office, you can't put the Access database on SharePoint. (Yes, you can literally store the file there, but you can't interact with it via ADO in the usual way as VBA does not seem to like hyperlink web address). I think the problem is that Access doesn't work (or work well) over the web?

So what to do?

Thanks, I have heard of SharePoint 'lists' but I think they have serious limitations, for starters, the number of lines that can be stored in a list? I am pretty clear I need a proper functioning database with views, multi-User capability and enforced data integrity etc etc.

What I want to be able to do is have Excel font-end Users be able to access the back-end central database remotely from home.

So here is my dumb question: Can you put SQL Server on SharePoint? Thinking is, if SharePoint is accessible outside the organisation, then maybe anything placed in it is similarly available?

It is likely a stupid question as perhaps SharePoint is not designed to do this.

(As an aside, I have been able to put Access on OneDrive and interact with it normally (as a sole User), but only if you map (sync) the OneDrive location to Windows File Explorer and then use the normal Windows Explorer file address (as opposed to the hyperlink). I don't know if this works in a multi-User situation. Another problem is that each User's device may map the OneDrive location with a potentially slightly different path in Windows File Explorer, depending on their device/situation. - so you can't program in just one 'file path' into the Excel application and expect it will work for everyone).

To use distributed access front ends with sharepoint, you don't put the access back end on SharePoint that's not what we mean. Not literally putting an access file on SharePoint and then trying to connect to it. To use access with sharepoint, you design the front ends in such a way that they contain LINKS to SharePoint LISTS.

 

[JonS]

Thanks Isaac. To answer my own question , I think SharePoint is basically a web-page + file sever of sorts. It cannot house an instance of SQL Server.

 

[GPGeorge]

Thanks Isaac. To answer my own question , I think SharePoint is basically a web-page + file sever of sorts. It cannot house an instance of SQL Server.

Here's a link to a video on using SharePoint lists as the back end for Access relational database applications.

One should NOT try to place an actual accdb somewhere in SharePoint and try to use it as a back end. The same is true for OneDrive, DropBox, etc.

 

[theDBguy]

Thanks, I have heard of SharePoint 'lists' but I think they have serious limitations, for starters, the number of lines that can be stored in a list? I am pretty clear I need a proper functioning database with views, multi-User capability and enforced data integrity etc etc.

Just curious, how many records are in the largest table in your Access backend? SharePoint can have multiple views, enforce referential integrity, granular security, and allows concurrent multiple users.

 

[Isaac]

The previous job I was at I spent two solid years fighting with SharePoint and access. It's doable in a subpar kind of way but there were a couple things I ran up against that I could never solve despite all the articles I read and it seemed to me that a lot of people had hit similar brick walls.

One is if you have to have SharePoint lists with people type columns it will not be possible to update a table object in Access which represents a link to a list or view that contains a people column even if you're not trying to update the people column. Pretty big blocker!
I was starting to overcome this by convincing the business to use Network usernames as text instead of those people columns which works well with access but it was hard to overcome the business's habit of wanting to see those people columns. You can also try linking your access database to a SharePoint view that does not contain any people columns. This does work as a Band-Aid solution.

I also encountered a spontaneous error, database or object is read-only cannot update. It was so sporadic I could never solve it and we ended up not being able to depend on my access databases that used SharePoint list as a back end.

 

[JonS]

Just curious, how many records are in the largest table in your Access backend? SharePoint can have multiple views, enforce referential integrity, granular security, and allows concurrent multiple users.

It would be for transactional data - initially several hundred rows. But eventually, the application could extend to other 'mass data' which could easily be hundreds of thousands of rows.

What is the limit on SharePoint rows?

 

[GPGeorge]

It would be for transactional data - initially several hundred rows. But eventually, the application could extend to other 'mass data' which could easily be hundreds of thousands of rows.

What is the limit on SharePoint rows?

A whole lot less than that. SharePoint is looking less likely to be a viable solution here.

 

[theDBguy]

It would be for transactional data - initially several hundred rows. But eventually, the application could extend to other 'mass data' which could easily be hundreds of thousands of rows.

What is the limit on SharePoint rows?

SharePoint can handle a few hundred rows. Hopefully, when that eventuality comes that you need to handle mass data, a lot of time have already passed and that your company would have SQL Server available by then. Until that time comes, you might give SharePoint lists a chance for the time being. Good luck!

 

[Isaac]

@JonS
Sorry if this suggestion is redundant but if it is take it as an emphasis, have you investigated the possibility of using the free SQL Server Express in this project? I've put it to good use in some small business projects I have done. Even though I have very little true DBA (A as in Administrator, not Analyst) knowledge, it was easy to set up on a virtual machine anyway. There are space considerations, but most of the small biz stuff I've done doesn't come remotely close to touching the limit.

 

[Pat Hartman]

SharePoint is based on SQL Server but that doesn't help you. It is NOT SQL Server. It is an application that lets you store files or data "lists" which are similar to tables. If you expect your row count to exceed a few thousand rows, I would not bother with SharePoint at all.

OneDrive is a non-starter. You CANNOT share a BE using OneDrive. There is no way to control the Access to prevent multiple people from opening the database at one time and the last user to close, overwrites the previous saved version without any data merge. OneDrive treats Access databases as plain ol' files. It's all or nothing. All other similar cloud services have the same problem.

If you can host a SQL Server/Azure database, you can link to tables in it from Access. I have never had any success with this. It has always been worse than watching paint dry. The people who have reported success are running their own cloud and apparently know how to configure it to support Access. I tried three different third-party hosts and all failed. Perhaps it was because I always used the "cheap" plan rather than an expensive plan.

The two solutions that work very well are Citrix and Remote Desktop. Both of which you can host yourself or use a third-party. If you use a third-party, you're looking at around $40 per month per user PLUS the O365 subscription. I don't know what the server licenses cost for Citrix and RD but the seat licenses for RD are free with a pro version of windows. Otherwise, they are ~ $100 for a perpetual license. The cheapest solution is RD connecting each person to his office desktop. If you don't have Office PC's for every one, you might consider buying them depending on what a Server license costs. With a Server license for RD, you are running all desktops as separate sessions on the server rather than remotely connecting to physical computers.

 

[JonS]

Thank you for that Pat and noted your comments on SharePoint, OneDrive and cloud generally as a places not suitable for an Access database that will be used by multiple Users simultaneously.

I wanted to clarify, the application is an Excel front-end with an Access database back-end. Originally, I had wondered if changing the back-end to SQL Server would make a difference for remote access capability using SharePoint. It won't, as you have pointed out.

Separately, we used to be able to Windows Remote Connection from any device via VPN to our own desktop machines at work, but they stopped that access. Instead, we have all been issued work laptops. Those of us with (the restricted in numbers) VPN access can use the work laptop to access the network. But for those folks without VPN and and those who wish to work on their own home devices, they created/made available the Windows Virtual Desktop (as opposed to Remote Desktop Connection (or MSTSC)) which gives access to the network and applications provisioned in it. Originally it was painfully slow. But recently, they upgraded the CPU/memory and it works whole lot better. It is also likely a more secure environment as (it is my impression) it maintains a strict tunnel to the organisation. For example, in our case, you can't copy and paste from the WVD to your own windows environment on your PC, where as it would seem with VPN you could expose the organisation to anything lurking on your device (if that is correct).

Anyway, good information and thanks for your help and comments. JonS

 

[Isaac]

Thank you for that Pat and noted your comments on SharePoint, OneDrive and cloud generally as a places not suitable for an Access database that will be used by multiple Users simultaneously.

I wanted to clarify, the application is an Excel front-end with an Access database back-end. Originally, I had wondered if changing the back-end to SQL Server would make a difference for remote access capability using SharePoint. It won't, as you have pointed out.

Separately, we used to be able to Windows Remote Connection from any device via VPN to our own desktop machines at work, but they stopped that access. Instead, we have all been issued work laptops. Those of us with (the restricted in numbers) VPN access can use the work laptop to access the network. But for those folks without VPN and and those who wish to work on their own home devices, they created/made available the Windows Virtual Desktop (as opposed to Remote Desktop Connection (or MSTSC)) which gives access to the network and applications provisioned in it. Originally it was painfully slow. But recently, they upgraded the CPU/memory and it works whole lot better. It is also likely a more secure environment as (it is my impression) it maintains a strict tunnel to the organisation. For example, in our case, you can't copy and paste from the WVD to your own windows environment on your PC, where as it would seem with VPN you could expose the organisation to anything lurking on your device (if that is correct).

Anyway, good information and thanks for your help and comments. JonS

You're facing a classic access conundrum which, at the risk of being crucified for saying this, is probably one of the number one reasons that some people have come to their end of the road with access.
Because many people are in the following situation:::
Corporate environment, very strict and locked down, no one developer can just go out and decide to purchase citrix, most corporations stopped allowing remote desktop a long time ago much less maintaining a second machine for everyone in a physical office, working from home, on VPN only, and of course everyone is on wi-fi.

In that exact scenario, using an access for a back end is nearly impossible anymore.

I was in that exact same situation in my last job so I began investigating using SQL server as well as SharePoint lists for the back end. I was denied the former, and the latter ended up being a pretty crappy solution. I looked into power apps which were somewhat promising even though they don't allow near as much custom programming as access does which is sad but that's where we're headed for these extreme late generation platforms where no one wants to actually learn to code.
Not trying to be Negative Nancy, but my solution was moving to a different employer that made me happier. I hope you are able to find a great solution and end up happy somehow! Enjoyment of work is so important.

 

[leannemurphy]

Sorry to piggyback off this post, but has anyone tried to do a combination sharepoint and SQL Server solution? For example, for the lookup tables that feed your dropdown boxes could you use sharepoint to manage those lists, and then for the larger datasets that you may need to analyse, using SQL Server? Users can then just expect it to be slower when they are working with large datasets, but get the benefit of a faster solution if they are just using it as a simple database with limited rows?

 

[NauticalGent]

Sorry to piggyback off this post, but has anyone tried to do a combination sharepoint and SQL Server solution? For example, for the lookup tables that feed your dropdown boxes could you use sharepoint to manage those lists, and then for the larger datasets that you may need to analyse, using SQL Server? Users can then just expect it to be slower when they are working with large datasets, but get the benefit of a faster solution if they are just using it as a simple database with limited rows?

I have used both and there simply is no comparison. If SQL server is available, go with that and never look back...