Is access the way to go?

[cprobertson1]

Good Morning folks!

I've got a project here - quite a simple one actually.

It's just a system for aggregating comments about the running of business (like "need better control of welding materials" or "purchasing need to get their bottoms in gear") from various users and allows administration of them at various management levels - good stuff.

I've recently received the order to upscale it to a European and then worldwide distribution.

At the moment it's just a split database - my intention would be to upgrade it to a mySQL back end (somewhat arbitrarily chosen, because it's free - I can use any back end in principle, provided I can get funding for it).


The user-base would be about 30-40 moderators, and approximately 200-300 commenters - however - only the moderators would be using it for any amount of time - the commenters just submit a comment and that's it - after that it's mostly handled by a chain of emails.

Bearing in mind that I can potentially have a dedicated server as a back end (or at the very least, a VM or even several), is using Microsoft Access as the Front-End a bad idea for a project of this scale?

I'm afraid I've not had any experience of a deployment of this sort of scale - the most I've done is three factories, with very low traffic (1-3 users per plant)!

Many thanks in anticipation - hopefully I'm not asking too stupid a question

 

[isladogs]

The basic idea is perfectly suited to Access ... unless you are talking about all users worldwide commenting in the same SQL datafile.

Access works very well in a LAN but not in a WAN.

Suggest you use SQL Server Express rather than MySQL. Its also free and as both are MS products, they are well suited to each other

 

[cprobertson1]

The basic idea is perfectly suited to Access ... unless you are talking about all users worldwide commenting in the same SQL datafile.

Hmm - I think that might have been what I was intending I'm afraid - any given site would need access to the comments from other sites - but, as I mentioned, I'm unfamiliar with the territory; what sort of layout of database files would/should I be looking at here?

Suggest you use SQL Server Express rather than MySQL. Its also free and as both are MS products, they are well suited to each other

That was actually the original plan - but after a (very cursory) glance, there was a limitation of the SQL Server Express I was worrying about... except, looking over the limitations, I can't figure out what it actually was!:banghead:

It might have been the max database size of 10GB - but I'm pretty sure that can be overcome pretty easily just by adding in more database files?

 

[jleach]

