Best Database for Inventory

At some point in the next six months, I have to create a database for my department's inventory.  Currrently, the department is using Excel 2013.  The database only has about twenty fields.  However, Ideally, I would like several departments to use it.  In the past, I have used Access for these databases.  However, my concern is that sometimes in the past Access gets corrupted.  Especially, when using through the network.  I have not used Access in a while, so not sure if it is more robust now or not.  Or, I would like to know if any database expert may have a different opinion and/or suggestion.  Thanks in advance.
Solo PascualIT SupportAsked:
Who is Participating?
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.

Access is stable when implemented correctly.  You need to split into two parts.  The FE (contains forms, reports, queries, modules) and the BE contains ONLY tables.  The BE (back end) is placed on a network share where everyone can link to it.  The FE (front end) is distributed to each individual so they have their own personal copy.  Therefore when each person opens the FE, they are not impacted by any other user.

It is only when multiple people open the FE that you run the risk of corruption.  Of course a monolithic app where the FE/BE are all in the same physical database is the worst possible situation and is the most likely to lead to corruption.

If you want a more robust BE, you can use SQL Server.  Access is a fine FE to SQL Server.

You can start with an ACE (Access) BE and convert to SQL Server in the future.  It will be pretty simple if you start by using good client/server techniques when developing the FE application.

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
Vitor MontalvãoMSSQL Senior EngineerCommented:
There's no such thing as "Best database for inventory".
You choose the best database solution that fits your needs and this depends on the requirements. Many questions need to be answered but here are some:
  • How many simultaneous users will need to access the database?
  • How large is expected to be the database?
  • How many transactions per second is expected?
  • What is the acceptable response time?
  • Is high availability required?
  • Is disaster recovery required?
  • Which operation systems are acceptable?
  • How much is the budget available for the solution?
Dale FyeOwner, Dev-Soln LLCCommented:
Another cause of corruption of Access data is attempting to connect the Access FE to an Access BE over a wireless network.  Wireless networks are inherently unstable and use of an Access FE linked to an Access BE over  a wireless network is bound to cause disconnects and lost data.  If you need to do this over a wireless connection, then you should consider using SQL Server (the Express version is free) as the database BE.

Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Just keep in mind that if you decide to for a database platform like MySQL or PostgresSQL or even MS-SQL  you will have to use a framework, like PHP or Ruby or .NET for the UI to build on top of that, which is not trivial task. Giving the fact that at the present your needs are covered by Excel I can assume that the usage and load are nothing too big and if you are familiar with Access than this seems your best shot. Access has the framework for the UI as well as the data in one package so it should give you what you need. As per a previous post you could use Access with MS-SQL if you want but I don't think is necessary. On top of that you will have to consider licensing for it.
Just keep in mind that if you decide to for a database platform like MySQL or PostgresSQL or even MS-SQL  you will have to use a framework, like PHP or Ruby or .NET for the UI to build on top of that
As I said, Access is a FINE FE for SQL Server.  It is also a fine FE for Oracle, DB2, Sybase, and pretty much any other RDBMS that supports ODBC.  Access out of the box at version 1 supported ODBC and has always been a multi-user platform.  I adopted Access in the early 90's with version 2.0 when I discovered that I could link to DB2 tables on our IBM mainframe and actually update them and I haven't looked back.  At least 90% of the apps I create, use a "real" RDBMS as the BE.  All the bad press you ever see about Access is targeted at Jet which people who don't know anything about Access cannot separate from Access the RAD tool which is used to build applications.  Jet (.mdb) and ACE (.accdb) are the desktop database engines that are inseparable in the minds of many from Access but although Access (which is what its name is all about) uses Jet or ACE to store its own objects doesn't need to use Jet or ACE to store data.

Access as an FE is infinitely scalable because every user has his own copy of the FE much the same way that every user would have his own copy of myprogram.exe if you built in a compiled language.  The only limit for Access linked to ODBC databases is the number of CALs you bought.

Access is however close to useless over a WAN so it is a LAN tool only.  If you have multiple sites to support and you want to build an Access solution, you're going to need something like Citrix or RDS.
Solo PascualIT SupportAuthor Commented:
Thanks so much for all the replies.
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

From novice to tech pro — start learning today.