Attach a SQLite database into Excel’s memory via microETL

In my previous post I described the various methods of accessing SQLite databases from within Excel using microETL. Via comments on the post, Michael Römer suggested a change to how microETL loads into memory an external SQLite database (not only suggested, but also provided the C code changes to enable the change; thanks Michael).

The existing xLiteLoadUnLoad(filename[,unload]) function loads a SQLite file into “main” i.e. the primary database (which is usually a :memory: db) overwriting any existing data. Michael’s suggestion was to allow loading into another in-memory database with a different alias; thus keeping the main database intact but allowing the benefits of in-memory access to the externally attached database. This feature has now been added.

I’ve kept the existing xLiteLoadUnLoad as is, but added a new optional argument to the xLiteAttachDB function so.

  •  xliteAttachDB(databaseName,alias)  becomes xliteAttachDB(databaseName,alias,[loadInMemory=False]). The optional loadInMemory argument defaults to FALSE, so acts like the old version (i.e. issues a standard SQLite Attach statement). But if set to TRUE; the function will first Attach a “:memory: database” named as the alias, then will load the external database file into that in-memory database. Once this happens the on-disk database is not referenced, so any changes will not be reflected back to disk. To enable changes to be persisted to disk, I’ve added another new function…
  • xLiteDBSaveAs(alias,outDatabaseFile) will save a copy of the database named alias (with could be “main” if you wished to backup the default in-memory database) to the file outDatabaseFile. I’ve also added a …
  • xLiteDetachDB(alias) to issue a SQLite DETACH statement. You might ask why not simply use the SQL() function to issue DETACH (or indeed ATTACH) statements? Statements such as ATTACH/DETACH cannot be issued by the SQL() functions as its pre-processor (for table() functionality) wraps SQL statements in a SAVEPOINT (nested SQL transaction). You can however use the fastSQL() or xliteRawSQL() functions to issue such commands.

There’s another (this time breaking) change to the SQLScript TIMER  (see here …) command. The existing function used an ActiveX control (the Internet Explorer control as a provider for JavaScript timer functionality); ActiveX controls do not work under 64bit Excel, so I’ve reverted back to using Application.OnTime as my timer mechanism. The breaking change is the 3rd argument, which previously expected a value indicating the number of thousands-of-a-second to wait, now it represents whole seconds.

To download the latest version see the http://www.gobansaor.com/microetl page.

Update:

For another method of loading SQLite databases within Excel/VBA see my new .NET-centric micro ETL tool  http://blog.gobansaor.com/category/hammer/

Advertisements

One response to “Attach a SQLite database into Excel’s memory via microETL

  1. Pingback: Accessing SQLite databases from Excel via microETL | Gobán Saor