You have a lot of data to catch to go over 10GB (the Access limit is 2GB, and most people don't hit that, but tend to migrate to SQL Server for other reasons, typically security). In any case, SQL Server is the next usual step from an Access BE.

With that said, there's no reason at all you can't do a remote SQL Server backend and a distributed Access frontend. We've done this many times and have several active projects with that setup.

The trick is to know what you're doing. This article takes a deep dive into performance with a remote backend: https://dymeng.com/azure-series-05-database-performance/ (or come to Vienna in April for Access DevCon where I'll be giving a talk on the subject: http://www.donkarl.com/devcon/agenda.htm)

So: a remote backend with a distributed frontend for what you need to do sounds fine. If we didn't want something web-based (which can be secured a bit more), that'd probably be the route we'd take as well, given your description.

 

[jleach]

Also, rather than SQL Express and a self-hosted server, SQL Azure is quite reasonable for pricing at the lower service tiers and is very easy to use in a situation like this.

 

[Gasman]

We use an Access system in work from a web browser. The DB runs on Windows Server 2012 R2 and we just login. That appears to run locally on that server and we just have a window to standard forms etc.

 

[CJ_London]

you can also look at using sql azure (not free, but not necessarily expensive) for your back end.

However suspect your main issue is the front end. Yes access can do it, but each user will need a copy of access (or the runtime) to be able to use it.

With the global organisation you describe, you probably already have an intranet and I would have thought the powers that be would prefer to channel this sort of activity through that.

as another approach for a budget solution - One setup I developed a number of years ago for an authorisation process was to use email (reason, servers were slow, not everyone had ms access, not everyone had access to a company LAN). Access would watch a specific email account and when emails arrived (to an agreed format) they were inspected, data was imported and automated emails sent out (the original email being moved to a specified folder) and returned in due course for further action. An administrator would check the account inbox regularly for any emails that were not imported because they did not meet the criteria.

 

[cprobertson1]

Thanks for the help folks! Very much appreciated

@jleach - aye, it just occured to me how much text 10GB actually is - in fact, I've generated text files containing billions of figures (raw results from simulations of ecosystems at uni) and they were only a few GB each - and this database won't have anywhere near that much data to deal with!

Off-hand, I don't think we'd be wanting to use a web interface - though I will of course look into it!

@CJ_London: we do indeed have an intranet - in fact that was one of the ideas up for discussion - the decision to try access was based around the distributed front end I wrote that two of the sites already use (just running off a network drive) - and as far as I know, every site has a site licence for MSAccess - at least in the UK.

I'll bring up the Intranet option the next time there is meeting on the subject (which would be... tomorrow apparently - wow, am I glad I looked at my Outlook Calendar/scheduler there!) - I would not be developing that though web design is not even close to my forte!


Thanks again for the help everybody - lots of stuff to think about (and that article on Database Performance looks like it will be a good read too!)

 

[Lightwave]

Azure managed SQL Database backend with local MS Access front ends can be really nice.

Access runtime is free so don't need to have a paid licence on local computers
Access runtime 2016

There should be runtime versions of all Access versions but they get harder to find as you go for older versions of Access.

Here's a nice recent lecture on using Access with the cloud partly delivered by Juan Soto

https://www.youtube.com/watch?v=XgSqPnuFgNs

He explains a really nice example of a SQL Azure backend / MS Access front end (for complicated system admin) and ASP.NET web page for more basic user operation. ASP.NET would be additional cost as would require separate hosting to SQL Azure backend.(ignoring the cost of your time to learn and develop!)

This looks like a really nice format. In your case if you go for SQL Azure / MS Access you could independently develop ASP.NET functionality at a later date this very much would future proof your system. Similarly if the web app goes awol you still have the security of rock solid MS Access. Web Forms are a bit poor (in terms of controls and master details forms) and complicated and confusing to put together compared with desktop access forms at present (this intrinsic feature of Web applications and not Microsofts doing) - a lot of people are working on things like standards and stability in web frameworks and stability flexibility and usability is steadily improving although it changes so quickly that in itself this is a big issue for organisations.

 

[jleach]

you could independently develop ASP.NET functionality which future proofs you. Web Forms are a bit poor and unstable compared with desktop access forms at present - this is likely to change over the next 5 years.

We do this a lot. Many (most) clients want to have on-demand, no-install access to an app, and web apps are great for this. Then you can attached some Access apps to the same db on the side.

As for web forms being a bit poor and unstable... not sure about that. To be clear, there's a few different ASP.NET technologies, namely Web Forms and MVC. Web Forms are actually old and dated, not used much anymore. Like the VB6 almost. And they were never really any good anyway: the Web Forms development paradigm tried to retain the event driven programming models of the desktop world and bring it to the web, which really doesn't work. MVC on the other hand is superb, and far more stable than Access (in recent years, we've experience a sharp downturn in the stability of running Access apps: particularly due to their CTR and O365 models - they're really making a mess of things).

So in general, a well put together ASP.NET MVC application has much better long term stability, IME. The problem is the cost of developing them. We're pretty good at it and have built up a lot of internal tooling over the years, but a quick Access app is still faster to develop. Nothing will ever match the RAD awesomeness of Access.

Cheers

 

[jleach]

Regarding web development stability: it's an open field and one has to choose what they want to use carefully in order to be stable. Well chosen libraries can make for a highly stable and nicely functional web app, but if you just go grab whatever from wherever, it's going to be a mess. We've definitely found that the "make minimal use of external resources and keep everything self contained" way of doing things helps considerably. Browser compatibility is excellent these days, and even backward compatibility is good.

Anyway, one of my favorite articles I've read recently touches base on the ridiculousness of javascript frameworks. If anyone's ever tried to put together an Angular or React project, check this out: https://medium.freecodecamp.org/eve...l-written-more-than-5-minutes-ago-f96642d4f05

 

[Lightwave]

As for web forms being a bit poor and unstable... not sure about that.

I changed unstable to - complicated and confusing to put together.

Undoubtedly a measure of my knowledge - I've only dipped my foot in web applications development.

I find web form design more intricate and tricky there are less people about to ask questions and error trapping is sparser.

But yes the benefits are great once they are up and running.