hello,
I am trying to do a simple shared database for a small workgroup of people, who use Excel for their data. I am thinking SQLite, because I don't really want to go server, as there are only 5 users and they're all local, and the database is very low intensity.
What's the best tool for accessing SQLite via Excel?
Thanks.
Get yourself a OleDB provider for Sqllite, then connecting to the database you could use ADO (old school), or you could use a COM+ dll which you could activate via CreateObject() a good place to start is http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers and one ive actually used before http://dimzon541.narod.ru/sqlitex/
Why not create your database in Microsoft Access? The Jet MDB drivers are going to work great with Excel and be installed as part of Office.
Microsoft has spent years trying to get a shared file database to work on a LAN. In short, it's not possible, but they've managed to get it to work nearly 99% of the time, and they have tools for fixing it when it breaks. SQLite is designed as an embedded databases. While it does attempt to do file locks, it's not designed to be a great multi-user system. Like all shared file databases, it too can have problems with multiple users changing data at the same time. Additionally, the Jet (mdb) database has the added feature of being very easy to later upgrade to a Microsoft SQL Server - either their embedded/free version, or a full licensed server. You can't one click upgrade SQLite in the same way.
With any database, make sure you make a nightly backup part of your plan.
I suspect the OLEDB provider may be best. But here's link to page showing how to use the SQLite ODBC driver with Excel: http://help.lockergnome.com/office/SQLite-Excel-ODBC--ftopict704125.html
Also, some interesting general articles on this blog about using Excel/Sqlite combination: http://blog.gobansaor.com/category/sqlite/
Facing the same question, I made a lightweight library to give direct access to SQLite3 from Excel VBA. It means I have a much simpler solution, with no intervening ODBC or OleDb/ADO layer, and the performance reflects the SQLite database performance and not that of the opaque wrapper.
I have put an early version of the project on CodePlex: SQLite for Excel provides a high-performance path to the SQLite3 API functions, preserving the semantics of the SQLite3 library calls and allowing access to the distributed SQLite3.dll without recompilation.
Any feedback would be much appreciated.
dhSQLite from datenhaus looks promising. I tried their examples, everything looked OK. I have not had time to try with our sqlite DB yet.