22
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!).
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
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.