Hrm. My previous employer used Access for its operations and later scaled up to SQL Server, with Access remaining as the front end. It worked, but there's some problems with the implementation.

First, having people use Access to get to data in SQL can potentially be a minefield of trouble. If your front-end users don't understand programming well, as you indicated, their queries may end up like a tangled blob of spaghetti. They'll get their results (maybe!), but it's entirely possible that the queries they use will be horribly inefficient, or, painfully enough, non-deterministic (so the results will vary based on executions).

Second, Access functions best with a SQL Server backend when the queries you write for it are pass-through queries. This style of query lets you write the SQL code directly, which is executed on the server, and the results are passed back to Access for displaying.

Without a pass-through query, Access will instead request the entirety of the dataset from the SQL Server, operate on it, and then display the results. Because Access is not nearly as efficient as SQL Server, this will kill the performance, especially if the datasets you're working with are quite large. If the datasets are relatively small, however, it won't be as bad.

I suppose it's important to question the company's current setup; do they have any programmers, or a DBA? From your description, they don't, and that's all kinds of bad. If they don't have a DBA, they have nobody to tune the SQL queries with, nevermind maintenance and backups. If the statistics are horribly out of date on the database, and the database isn't indexed well, nothing the front end can do will ever help with optimization. The same goes for backups and corruption checks; if nobody's there to implement those, the entire business is dead in the water the moment something corrupts (and it might already be corrupted somewhere, if nobody's checked for it!).

It sounds like what the business needs is a solid programmer and a solid DBA, at minimum. I might be ahead of myself here, but a business that relies on in-house programmed systems cannot survive without the personnel to actually maintain and extend those systems. Granted, if the proper automation is set up to ensure the livelihood of the system, and all user needs have been accounted for, sure, that gets the problem out of the way. But it's unlikely this will be the case in a complex business.

Err... Well, soapboxing aside, that's my bit on the subject :-). In summary, Access is workable, though with major pitfalls that your users can easily drop into, and I'd highly suggest the business be motivated into getting the necessary personnel as soon as possible.