Take the 2-minute tour ×

Someone asked me this the other day, and I couldn't think of a good answer. Platform portability is completely irrelevant to the project.

In fact, Jet has some features that SQLite does not, namely foreign keys.

So can anyone think why SQLite should be used instead of a Jet database?

share|improve this question
Actually, SQLite allows you to implement foreign keys with check constraints. –  Charles Duffy Feb 3 '09 at 6:01
SQLite 3.6.19 (released in November 2009) added support for foreign key constraints. –  Paul Lefebvre Jan 8 '10 at 17:12
And why not use FireBird? –  reinierpost Feb 27 '11 at 11:01

10 Answers 10

up vote 15 down vote accepted

Contrary to what other people are saying, Jet is not dead and far from it: ACE is the new version of Jet and it's pretty robust and backward compatible.

Both SQLite and Jet/ACE have their strengths and weaknesses and you need to get more information about the specific points that are important to you and your application.

  • In either case you can redistribute the engine.
  • Jet/ACE is a bit more integrated and supported out of the box in MS tools and Visual Studio.
  • Jet/ACE has more granular locking, which may be important if your app allows multi-users or needs multi-threaded access to the database.
  • Jet/ACE has more features in terms of what you would expect from a database (joins, unions and complex queries).
  • Jet/ACE has a simple migration path to SQL Server, so if your database needs become big, you could move to SQL Server fairly easily.
  • SQLite is cross-platform, so if your app needs to be ported to Linux/Mac under Mono then SQLite is a better choice.
  • the SQLite engine is tighter so redistributing may be easier.
  • datatypes are quite loose in SQLite.
  • SQLite has more liberal redistribution rights (since you can basically do whatever you want with it).

People who say that Jet corrupts databases are stuck in 1995.

In the end, unless your application has some very specific requirements that are pushing the boundaries of either database engines, then it probably doesn't matter which one you chose.
Just use the one that easiest for you to include in your project.

share|improve this answer
SQLite also supports joins and unions: sqlite.org/lang_select.html. Maybe you meant that it does not enforce the integrity of foreign keys? –  Wim Coenen Aug 11 '09 at 22:28
A db that doesn't enforce foreign-key constraints is not really a database in any real sense. –  David-W-Fenton Jun 9 '11 at 0:20
@David: it may not matter in some cases and if you're using an ORM, it will generally handle these for you. There is a place for all of these databases, you just need to pick the one that makes the most sense for your project. –  Renaud Bompuis Jun 9 '11 at 10:40
Foreign-key constraints MUST be enforced in the database engine itself, not at some level above it (either at the application level or in a tier between the app and the database). I am shocked that at this late date anybody would attempt to justify the enforcement of relational integrity anywhere else than in the database engine itself. It just makes no sense to me at all. –  David-W-Fenton Jun 12 '11 at 20:26
Open Office lets you open Jet DB so its cross platform too. –  Lemuel Adane Sep 5 '11 at 0:09

SQLite is superior to Jet for the major reason that SQLite is ACID-compliant whereas Jet, unfortunately, isn't. If data integrity is an issue, SQLite offers a much more "robust" platform for your data storage requirements. See "SQLite Is Transactional" and "Atomic Commit In SQLite" for more details.

SQLite does indeed lack a few features (such as foreign keys), however, these are primarily due to SQLite being specifically developed as being an extremely small and lightweight database that is also serverless.

The serverless aspect of SQLite is also a major benefit over Jet in that nothing needs to be installed on the machine that will run your database. For example, I have used SQLite in an ASP.NET web application and all I needed was the SQLite DLL (in this case is was the excellent System.Data.SQLite drop-in replacement) in my application's "bin" folder, and my database in the application's "App_Data" folder. I could then upload these files to my webhost, and it all "just worked". This is without having to actually install or register anything on the target machine.

A small dowside of SQLite is due to the database being file-based. Database writes will lock the entire database file rather than a specific row or table, whereas Jet will offer you a more granular level of locking. Another small issue, based on the same file-based reasoning, is concurrency, however Jet itself does not offer a high level of concurrency either.

share|improve this answer
That's not a downside of SQLite. Concurrent/network file writing on Windows is inherently unreliable (sooner or later you'll find intermittent data corruption has been occurring for the last six months so it's in all your backups too), so multiuser systems need a proper database server. SQLite and Jet are for single user scenarios. –  rwallace Apr 29 '10 at 14:08
What is the basis for the claim that Jet is not ACID-compliant? Jet by default uses implicit transactions for everything (unless you explicitly tweak the database engine to not use them), and you have full access to transactions for commit/rollback via DAO. So, I think the assertion is completely baseless. –  David-W-Fenton Mar 30 '11 at 22:14
The issue of "nothing needs to be installed" applies to Jet, as well, at least for all versions of Windows starting with Windows 2000, where Jet 4 is installed as part of the OS. –  David-W-Fenton Mar 30 '11 at 22:15
@David-W-Fenton The basis for the claim is this article: msdn.microsoft.com/en-us/library/aa190103.aspx, direct from MSDN itself. Note the "Important" section near the bottom that quite clearly states that the JET engine does not support durable transactions, something that is required for ACID-compliance (indeed, durability is the "D" in ACID). –  CraigTP Aug 4 '11 at 14:38
In 16 years of professional Access development I've never enountered a problem with the "D" in ACID. This is one of those false tests, seems to me, a theoretical construct that doesn't necessarily reflect real-world requirements. –  David-W-Fenton Aug 5 '11 at 20:27

