This is the original xLite project page, for the latest and greatest xLite, an Excel combinded with SQLite,combined with Python, datasmithing platform see http://www.gobansaor.com/xlite.
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.
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
[...] April 10th, 2007 I’ve just uploaded my first Proto module; a rewrite of a VBA module I’ve been using for many years to provide me with a bridge between the ease of use of desktop user-focused tools and the power of a SQL enabled database. In the past that database was Oracle (and occasionally MS Access) and the desktop tool was of course Excel; now the database is SQLite ,the client tool is still Excel but this time in a supporting role to Proto. Over the next few weeks I’ll document and publish both the Proto and the pure-Excel version of the this code, in the meantime check-out the work-in-progress project page for xLite (http://gobansaor.wordpress.com/projects/xlite). [...]
[...] the lack of a fast and powerful GUI tool has been a problem. Admittedly, in my Excel/Sqlite xLite suite GUI generation is not a problem as VBA forms are both fast to develop in and feature rich, but [...]
[...] a new COM wrapper for SQLite from German company Datenhaus. I’ve started to use the COM free PSSQLITE.DLL in my Excel VBA projects but I may concider using the Datenhaus dhSQLite library in certain [...]
[...] So what to build for my Proto debut? Well looking at Proto one of the things that struck me was the lack of a relational join feature. In a previous post I explained that other mashup tools tend to use XML as their “pipe transport protocol” rather the table-oriented nature of EntrySets. But these entrysets cannot be manipulated using relational operators, not even the ability to join two tables by a shared key. Although the tool doesn’t offer this facility it does offer the flexibility to create a component to solve the problem and somebody has already done so; simulating an Excel VLOOKUP “join”. But I wanted the full power of a relational engine, INNER and OUTER joins, UNION and MINUS, aggregations via GROUP BY, access to very large datasets and I already had the answer; an Excel VBA module, xLite. [...]
[...] :memory: can also be useful, particularly when that memory is shared with Excel. I’ve already experimented with embedding SQLite into Proto, based on VBA code I’ve used in the past for integrating SQLite into Excel. I [...]
[...] also been busy re-factoring my VBA SQLite and Amazon S3 code with the intention of publishing them as an Excel based micro-ETL platform. [...]
This seems to be exactly what I need for a project using Excel. My users do not have Access, and using SQLite would solve that dependency.
But.. I cannot find any example of how to use your VBA modules ? Can you give even ONE simple example of which function to call ? The only public function you have in your modules require a lot of parameters, but they are not explained. I do not need to work with joins (usually). All I need is a simple “hello world” like example where you select 2-3 fields from a database, into Excel, using VBA.
I would be most grateful for this.
@ T Jensen
The 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…
A 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.
There’s a whole bunch of others over on
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers
Tom
**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!
Tom