MS Access / SQL as suitable business solution

This is a very general question. Call it a thought bubble.

I have a client who has a very old legacy system (Tetra CS/3 running on Unix) with lots of modifications unique to their business. They need to update at some stage but will have a huge problem re-engineering all of the changes. For this reason package software may not suit and we are considering rewriting the entire system using a combination of SQL Server and run-time Access databases in a type of client/server relationship. The basic accounting functions are not that complicated, which is why I feel confident about re-writing the applications. I know I can use SQL Server Express to create a small configuration with just a few users and then migrate to full SQL if it works or use a cloud-based database. Ultimately, they'll want to have about 20-30 users with half of those being active at any one time.

The question is whether or not it is feasible to use a run-time MS Access database to provide this type of solution for each user. Virtually none of them will require massive amounts of data transfer; they mainly just do order entry and processing, receipts, payments, etc. I've seen a company run it's entire data processing needs on a series of Access databases before, so I can see it can be done, although I suspect that standard Access does not provide the sort of robust data integrity necessary, hence the SQL server.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I can't see why you would use an access database, unless your clients have to be offline and require some sort of sync'ing mechanism.  As to using SQL Express, it is very robust(Same engine as full SQL) and the biggest limitation is with database size, which I believe is restricted to 10GB, and memory usage which is restricted to 1GB.

I've seen horrendously inefficient solutions in Access or Excel, but just because it's possible doesn't make it a good idea.  The list of pro's for SQL over Access is long, so if you don't have a specific reason not to use it, it's probably the way to go.
Brian CroweDatabase AdministratorCommented:
I think he's just planning on using Access as a UI layer which is feasible if you don't have the resources to create the application yourself.

I think your plan is a viable one though you will probably want to use CRUD procedures to help avoid any concurrency issues you may run into with 10-15 users.
GregNielsenAuthor Commented:
Thanks for that. I've got a lot more research to do before I head down that track, particularly with the stored SQL procedures and CRUD approach. I just wanted to know if it was fundamentally the right approach or if I'm wasting my time. The reason why I wanted to use Access is because I know it pretty well and it's so easy to create/amend forms and reports, which is something the client seems to want a lot of. Their business is constantly shifting, and so I wanted to provide them with a degree of self reliance.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
An Access FE (forms, reports, etc) with a SQL BE (Tables, SP's, etc) is a "best of both worlds" type of scenario. Access works easily with SQL Server, and you can create some very nice user interfaces with Access.

As mentioned, figuring out what is best to leave in Access and what's best to move to SQL can be a challenging task, and one that you'll have to hone as you move into the project. There are some writeups that might help you with that decision: - The Best of Both Worlds in particular, but also the Relinker

Before finalizing your decision, however, note there is no further development being done on the desktop side of Access as of Access 2016 - ALL new features will be on the web side of things, and only bugs and security fixes will be provided from MSFT. In itself, that's not a show-stopper, but it would cause me to consider the long term maintenance and feature set of my solution. For my money, any new project where Access is a candidate automatically gets equates to a long, hard look into .NET. Here's an article that compares a couple of different platforms:
Jim HornMicrosoft SQL Server Data DudeCommented:
>whether or not it is feasible to use a run-time MS Access database to provide this type of solution for each user
Yes, I've ran this way for multiple clients.

Some thoughts (that aren't repeating the above comments)
Not a bad idea to inform them that it's going to take some time just to come up with the full requirements list of the 'custom logic', so you can get solid set of what you'll be writing.
>with lots of modifications unique to their business.
You're going to want to do a full cost estimate of rewriting the above in Access (or VB, or anything else), just to make sure the client is okay with that.  Some clients want to upgrade but don't want to pay the $$ to rewrite.
You'll have to manage how to deploy changes to the Access app, as walking to 20-30 people's desktops and giving them a new Access FE will get old quick.
Recommend bringing in an Access-SQL pro to help with design.  Just because you know it doesn't mean that you'll be able to design it correctly, especially if you know Access but not necessarily SQL and won't be able to design things like stored procedures, security, any encryption needs, AlwaysOn, etc.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GregNielsenAuthor Commented:
Thanks for the help. I'll try a few things first so I can better understand the options and then probably look for more assistance.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.