This is still a question that comes up from time to time. I'm considering all the pros and cons for both right now for a new project. I write a lot of financial applications that deal with money values so one of the most important "pros" for Access/JET/ACE/whatever-they're-calling-it-today (I just call it Access) is its strong types. Don't underestimate the power of having strong types when you're dealing with money - Access is the only single-file database I've seen with support for a money/decimal type that can store REAL money values.

One of my main retail products uses SQLite as a backend and I can tell you that I've had virtually no problems with it deployed even in the craziest situations. SQLite is definitely designed to be single-user but I have a LOT of customers using it over SMB. You do have to write checks into your software to check for return values of SQLITE_BUSY when running queries but if you wrap that up with an auto-retry it "just works".

There are only a few reasons I'd choose Access over SQLite - one is data types. If I'm ever writing software that has to do math on money values (tax, etc), I'll use Access. The only other compelling reason to use Access is an upgrade path to SQL Server. Since I've never used SQL server in my life (and don't plan to), it's not a big deal.

In the end, both are extremely robust databases - I wouldn't hesitate to use either in a production environment. Just remember to use the right tool for the job and sometimes that means a database server (PostgreSQL has treated me right over the last 13 years, that's for sure!).

share|improve this answer
Again, SQLite's real weakness is loose typing. But that's only a weakness if you need strong types, and most folks probably don't. MS-Access/Jet/Whatever is the only single-file zero-admin database that I've found with support for one data type that is exceptionally important in most of the software I write -- the money/decimal type! When you need it, nothing else will do! –  Mitchell V Mar 31 '11 at 1:39
No -- its proper handling of fixed decimal values that's important for financial applications. You can only bill, pay tax etc. using whole cents. Its not valid to bill someone $.0033333333 since they cannot pay this without resorting to tinshears. Since you can normally return individual items on an order or invoice they must be rounded to a value in cents. Floating point, or plain integers dont hack it in this space. –  James Anderson Jun 8 '11 at 3:25
Uh, isn't that an application-layer issue (rounding), rather than a data storage issue? Jet/ACE's Currency type is limited to 4 decimal places, but you're perfectly free to round to whole cents before storing the numbers in it. –  David-W-Fenton Jun 9 '11 at 0:23

Jet is no longer supported. SQLite is also easier to install since it's one dll that can easily be packaged with your app. Using SQLite also can prevent vender lockin, just because language or cross platform portibility isn't a concern now doesn't mean it won't become one later. For more on Jet's retirement see http://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine

share|improve this answer
The Wikipedia article is filled with bad information. Jet was removed from the MDAC because it was PART OF WINDOWS (from Win2K on). The ACE is just a new version of Jet, fully backwardly compatible with Jet 4 and earlier files. The ACE has a long future, as long as Access exists. –  David-W-Fenton Feb 4 '09 at 5:10
This from Microsoft: "Microsoft has deprecated the Microsoft Jet Engine, and plans no new releases or service packs for this component." (msdn.microsoft.com/en-us/library/ms692882.aspx). –  onedaywhen Feb 4 '09 at 8:50
Jet 4.0 is included as part of Windows 2000, XP, VIsta and 7. YOu don't need to install it. So how can SQLLite be easier to install? Granted copying a DLL isn't a difficult install. –  Tony Toews Nov 24 '09 at 19:46
"Jet is deprecated" does not mean "Jet is not supported". Jet is part of Windows, it is supported just as much as any other part of Windows. "Deprecated" does mean Microsoft discourage its use and they would rather not enhance it. –  MarkJ Dec 22 '09 at 17:17
Here's the page on the Access official blog where they confirm that ACE is Jet. It also says Jet is a Windows system component: that means it is supported. blogs.msdn.com/b/access/archive/2005/10/13/480870.aspx –  MarkJ Jun 23 '10 at 1:01

Cost is not an issue. If your frontend is built in something other than MS-Access, users of the application do not have to pay any fees to have the Jet drivers installed. Visual Studio would include those drivers during your build (At least the pre .NET versions did.).

I'm guessing you have no personal preference and are equally skilled in development in either environment. If your users have already MS-Access licenses and they would like to be able to write their own reports (Oh, God forbid any non-hacker attempting such a tremendous feat!), use Jet.

