17 people following this project (follow)

Overview

SQLite http://www.sqlite.org is a small, easy-to-use, open-source SQL database engine. SQLite for Excel is a lightweight wrapper to give access to the SQLite3 library from VBA. It provides a high-performance path to the SQLite3 API functions, preserving the semantics of the SQLite3 library calls and allowing access to the distributed SQLite3.dll without recompilation.

The current release has the following parts:
  • SQLite3_StdCall – A small and very simple C .dll that makes it possible to use the standard SQLite3 .dll from VBA. It just passes calls from VBA on to SQLite without any change in the parameters, but this allows the StdCall calling convention that VB6 and VBA is limited to.
  • Sqlite3.bas VBA module – Has all the VBA Declares, and does the parameter and string conversions. It exposes a number of SQLite3xxxx functions. These map as directly as possible to the SQLite C API, with no change in the semantics. Although I have not exposed the whole API, most of the core interface is included, in particular the prepared statement, binding, retrieval and backup functions. Date values are stored as Julian day real numbers in the database.
  • Sqlite3Demo.bas VBA module – Has tests that serve as nice examples of how to use the SQLite3xxxx functions.
  • SQLite3Demo.xls contains the two VBA modules.

Getting Started

  1. Download the release archive.
  2. Unzip the download to a convenient location.
  3. Open the Distribution\SQLiteForExcel.xls file.
  4. Open the VBA Editor (Alt+F11).
  5. Find the example test code in the SQLite3Demo module.
  6. Find the documentation for the SQLite functions here: http://sqlite.org/cintro.html. The complete query language for SQLite is documented here: http://sqlite.org/lang.html.

Related Projects

  • The SQLite home is at http://www.sqlite.org and the most recent version of the SQLite3.dll library can be found here http://www.sqlite.org/download.html.
  • To create User-Defined Functions (UDFs) for Excel using C#, VB.NET or F#, have a look at my Excel-Dna project.
  • For access to SQLite from .NET I recommend:
    • the official System.Data.SQLite is a full-featured ADO.NET driver with full Linq and Entity Framework support, or
    • the sweet-looking sqlite-net, a light-weight wrapper with attribute-based object-to-database mapping and some Linq support.

Support

Start a discussion or create a new issue here on CodePlex. You are also welcome to contact me directly at govert@icon.co.za with questions, comments or suggestions.

Last edited Nov 15 2011 at 8:09 AM by govert, version 18