How do I access SQLite from VBA?

Facing the same question, I made a lightweight library to give direct access to SQLite3 from Excel VBA. The end result is a much simpler solution, with no intervening ODBC or OleDb/ADO layer, and the performance reflects the SQLite database performance and not that of the opaque wrapper. It's also nice because you need no registration of a COM component in the registry, you just copy two .dlls with your workbook and add a .bas module into your project.

A disadvantage of this approach is that the API is not the standard DAO or ADO interface, so you might need to make some wrappers, or convert some of your code to make it work. That also means you need some familiarity with the SQLite API to use it, but the SQLite documentation is very clear.

I have put an early version of the project on CodePlex: SQLite for Excel 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.

Any feedback would be much appreciated.

Update: The SQLite for Excel project now lives on GitHub.

How to access sqlite from vba macro from here (original article in chinese) :

Software environment:

1) Win7 (32 bit) 2) Excel2007 (with VBA function)

Steps:

1) First, http://www.zsplat.pwp.blueyonder.co.uk/programming/sqlite-3.5.7-odbc-0.65.zip download and install the SQLite ODBC driver.

Note: If you use Win7, then you need to Adminitrator permissions, otherwise the installation fails. The simplest step is to use the Administrator login, and then install the SQLite ODBC driver.

2) Open the Excel VBA code editor window, the menu bar [tool] -> [reference], adding Microsoft ActiveX Data Objects 2.7, the purpose is to use VBA, and database connectivity.

3) Use the following code to connect SQLite database:

Dim conn As New ADODB.Connection Dim dbName As String 'Define the connection string dbName = "Driver = {SQLite3 ODBC Driver}; Database = D: \ yourdbname.db" 'Open the database ff.Open dbName ff.Execute "create table a (a, b, c);" 'Other operations on and VBA to connect to other DB like Access the same. 'Close connection ff.Close 

There're some DLLs that you can use to access a SQLITE Database from Visual Basic using adodb. I think here is the info that you're looking for. I never tried it, but the link can be usefull for you.

Good Luck.