WORK IN PROGRESS……
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.
Proto Custom Module
http://www.protosw.com/mods/lib/view/446
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. [...]