xLITE – Excel/Proto SQLite utility

This is the original xLite project page, for the latest and greatest xLite (now called microETL), an Excel combined with SQLite,combined with Python, datasmithing platform see… http://www.gobansaor.com/microetl

“Old” xLite is VBA Module to allow access to the power of SQLite from within a VBA macro environment. A version exists for Excel and for Proto.

The old xLite code should be looked as an example of using the Pivitol Solutions SQLite Wrapper not as a library. The example in this case is joining two ranges (or Proto EntrySets) using SQL in either an inner or outer join with an optional block of SQLite commands to follow.

xLiteExecute(DatabaseName As String, joinOuter As Boolean, tableA As Variant, tableB As Variant, stmtTable As Variant, outStruct As Variant, outSelectStmt As String, outError As String) As Long

DatabaseName: the name of the SQLite database to use, if left blank, uses an in-memory database.
joinOuter: if true performs a left outer join
tableA: the range pointing at the first (left) “table”
tableB: the range pointing at the right table
stmtTable: (optional) range pointing a list of SQLite commands to execute after the successful join
outStruct: the resulting joined table is output to this variant array (or EntrySet in Proto)
outSelectStmt: the actual SQL statement used to perform the join, for debugging or educational purposes
outError: what error if any

The function also sets a number of views (again most likely of use for educational purposes):
joinABView = based on the last successful join SQL be it inner or outer
innerJoinABView = as above but as an inner join
outerJoinABView = this time as an outer join

Think of the code as educational rather than a solution; if you need a more traditional library then…

**NEW** SQLite for Excel http://bit.ly/bkgP1h from the same guy who gave us ExcelDNA http://bit.ly/9WdKYH If this had existed a few years back this would have been the one I would have used. It simply exposes the C SQLite3 functions (or at least the vast majority) directly to VBA, simple, yet brilliant!

Another good option might be the Polish litex
http://www.assembla.com/wiki/show/litex , open source.

The closest to ADO style functionality would be dhSQLite (from Germany
http://www.thecommon.net/2.html ), it’s not open source but it’s free
and top class.

There’s also http://www.sqliteplus.com/, not free, but also very good.

If you have basic C skills and you’d like to roll-your-own VBA
friendly SQLite library, try http://tannertech.net/sqlite3vb/.

I use none of the above, wedded as I am to the old but venerable
Pivotal Solutions wrapper (no longer maintained, link dead but can be
downloaded from http://www2.gobansaor.com/xLite/pssqlite.dll ). This
is a low level wrapper, but I like it as it wraps the sqlite3.dll
rather than recompiling the library and it’s simple enough that I can
maintain it myself with my somewhat limited c skillset. Not
everybody’s cup of tea though.

For good old fashioned ODBC there’s http://www.ch-werner.de/sqliteodbc/

For ADO.NET there’s http://sqlite.phxsoftware.com/

There’s a whole bunch of others over on
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

Proto Custom Module

http://www.protosw.com/mods/lib/view/446

UPDATE: Opps —  Feb 2009 – seems link is broken

Proto Examples

http://www2.gobansaor.com/xLite/Join Two Tables via SQLite.proto

http://www2.gobansaor.com/xLite/Join to External SQLite database.proto

VBA Code:

http://www2.gobansaor.com/xLite/xLite.zip

DLLs used:

SQLite VBA friendly wrapper SQLITE3.DLL from Pivotal Solutions. The ZIP contains both the source and the DLL. No COM component so no registry entries required, just needs to be placed on your PATH or in your current working directory. Dynamically loads the SQLITE3 library at runtime so to get the latest SQLite version just swap in a new SQLITE3.DLL, no compile necessary. Also supports Sqlite(2).

Pivotal Solutions link no longer works use http://www2.gobansaor.com/xLite/pssqlite.dll to download DLL

http://www.pivotal-solutions.co.uk/downloads/company/pssqlite.zip

To download the latest SQLITE3.DLL go to http://www.sqlite.org/download.html

For ease of deployment both DLLs along with a sample NWIND.DB (a sqlite version of the well known MSAccess Northwind database) can be downloaded from http://www3.gobansaor.com/xLite via a function within the xLite module. When the module fails to find PSSQLITE.DLL it will call this function and prompt you to download the files.

Or download from this site using the following links..

http://www2.gobansaor.com/xLite/pssqlite.dll

http://www2.gobansaor.com/xLite/sqlite3.dll

http://www2.gobansaor.com/xLite/Nwind.db