@jsmith said:
So, Jet (the default Access database engine) could never be used for anything in production reliably? Someone better tell that to Microsoft. They've been using it successfully with their DHCP and WINS products for 15 years. I don't see any widespread panic, just a bit of extra maintenance. Microsoft Exchange also used a modified version of Jet successfully for years.
The Microsoft examples could just as easily be SQL based without extra expense. MSDE (or the new SQL 2005 Express Edition) is totally free and 100% code compatible with SQL Server. They chose Jet becuase it is simple and popular.
I agree all Access apps should have the front end/db split. It is almost impossible to roll out updates and fixes otherwise. Backups are a huge issue, but there are solutions to back up a Jet database out there. Personally, I don't see anything wrong with a small internal app being an Access front end (as an ADP file, not an MDB) connected to MSDE or SQL Server on the back end.
One small gotcha with the jet provider/Access is that MS limits it to approximately 10 concurrent connections. I say approximately because it's changed back and forth between 10 and 15 in various versions of MDAC over the years. If you have a SQL Server, my recommendation would be to just use the SQL Server and forget about Access entirely... unless you really enjoy having weirdness start happening when it begins to scale.
Jay