Replacing Microsoft Access with a different portable database and interface

Question

Our company is upgrading all work computers soon and no longer supporting MS Access. Unfortunately, a lot of important database is stored in a plethora of Access databases with many differing styles of interfaces. Storing it all on a database server is out of the question. What is a good portable (non-server) database system that I can use?

The one I'm leaning toward is SQLite, but I'd also like to be able to write interfaces to query the data. If I use SQLite, what is a good way to write a query interface that will work on a lot of Windows machines natively?

EDIT: Data migration will not be a problem. I'm more worried about interface migration. What is a good way to write a query interface for one of these portable database systems?

EDIT2: It seems that there's some confusion as to what I mean by "query interface". I don't need a complete query system. I'm simply looking to create a simple frontend through which I can search the Database by table and by fields.

Answer 1

Judging by the title of your question, you are concerned about what database (i.e. data container) should be used in place of Microsoft Access. However, viewing your question and the ensuing answers and comments, your greater concern is actually what to use to design the Application/user interface, what you are referring to as the "query interface".

Here's a couple things you need to realize:

1) SELECT queries allow you to extract data from a database. However, the data has to get into the database somehow. At no point have you specified whether or not your so-called "query interface" is also used for data entry. If it is in fact used for data entry, as I suspect it is, then you should concern yourself with how your going to design your "application", not your "query interface". Data entry often requires a certain amount of guidance and logic that is defined outside of the database. This logic includes (but is not limited to) things like validation rules, input masks, parent child relationships that are enforced by the GUI design. This is essentially an application, not merely a "query interface". Applications are usually designed to make data entry easier than having the user enter data directly into the tables. But even here you must realize that ultimately a user can't even enter data directly into a table without some type of application GUI, whether that GUI is something you've designed or not.

2) Microsoft Access is a Rapid Application Development (RAD) tool that allows you to quickly and easily design and save queries, forms, and reports. It appears to me that you're real question is, What RAD tool other than Microsoft Access can I use to develop queries and forms for viewing and entering data in small desktop databases? And what database would you recommend we use together with the RAD tool you're recommending?

Assuming I've properly defined your actual question, allow me to give my own viewpoint on this question. First, as has already been pointed out in another answer, as long as the clients are running MS Windows, you don't really need to move away from using Access/Jet/ACC as your database (data container, the part that holds the tables). As long as MDAC is installed an Access database should be "machine readable".

It seems to me the more important question here is what RAD tool should you use in place of Microsoft Access? After you answer this question perhaps it will help you answer the first question, of what database should be used. In answer to this question, let's be honest. While Microsoft Access has it's shortcomings, you probably won't find any other RAD tool that allows you to so easily create Forms for data entry. FileMaker Pro is one of the only competing products that I know of.

Yes, you could use Visual Studio .Net to create .exe runtimes. These runtimes can include forms for viewing and entering data as well as reports. I think you'll find that the learning curve is pretty major, especially if you are not yet familiar with ADO.Net which is very different from DAO or ADO Classic. If you want to give your users ways to create their own queries/filters or reports you'll have to design this functionality into your GUI since Visual Studio doesn't give you the easy wizards and design tools you'll find in Microsoft Access. While Visual Studio is far more powerful than MS Access, you need to realize that the development time for even the simplest of applications is probably going to be much higher, especially when you are first learning.

Are there any other RAD tools you could possibly use? I know little if anything about the different design tools listed here but this might be a good place for you to start: http://en.wikipedia.org/wiki/List_of_rapid_application_development_tools#Desktop_Rapid_Application_Development_Tools

Answer 2

SQLite is a great option and will extend your reach to many platforms beyond Windows (iOS, OSX, Android, etc.). I recommend this. If you want to keep using Microsoft products, SQL Server Express or Compact Edition is another possibility.