0

We have a Access database initially created in Access 2010 for a small group of users (only 10 at present). All users are now upgraded to Office 2016 (not 365). The database is split, back end on shared drive on networked computer. Is currently small ie <1GB. The database is now required to be located online so that users can connect to the database from off site. This article really useful

SO 32653858

I have read quite a bit about possible solutions but not sure if I understand it all so hoping I can get a simple and cost effective solution. We are a not-for-profit organisation.

What I understand is that Sharepoint could be a solution but costly for us Recommended solution seems to be convert back end data file to SQL and keep front end local with all forms, queries, reports for users to use as per current situation.

So two questions.

  1. Difference with MySQL vs SQL Express vs SQL server

  2. Where should the SQL converted file be stored – “on the cloud” but where is this. Do we have to get a provider of space (ie not dropbox, onedrive etc). We are getting a website at some point – can the database be stored in same location/provider as website. Is this secure for sensitive information. (sorry if this is a silly question)

Thanks.

Community
  • 1
  • 1
Jakeymus
  • 5
  • 1

2 Answers2

1

SharePoint is expensive, but an office 365 subscription for about $6 allows you to invite up to 500 free users (and they can even logon using Gmail or non-Microsoft accounts). So up to 500 users for a grand total of $6 per month quite much shows that using SharePoint tables on office 365 is BY FAR the LOWEST cost choice, not some expensive choice as you suggest. The major issue with SharePoint tables is for tables over 5000 rows, the system can be slow. And all relationships defined MUST be an auto number PK and a long number foreign key). If your application “fits” within the limitation of SharePoint tables, then the low cost office 365 plan is a very good choice. In addition, the 365 setup allows the front end application to run even when the internet connection is lost – and data syncs when you are able to return to a location with internet services.

As for using SQL server, the general approach is you have to migrate your tables in Access to SQL server. Where that SQL server system is located is much YOUR choice. You can use the running instance of SQL server from your web hosting provider, but ONLY if that web provider allows external ODBC connections. Not a lot of providers allow external ODBC connections to SQL server (or MySQL for that matter). The reason for this is that exposing the web site database server to the wild internet full of hackers can be a risky choice.

Another choice is to use the Azure operating system, and on Azure there are versions of SQL server available (often called SQL Azure) – this choice does allow external connection.

So there not a lot of different between MySQL and SQL Express. SQL is free for business use, and MySQL server requires a license which is quite expensive. However, if you choosing web hosting, then you’re not purchasing the database server, but only paying your monthly web hosting fees – so when choosing hosted systems, you never have to purchase nor setup SQL server and thus the “price” is only reflected in the low monthly fees.

So the location of your SQL server is going to be in the cloud – usually from your web hosting provider. As noted, you can using basic standard web hosting and most every plan includes SQL server or MySQL as part of the low cost plan (the problem however is FEW providers allow external ODBC connections to the running instance of the SQL server running on that web site).

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • So am I understanding correctly that to use sharepoint we would only need 1 copy of 365 to utilise this? That would certainly come into our budget and our db is unlikely to get as large as 5000 for some years. As our data is confidential information I'm not – Jakeymus Nov 03 '16 at 09:45
  • Sorry... lost the link. So Sharepoint sounds like our solution. Am I right in that we could store/convert the back end data file to a sharepoint list using one 365 licence. Then users can then use their local front end access file to link to this sharepoint file to utlise the full vba capabilities already in the front end file. Or they could also choose to use a web front end. Having just found another of your posts about this, can you confirm that we would also have the benefit of offline use and syncronisation? All this sounds too good to be true. – Jakeymus Nov 03 '16 at 10:15
  • Thanks Albert for your advice. I'm going to start with RDP, then try SQL Express. I will try Sharepoint at some point too as you've easily explained how this is attainable for us. A big thanks also to you for all your other posts, I have read lots of yours and they have been invaluable. – Jakeymus Nov 05 '16 at 01:59
1

You don't need SharePoint or any other software. Just use SQL Express (you can always upgrade it to full SQL Server if you ever start hitting the performance limits) to make your database accessible on the Internet (security issues exist here so make sure you know what you are doing) or via VPN and distribute your front end with either Access or just Access Runtime. Depending on your knowledge of networking and Access you can accomplish what you want with zero software cost (assuming you already have at least one system with MS Access for development and maintenance and an Internet facing Windows server).

SunKnight0
  • 3,331
  • 1
  • 10
  • 8
  • My networking/vpn knowledge not so great. So I understand this to be that we convert the data file to SQL Express and it would still be stored on our network. but we would need vpn software to make sure it was secure and accessible to our staff when off site. We are small, we don't have a server, just network through our modem/router. – Jakeymus Nov 03 '16 at 10:21
  • You don't need a "server" server just a Windows machine that you set up with an IP reservation or static IP and a port forward to it. It can be any of your workstations, ideally one that is always on and has a battery backup. Pick a random port number (not the standard SQL port number) and set up SQL Express to use that. If you are careful with password strength and security and you are small enough not to have a server it is unlikely anyone will bother cracking into your database so you can forego the VPN. – SunKnight0 Nov 03 '16 at 12:49
  • You generally want to avoid hosting it online because then you add Internet access lag to your office environment. Not a big deal for the occasional remote access, huge deal for all day-every day. Also if everybody has stationary computers at the office you can just set up RDP access to those instead of putting the database online. Even a couple of standing Windows workstations will do if you are all well behaved about sharing and logging off when not using them. – SunKnight0 Nov 03 '16 at 12:51
  • Thankyou for the suggestion of RDP, don't know why I didn't think of doing that. With regard to the lag you mention - our internet is ADSL (only thing available) avg is 17mbps down and 4 mbps up. Not great but better than a lot of other areas around where we are (being rural). Onsite users would have it open most of day, users off site would just connect to update or search but would be for shorter periods of time, not all day. Would this be too slow a connection for sharepoint use. – Jakeymus Nov 04 '16 at 10:25
  • I don't use SharePoint so In can't answer to that, but I can definitely tell you that local access to data is a lot faster than remote access to data. Don't be fooled by how things work on a Word or Excel file where the data is effectively on a local cache/temp file and you only have lag when loading/saving. Access uses constant connection which makes a world of difference. How big a difference depends on how big your database is and how many reads/writes Access has to do when working with it. – SunKnight0 Nov 04 '16 at 13:20
  • Thanks SunKnight0 for all your advice. I'm going to start with RDP, then try SQL Express. I will try Sharepoint at some point too. Big thumbs up for all you guys helping on this forum. – Jakeymus Nov 05 '16 at 01:57