SQLite as a lightweight Access-Replacement?

May 16, 2012 at 9:18 PM
Edited May 16, 2012 at 9:22 PM

I am new to SQLite and this small database actually intrigues me. Spontanously, I am wondering if it is possible and reasonable to use SQLite as a database on which Excel would rely on: 

I have an Excel-File which is abused as a database of more than 100K datasets with more than 60 columns. Now I have the issue that we will exceed the limits of our computer's RAM soon. 

There are a few tables which extend the main table, basically just VLOOKUPs, ugly, yes, but it grew like this over years. 

Now, I basically could use MS Access which would perfectly intertwine to Excel. But then again, although Access is capable of handling of more than one user (just few users, like 10 max), it is really ugly to maintain so.

Do you think SQLite could be a way for some different reports in different excel files that simply put, just import queries from SQLite?

May 17, 2012 at 9:26 AM
Edited May 17, 2012 at 9:27 AM

Having Excel as your front-end, and storing the data in a .mdb or SQLite database makes a lot of sense. Between using an Access .mdb file with Jet/DAO to talk to it from VBA, or using SQLite, I'd recommend the one you are most familiar with.

Jet/DAO using RecordSet.Seek on an index has fantastic performance. If you have multiple users accessing the database over a network share (maybe a handful of users), the .mdb option would be better.

SQLite has no database size limit, and is cross-platform.