Excel and sqlite. Trying to understand the options available

[sark666]

Some users here mention dhRichClient3, and I understand there is also http://sqliteforexcel.codeplex.com/

I did some searching and also found microETL and some further searching I also found palo for excel, which doesn't use sqlite but uses it's own db format. I'm not even sure if that uses sql syntax.

There's also http://sqliteforexcel.codeplex.com/

I know the advantages of sqlite in the sense of easy configuration, easily distributable (1 file) etc. But the main negative is not allowing multiple users.

The project I may be working on would be distributed locally and it would get around a lot of red tape to not have to set up full-blown sql servers.

However, there may be a need at some locations to have multiple people to have access simultaneously.

But this would be probably in the range of 5 people max. Can sqlite do that? I know microsoft access says it's limit is 255 but really it's way lower. Can sqlite allow a small number of users accesssing simultaneously or is it just 1 user?

I'm also much more comfortable in excel/vba and I'm just learning sql so I thought instead of going the access route maybe I could pick up sqlite and use this with excel.

So I'm wondering if anyone has used any of these and any thoughts they can provide.

 

[SydneyGeek]

One option: Start with Access. Use ADO to pull and push the data. If you have performance or concurrency issues migrate the databaes to SQL Server Express.
It's free, doesn't have to be located on a server, and gives most of the performance of the full version. And its size limit is 10 Gb instead of the 2 Gb for Access.

Denis

 

[sark666]

Thanks for the suggestion. I never heard of sql server express so I'll look into that.

Well, a couple of issues, I have very limited experience with Access so I thought an excel based solution might be better for me, and I'm not sure if sql server express has installation requirements. the nice thing here, is that it's easy to deploy an sqlite/excel based solution.

I doubt there will be lots of users accessing it concurrently. Maybe 4 or 5 at a time, so that's why I was trying to determine how feasible it is to use sqlite. I'm not sure if it's strictly one user at a time

 

[xenou]

From what I gather reading the many posts on this thread:
http://stackoverflow.com/questions/54998/how-scalable-is-sqlite

1) SQLite does support (a modest amount of) concurrency but you have to know what you are doing to get the best performance out of it.
2) You would not have to worry about SQLite if your users only needed to read the data (as opposed to modifying data). The file would only be locked if users are writing (INSERT/UPDATE/DELETE queries) to the database, not if they are reading it (SELECT queries). You would have some cause for concern if you think you will have more than one user will be trying to write to the database at the same time.

 

[sark666]

Thanks again. They could be a scenario where more than 1 person would be writing to the database. Again, this would be very few, maybe 2 - 5 people max. How does access deal with this? I know it doesn't scale well but I've seen Access handle mutiple people (but again few concurrently writing to the db) without issue.

My understanding is if you and I are both writing to the db, but we are updating different records then there would (hopefully) not be an issue. Say I am updating Employee X and you are updating Employee Y, then we wouldn't have an issue, whereas if we were both trying to change info on Employee X, then you are asking for trouble. Or maybe I'm wrong and with just even two people writing to the db regardless if they are different records is going to cause issues.

 

[xenou]

Regarding Access you are basically correct. This can be a somewhat technical matter. Access might lock a "page" of the file as stored on disk - which could be more than one record, but probably gives better performance on the whole. I believe this can be altered to more granular locking (record level locking). I don't know which is the default. In short, however, concurrency is supported and will work fine with with a modest number of users. I don't have experience with, say, dozens, or a hundred plus, users at once so I can't report on my experiences under these circumstances.

Regarding SQLite I don't know. From my very cursory reading on the subject it seems that earlier versions of SQLite (might have) locked the entire file even if only one user was writing to it. More recent versions of SQLite (appear to) provide support for more than one user to write to the file at once (but you probably need to be sure of what you are doing). Overall, SQLite is not really intended for such use - I believe it could easily handle hundreds of users who are reading from it simultaneously, and scales to millions of records without difficulty, but it's another story if you have a lot of transactional processing.

It's not necessarily a big deal. A write can take only milliseconds. The chances of two users trying to use the database for a write in the same millisecond is very minute, and even it it did happen, they'd probably be able to try it again immediately, with success. The main issue with these kinds of things is 1) that you don't keep the file locked for the entire time the user is using the database, but only when the user is actually editing or updating a record -- with that, you can try to design your database so conflicts are unlikely and resources aren't locked for long periods of time; 2) that your database doesn't freeze in a deadlock situation (good databases shouldn't - I don't think many do any more but it could depend on how you configure the database; 3) You as the DB adminstrator should be aware of what could happen if two users are working on the same record at once - if it's possible that one users changes could be overwritten by another and they may never know it (this can be something you're willing to allow, or something you're not willing to allow).

Sorry to be so long - locking is a big topic and there's much to say about it, with many differences in DBRMS implementations.

 

[SydneyGeek]

Adding to xenou's comments; try to avoid creating forms based on multiple tables, because having that form open will lock records in all of its source tables.
And if a user tries to run a bulk operation (update or delete, for example) on one of those tables the lock will prevent the operation.

Denis

 

[xenou]

For what it's worth, if you are working with SQLite probably most (all) of your database activity will have to be coded. That could be no big deal or a hindrance to your project depending on your comfort level. With Access, I usually find it very easy and effective to create my Database in Access with all the GUI tools it provides (drag/drop, wizards, dropdowns, and so on). Then when the database is finished, my code from Excel is just the typical CRUD (Create, Read, Update, Delete) you need to run your application. You won't have any such fallback GUI tools to go to with SQLite - though it appears to be a relatively straightforward and easy to use for all that. It's often a companion to embedded applications and its API is written in C/C++ and I hear about it a lot in terms of lightweight embedded applications. I personally do not think its common as a companion for Excel (yet) though it's been getting some fanfare and has some enthusiasts (notably, Tom Gleeson. It certainly is simple to install - copy the dll file to your computer and away you go. The link in the first post (I reckon) provides code that interfaces between SQLite's API and your Excel project so you can run commands on the database with VBA, and I imagine that Tom Gleeson's microETL links performs a similar role.

Personally I'm not familiar with it though I tried it once as a demo using vba/Excel - in which case, it worked without a hitch as advertised. One difference to most databases is that all dates are text and are stored in yyyymmdd.hhnnss style (which supports sorting and comparison operations) - you would want to look in the VBA functions you are using to see if they handle this conversion to dates as needed by SQLite (or if they provide a conversion function). I also tried out this "management tool" which might be a way to just type in SQL statements (such as CREATE TABLE ... blah blah blah):
http://sqlitemanager.codeplex.com/
But it wasn't much more than a place to type commands and see some sample data from the table (so don't get too excited! There was only two comments and was was that it was better than some paid for version which leaves me wondering how you could possible have any less functionality!! Oh well. It's also a rar file so I used peazip to unpack it).

 

[smartsource]

One trick for concurrency using sqlite...

Attaching multiple databases allows for concurrent writes to tables in each database. This is especially powerful when coupled with in memory databases and when combined with their online backup api to sync db's and commit to disk the data marshalling is pretty straight forward.

If setup properly you can theoretically scale and spawn up to 19 concurrent writes while keeping the main db readable at all times. If your anywhere beyond 3 concurrent writes you probably should look at other rdbm's.