share|improve this answer
Given that MS Jet 4.0 is included as part of Windows 2000, XP, Vista and 7 you don't actually need to include the drivers with your install. –  Tony Toews Nov 24 '09 at 19:48

We used Jet for a long time and recently switched over to SQLite. Why?

1: When a database gets anywhere near 2 GB or with frequent use, it becomes corrupted in Jet eventually. This has caused us a lot of grief! This has not been fixed in Jet or ACE, though Microsoft has a separate tool that can supposedly fix the database files.

2: Microsoft deprecated Jet years ago, in favor of ACE, but if you read the details, Microsoft itself says that ACE is NOT a replacement for jet, and really wants you to use SQL Server instead.

3: Jet is no longer a standard part of Windows, but part of Microsoft Office, though you can download and install the distributable. However, you can not have both the 32 and 64-bit engines installed at the same time. If you have Office 2007 32-bit installed, and you try and install the 64-bit ACE engine, it tells you need to uninstall Office 2007 first.

So for these reasons we just decided enough is enough. Installing SQL Server is not a solution because it is a big complex invasive install and not very portable.

Our C++ software directly supports SQLite via the sqlite3.c file, and it works very well. I have implemented antive interfaces for OCILIB, Oracle, SQL Server, MySQL etc and this was one of the easiest. It is also much faster than Jet and the resulting files maybe a third of the Jet size. We do have some VB6 and VBA and .NET code that also need to use our database files and for that we use the SQLite ODBC driver (just Google it). Works well.

SQLite works fine in both 32 and 64-bits. And if you read up on it you will see it is seriously tested and amazingly stable. It also supports more standard SQL and is closer to Oracle/SQL Server than Jet is.

share|improve this answer
Your #3 is WRONG. Jet 4 is still a part of Windows, and has been since Windows 2000 -- it's used by Active Directory. I do expect that to change soon, though, given the 64-bit issue (i.e., Jet 4 will never be 64-bit, which means any compopnent depending on it have to be 32-bit). Jet 4 IS NOT managed by the Office development team, but by the Windows team. It is the ACE, the fork from Jet 4 (could have been called Jet 4.5 or Jet 5) that is owned by the Access development team (not the Office team), and will continue to be developed on its own path. –  David-W-Fenton Jun 9 '11 at 0:26
Jet 4 is not seeing any further development, so is a stable target. It is also fully supported and updated by Windows Update (which means it gets security updates, if necessary). But I'm not sure I'd assume that it will continue to be part of the OS for much longer. –  David-W-Fenton Jun 9 '11 at 0:26
The 2GB limit applies to ACE as well as Jet 4, and is unlikely to ever change, seems to me. I have never seen it as an issue, since from my point of view, the applications for which a Jet/ACE data store is appropriate are never going to get that large. If I had an app where the data store started approaching even 1GB I'd be upsizing immediately. If you're running into the 2GB limit in production use, it suggests two things to me: 1. you're not doing any db maintenance (regular compact), and 2. you chose the wrong data store in the first place. –  David-W-Fenton Jun 9 '11 at 0:30

SQLite is the new Jet. Even if cross-platform is irrelevant to you, it may not be to your customers. Using Jet locks them into Windows and to a no longer supported DB, neither of which are good things. And SQLite works with just about any development environment out there.

Jet is known for having strange corruption issues, so I tend to stay away from it in general.

You can certainly create foreign keys in SQLite, and as of SQLite 3.6.19 foreign key constraints have also been added.

share|improve this answer
Jet does not have "strange corruption issues." When improperly managed, files can corrupt. The causes are quite predictable, in fact, and easily avoided. –  David-W-Fenton Feb 4 '09 at 5:11
"When improperly managed, files can corrupt. The causes are quite predictable, in fact, and easily avoided" -- I think the point is that more sophisticated engines manage things properly so that you don't have to. –  onedaywhen Feb 4 '09 at 9:00
Any corruptions issues on a database, manageable or not, is reason enough not to consider it. –  Paul Lefebvre Feb 4 '09 at 14:38
None of my clients experience corruption with their Jet databases. It's been literally years since any of them had any problems. –  David-W-Fenton Feb 5 '09 at 2:19

Off the top of my head, it's free and cross platform; but more important... do you think it is more stable and scalable that Jet/MS Access/.mdb? Will it be longer lived that its successor (ACE/.accdb)

If it is being used by more than just a couple people, I don't bother with Jet. I go straight to MS-SQL (even the free version of it). It's just not worth the pain of a corrupt DB (which Jet is known for - although maybe they fixed it - I don't want to be their test case though).

share|improve this answer

If you program well in Python, Pearl, Lua or many other languages, SQLite would be the natural choice.

share|improve this answer

How about a regfree implmentation Checkout the StarSchema-OLAP demo. Hard to believe the performance.


share|improve this answer

Your Answer


