Best Database for Inventory

Solo Pascual
Solo Pascual used Ask the Experts™
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.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017
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.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
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 LLC
Most Valuable Expert 2014
Top Expert 2010

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.

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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.
Distinguished Expert 2017

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 Support


Thanks so much for all the replies.
Distinguished Expert 2017


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial