I have a legacy desktop application using JET for the database. The application needs to be able to access the database file from a network drive. Should I migrate the data access to SQL CE (or SQL Lite), or leave it in JET? Maximum table row size is around 50000.
+1
A:
You definitely shouldn't use SQL CE in this situation. It doesn't allow multiple connections from a network share. For details, and ways to avoid corruption, see this page.
SQLite would work in this situation. Otherwise, you can always leave it in its current form, and use the Office 2010 Database Engine. Another option would be to use SQL Server Express.
Reed Copsey
2010-10-12 19:01:04
I'd stay with Jet 4.0/DAO 3.6 as it comes with the OS. The Office 2007/2010 database engine are one more dependency and item to install.
Tony Toews
2010-10-12 19:38:01
@Tony: Unless you need 64bit compatibility, in which case, Jet is not so great...
Reed Copsey
2010-10-12 19:42:21
How does 64-bit Windows manage Active Directory? If it doesn't have a 64-bit version of Jet, what's it using? Or is AD on 64-bit Windows 32-bit?
David-W-Fenton
2010-10-12 20:25:47
@David-W-Fenton: No idea what they're using - though I'd suspect they may have just left it as a 32bit app...
Reed Copsey
2010-10-12 20:28:06
Well, 64-bit Access 2010 obviously works with Jet MDBs, so there has to be some way to use Jet 4 files via 64-bit. It's not entirely clear to me whether A2007/A2010 actually use the ACE to access MDBs or if they switch over and use legacy Jet 4 (the reason for my suspicions on that are that an MDB created in A2007/A2010 still uses the DAO 3.6 reference -- yes, that's independent of the db engine version, but makes me wonder...), but I originally assumed ACE was handling interaction with Jet 4 files, and now wonder about it.
David-W-Fenton
2010-10-16 16:05:47
A:
50K records is quite easy for Jet so long as sorting and criteria fields are properly indexed. Also one rule in the Access world is to keep a connection open at all times to improve performance if multiple users are using the database file. I would assume this rule holds true in other environments.
If you have more than 15-25 users updating the data then I'd suggest going to a product such as SQL Server Express. Of course if you stay with Jet 4.0/DAO 3.6 it's built into the Windows operating system since Windows 2000 so it doesn't need any dependencies. SQL Server Express would require an install on a PC somewhere.
Tony Toews
2010-10-12 19:36:45