MS Access v SQL (2 Viewers)

[pekajo]

Hi,
I am after some advice.
I am writing an ms access database for a charity, not to complicated or many users (5 at most), my question is I want to put it online and want some advice on my options.
I plan to just move the database to a cloud storage and give access to the users, or should I learn sql and approach it from that point of view.
With the plan to move to cloud storage how will ms access multi users handle it or is it just record locking.
Once again I don't know what I don't know so any advice you can give would be appreciated.
Regards
Peter

 

[theDBguy]

Hi Peter. Access was never meant/designed to be used on the cloud. If you want a true web enabled database, you have to go with SQL Server and use a web front end as well. Otherwise, you can keep Access as a front end but use SQL Server as a back end.

 

[pekajo]

Hi,
I hear and understand what you are saying but I am thinking that this is a cloud based DB with only a few people will access when I have given them permission.
My plan is to get a free 2gig onedrive ,whatever, and just use that as I can give permission to an actual folder.
Peter

 

[Galaxiom]

I hear and understand what you are saying but I am thinking that this is a cloud based DB with only a few people will access when I have given them permission.

Regardless of the number of users or size of the database, Access does not handle dropped packets and the latency you get with a WAN connection. In this situation if isn't an "if" but a "when" that the backend will corrupt.

Some have reported successfully using an Access front end against an SQL Server backend but I don't think it would be ideal.

 

[Minty]

Access works nicely with Azure-hosted SQL servers, particularly if you use the latest ODBC drivers.
We have a number of applications running in this configuration, one of which has a main data table with over 3 Million rows.

You need to be careful about loading vast datasets straight onto forms, and it can make a lot of sense to keep local copies of any lookup tables like employees or even clients if they don't change frequently.

 

[CJ_London]

the other alternative is to use terminal server - both front ends and backend are hosted online and performance is very good. But it's not free.

Whatever you do online has a cost. You need to balance the cost of these basic options

• paying a terminal server subscription for 5 users and low development cost access FE and BE (with free FE and BE licencing) - basically, other than relinking FE and BE, your existing app will work as is.
• paying the subscription cost of using SQLazure BE with not quite as low BE development cost (you will need to tweak your queries) plus low development cost access FE (and free FE licencing)
• paying the hosting cost of using something like mySQL BE and a high development cost to create the web pages - which may have additional hosting costs.

The last two options will require strong data management as Minty points out to minimise traffic across the internet - part of the subscription cost includes an element for the movement of data in and out of the BE

Note that Access simply does not work on one drive and similar.

 

[masteripper]

Well Access and Cloud in the concept of file just doesn't work...it might work for very small application for some tries but eventually lost packets will corrupt and so it will become useless.
The way to use it online are :
1. SQL BE/Access FE if the application is small it might be easy, if not we are talking about major rewrite
2.RDP : just put your application on a Windows VPS, pay the 5 pack RDS licences and let your users connect via Remote Desktop, application will work right away,will be fast and no extra work is needed, if you "put" the extra FX of RemoteApp for the end users it would seem like they are working on a local copy (provided your Internet connection is at least sufficient..minimum 5 Mbps)

 

[cheekybuddha]

>> My plan is to get a free 2gig onedrive ,whatever, and just use that as I can give permission to an actual folder. <<

This will not work with an Access database (backend).

See here for useful info

hth,

d

 

[Isaac]

I was wanting to clarify something about the Onedrive approach/idea. Disclaimer: I am not actually suggesting you do this, I think at some point you are likely to run into concurrent users issues and the possibility of data or file corruption would exist--therefore--I am only saying this as a point of fact, not recommendation.

If what you mean by this OneDrive approach is to simply:

1. have one single database file (no front end/back end)
2. have one user at a time utilize this by either A) Downloading from OneDrive, using database, then re-uploading to OneDrive, or B) map a local drive/folder to OneDrive desktop and do the same thing

Theoretically, Access isn't going to complain. It would be kind of like a Word or Excel document. One person downloads it, uses it, and then updates the online version with the single contained file again.

Note that I am NOT suggesting OneDrive will work as a shared back end, shared front end, or anything else.

But having said this, that approach seems to be of very little use. I have done work for a side client before who was a retired gentleman working on a building repair project for an HOA. He really wanted an Access database, and swore only one person would be using it at a time. At some point we switched to a Virtual Server (1&1 - now aka IONOS) with RDP access, which was cheap and great. But prior to that, there was a period of time in which I'd just hand it back and forth to him, and he'd put it on OneDrive, where another user would download it and use it. When I needed to perform updates they'd send me the whole current file, and on and on it went.
Obviously clunky as heck and next to useless, but it certainly "worked". But only 1 of your users could use it at a time.