SQLiteDB VBA Library is a set of VBA helper functions for the SQLite engine. The primary motivation behind this project is to provide convenient access to extended introspection features. Because of its generality, ADODB/ADOX libraries provide only limited metadata information. TheIntrospection subpackage of this library relies on the generic SQL querying mechanism and specialized SQL queries. It facilitates access to complete information about both the features of the active engine used and objects/attributes of the attached database. The library uses the ADODB package and relies on the Christian Werner'sSQLiteODBC driver (I use a custom compiled binary that embeds a recent version of SQLite, as describedhere).

Class diagram

Note, this post only covers the core functionality. Further documentation is available onGitHub, and complete source code, tests, and examples are available from theproject repository.

The three classes at the top of the diagram form theIntrospection subpackage responsible for metadata-related SQL code, withSQLiteSQLDbInfo being the top-level object. It implements a portion of the functionality, proxies functionality provided bySQLiteSQLDbIdxFK, and encapsulatesSQLiteSQLEngineInfo.

SQLiteSQLDbInfo

'@Folder "SQLiteDB.Introspection"'@ModuleDescription "SQL queries for retrieving SQLite database metadata."'@PredeclaredId'@Exposed'@IgnoreModule ProcedureNotUsedOption ExplicitPrivate Type TSQLiteSQLDbInfo    Schema As String    Engine As SQLiteSQLEngineInfoEnd TypePrivate this As TSQLiteSQLDbInfoPrivate Sub Class_Initialize()    this.Schema = "main"    Set this.Engine = SQLiteSQLEngineInfoEnd SubPrivate Sub Class_Terminate()    Set this.Engine = NothingEnd Sub'''' @ClassMethodStrict'''' This method should only be used on the default instance'''''@DefaultMember'@Description "Default factory"Public Function Create(Optional ByVal Schema As String = "main") As SQLiteSQLDbInfo    Dim Instance As SQLiteSQLDbInfo    Set Instance = New SQLiteSQLDbInfo    Instance.Init Schema    Set Create = InstanceEnd FunctionPublic Sub Init(ByVal Schema As String)    this.Schema = SchemaEnd Sub'@Description "Exposes SQLiteSQLEngineInfo introspection queries"Public Property Get Engine() As SQLiteSQLEngineInfo    Set Engine = this.EngineEnd Property'@Description "Generates a query returning the list of attached databases"Public Property Get Databases() As String    Databases = "SELECT name, file FROM pragma_database_list"End Property'''' @Proxy'@Description "Generates a query returning all non-system database objects."Public Function GetDbSchema(Optional ByVal Schema As String = vbNullString) As String    GetDbSchema = SQLiteSQLDbIdxFK.DbSchema(IIf(Len(Schema) > 0, Schema, this.Schema))End Function'''' @Proxy'@Description "Generates a query returning all non-system database objects, but triggers"Public Function DbSchemaNoTriggers(Optional ByVal Schema As String = vbNullString) As String    DbSchemaNoTriggers = SQLiteSQLDbIdxFK.DbSchemaNoTriggers(IIf(Len(Schema) > 0, Schema, this.Schema))End Function'''' @Proxy'@Description "Generates a query returning triggers"Public Function Triggers(Optional ByVal Schema As String = vbNullString) As String    Triggers = SQLiteSQLDbIdxFK.Triggers(IIf(Len(Schema) > 0, Schema, this.Schema))End Function'''' For some reason, running SELECT * FROM <schema>.pragma_integrity_check'''' with several attached databases gives the result as if <schema> is'''' ignored and all attached databases are checked. Prefer to run this'''' check when the only attached database is the one being checked.'@Description "Generates a query running integrity check."Public Property Get CheckIntegrity() As String    CheckIntegrity = "SELECT * FROM pragma_integrity_check"End Property'''' For some reason, running SELECT * FROM <schema>.pragma_foreign_key_check'''' with several attached databases gives the result as if <schema> is'''' ignored and all attached databases are checked. Prefer to run this'''' check when the only attached database is the one being checked.'@Description "Generates a query running integrity check."Public Property Get CheckFKs() As String    CheckFKs = "SELECT * FROM pragma_foreign_key_check"End Property'''' @Proxy'@Description "Generates a query returning database tables."Public Function Tables(Optional ByVal Schema As String = vbNullString) As String    Tables = SQLiteSQLDbIdxFK.Tables(IIf(Len(Schema) > 0, Schema, this.Schema))End Function'''' @Proxy'@Description "Generates a query returning all foreing keys in the SQLite database"Public Property Get ForeingKeys() As String    ForeingKeys = SQLiteSQLDbIdxFK.ForeingKeys(this.Schema)End Property'''' @Proxy'@Description "Generates a query returning all indices in the SQLite database"Public Function Indices(Optional ByVal NonSys As Boolean = True) As String    Indices = SQLiteSQLDbIdxFK.Indices(this.Schema, NonSys)End Function'''' @Proxy'''' See the called class for details'@Description "Generates a query returning child columns for all foreing keys and corresponding indices."Public Property Get FKChildIndices() As String    FKChildIndices = SQLiteSQLDbIdxFK.FKChildIndices(this.Schema)End Property'''' @Proxy'''' See the called class for details'@Description "Generates a query returning similar indices."Public Property Get SimilarIndices() As String    SimilarIndices = SQLiteSQLDbIdxFK.SimilarIndices(this.Schema)End Property'@Description "Generates a query returning table's columns."Public Function TableColumns(ByVal TableName As String) As String    Guard.EmptyString TableName    TableColumns = "SELECT * " & _                   "FROM " & this.Schema & ".pragma_table_xinfo('" & TableName & "')"End Function'@Description "Generates a query returning table's columns with placeholder columns."Public Function TableColumnsEx(ByVal TableName As String) As String    Guard.EmptyString TableName    TableColumnsEx = "SELECT * , 0 AS [unique], '' as [check], '' as [collate] " & _                     "FROM " & this.Schema & ".pragma_table_info('" & TableName & "')"End Function'@Description "Generates a query returning table's SQL."Public Function TableSQL(ByVal TableName As String) As String    Guard.EmptyString TableName    TableSQL = "SELECT sql " & _               "FROM sqlite_master " & _               "WHERE type = 'table' AND name = '" & TableName & "'"End Function'@Description "Generates a query returning table's foreign keys."Public Function TableForeingKeys(ByVal TableName As String) As String    TableForeingKeys = "SELECT * " & _                       "FROM " & this.Schema & ".pragma_foreign_key_list('" & TableName & "')"End Function

SQLiteSQLDbIdxFK

Bulky code related to database indices and foreign keys goes into a separate module.

'@Folder "SQLiteDB.Introspection"'@ModuleDescription "SQL queries for retrieving detailed information on database indices and foreign keys."'@PredeclaredId'''''''' Logically, this module is a part of SQLiteSQLDbInfo, and this FK/IDX code is'''' placed in a separate module simply to isolate the large amount of SQL code.'''' All methods of this module are exposed by SQLiteSQLDbInfo via composition.'''' This class is not supposed to be used directly, and it does not need to be'''' instantiated: all functionality can be used via the default instance.''''Option Explicit'''' @ClassMethod'''' This method can also be used on the default instance'''''''' Generates an SQLite query returning database tables, skipping'''' system tables (prefixed with "sqlite_") and ordering by ROWID'''' (in order of creation). If requested, a CTE WITH term is'''' generated.'''''''' Args:''''   Schema (string, optional, "main"):''''     Schema name/alias''''   CTEWITH (boolean, optional, False):''''     If True, format as a CTE WITH term'''''''' Returns:''''   String, containing the query'''''''' Examples:''''   >>> ?SQLiteSQLDbIdxFK.Tables''''   SELECT name, sql''''   FROM main.sqlite_master''''   WHERE type = 'table' AND (name NOT LIKE 'sqlite_%')''''   ORDER BY ROWID ASC''''''''   >>> ?SQLiteSQLDbIdxFK.Tables(, True)''''   t AS (''''       SELECT name, sql''''       FROM main.sqlite_master''''       WHERE type = 'table' AND (name NOT LIKE 'sqlite_%')''''       ORDER BY ROWID ASC''''   )'''''@Description "Generates a query returning database tables."Public Function Tables(Optional ByVal Schema As String = "main", _                       Optional ByVal CTEWITH As Boolean = False) As String    Dim Indent As String    Dim Query As String    Indent = IIf(CTEWITH, "    ", vbNullString)    Query = Indent & Join(Array( _        "SELECT tbl_name, sql", _        "FROM " & Schema & ".sqlite_master", _        "WHERE type = 'table' AND (name NOT LIKE 'sqlite_%')", _        "ORDER BY ROWID ASC" _    ), vbNewLine & Indent)    Tables = IIf(CTEWITH, "t AS (" & vbNewLine & Query & vbNewLine & ")", Query)End Function'''' @ClassMethod'''' This method can also be used on the default instance'''''''' Generates an SQLite query returning database views ordered by ROWID'''' (in order of creation).'''''''' Args:''''   Schema (string, optional, "main"):''''     Schema name/alias'''''''' Returns:''''   String, containing the query'''''''' Examples:''''   >>> ?SQLiteSQLDbIdxFK.Views''''   SELECT tbl_name, sql''''   FROM main.sqlite_master''''   WHERE type = 'view'''''   ORDER BY ROWID ASC'''''@Description "Generates a query returning database views."Public Function Views(Optional ByVal Schema As String = "main") As String    Views = Join(Array( _        "SELECT tbl_name, sql", _        "FROM " & Schema & ".sqlite_master", _        "WHERE type = 'view'", _        "ORDER BY ROWID ASC" _    ), vbNewLine)End Function'''' @ClassMethod'''' This method can also be used on the default instance'''''''' Generates an SQLite query returning database triggers ordered by ROWID'''' (in order of creation).'''''''' Args:''''   Schema (string, optional, "main"):''''     Schema name/alias'''''''' Returns:''''   String, containing the query'''''''' Examples:''''   >>> ?SQLiteSQLDbIdxFK.Triggers''''   SELECT tbl_name, sql''''   FROM main.sqlite_master''''   WHERE type = 'trigger'''''   ORDER BY ROWID ASC'''''@Description "Generates a query returning database triggers."Public Function Triggers(Optional ByVal Schema As String = "main") As String    Triggers = Join(Array( _        "SELECT tbl_name, sql", _        "FROM " & Schema & ".sqlite_master", _        "WHERE type = 'trigger'", _        "ORDER BY ROWID ASC" _    ), vbNewLine)End Function'''' @ClassMethod'''' This method can also be used on the default instance'''''''' Generates an SQLite query returning all non-system database objects'''' ordered by type (tables, indices, views, triggers) and then by ROWID.'''' The query returns two columns (sql, type_id).'''''''' Args:''''   Schema (string, optional, "main"):''''     Schema name/alias'''''''' Returns:''''   String, containing the query'''''''' Examples:''''   >>> ?SQLiteSQLDbIdxFK.DbSchema''''   SELECT sql, (CASE type''''       WHEN 'table' THEN 0''''       WHEN 'index' THEN 1''''       WHEN 'view'  THEN 3''''                    ELSE 4''''                    END) AS type_id''''   FROM main.sqlite_master''''   WHERE name NOT like 'sqlite_%'''''   ORDER BY type_id, _ROWID_'''''@Description "Generates a query returning all non-system database objects."Public Function DbSchema(Optional ByVal Schema As String = "main") As String    DbSchema = Join(Array( _        "SELECT sql, (CASE type", _        "    WHEN 'table' THEN 0", _        "    WHEN 'index' THEN 1", _        "    WHEN 'view'  THEN 2", _        "                 ELSE 3", _        "                 END) AS type_id", _        "FROM " & Schema & ".sqlite_master", _        "WHERE name NOT like 'sqlite_%'", _        "ORDER BY type_id, _ROWID_" _    ), vbNewLine)End Function'''' @ClassMethod'''' This method can also be used on the default instance'''''''' Generates an SQLite query returning all non-system database objects,'''' except for triggers, ordered by type (tables, indices, views) and'''' then by ROWID. The query returns two columns (sql, type_id).'''''''' Args:''''   Schema (string, optional, "main"):''''     Schema name/alias'''''''' Returns:''''   String, containing the query'''''''' Examples:''''   >>> ?SQLiteSQLDbIdxFK.DbSchemaNoTriggers''''   SELECT sql, (CASE type''''       WHEN 'table' THEN 0''''       WHEN 'index' THEN 1''''                    ELSE 2''''                    END) AS type_id''''   FROM main.sqlite_master''''   WHERE (name NOT like 'sqlite_%') AND type <> 'trigger'''''   ORDER BY type_id, _ROWID_'''''@Description "Generates a query returning all non-system database objects."Public Function DbSchemaNoTriggers(Optional ByVal Schema As String = "main") As String    DbSchemaNoTriggers = Join(Array( _        "SELECT sql, (CASE type", _        "    WHEN 'table' THEN 0", _        "    WHEN 'index' THEN 1", _        "                 ELSE 2", _        "                 END) AS type_id", _        "FROM " & Schema & ".sqlite_master", _        "WHERE (name NOT like 'sqlite_%') AND type <> 'trigger'", _        "ORDER BY type_id, _ROWID_" _    ), vbNewLine)End Function'''' @ClassMethod'''' This method can also be used on the default instance'''''''' Generates an SQLite query returning base info on database indices ordering'''' by ROWID (in order of creation). If requested, a CTE WITH term is generated.'''''''' Args:''''   Schema (string, optional, "main"):''''     Schema name/alias''''   CTEWITH (boolean, optional, False):''''     If True, format as a CTE WITH term'''''''' Returns:''''   String, containing the query'''''''' Examples:''''   >>> ?SQLiteSQLDbIdxFK.IndexBase''''   SELECT ROWID AS id, name AS idx_name, tbl_name, sql''''   FROM main.sqlite_master''''   WHERE type='index'''''   ORDER BY ROWID ASC''''''''   >>> ?SQLiteSQLDbIdxFK.IndexBase(, True)''''   ib AS (''''       SELECT ROWID AS id, name AS idx_name, tbl_name, sql''''       FROM main.sqlite_master''''       WHERE type='index'''''       ORDER BY ROWID ASC''''   )'''''@Description "Generates a query returning indices (base info)."Public Function IndexBase(Optional ByVal Schema As String = "main", _                          Optional ByVal CTEWITH As Boolean = False) As String    Dim Indent As String    Dim Query As String    Indent = IIf(CTEWITH, "    ", vbNullString)    Query = Indent & Join(Array( _        "SELECT ROWID AS id, name AS idx_name, tbl_name, sql", _        "FROM " & Schema & ".sqlite_master", _        "WHERE type = 'index'", _        "ORDER BY ROWID ASC" _    ), vbNewLine & Indent)    IndexBase = IIf(CTEWITH, "ib AS (" & vbNewLine & Query & vbNewLine & ")", Query)End Function'''' @ClassMethod'''' This method can also be used on the default instance'''''''' Generates an SQLite CTE WITH term for a foreign key list.'''''''' Args:''''   Schema (string, optional, "main"):''''     Schema name/alias'''''''' Returns:''''   String, containing the CTE WITH term'''''''' Examples:''''   >>> ?SQLiteSQLDbIdxFK.pForeignKeyList''''   fkl AS (''''       SELECT tbl_name AS child_table, [from] AS child_col0,''''              [table] AS parent_table, [to] AS parent_col0,''''              on_update, on_delete, id AS fk_id, seq AS fk_seq''''       FROM t''''       Join main.pragma_foreign_key_list(t.tbl_name)''''       ORDER BY child_table, fk_id''''   )'''''@Description "Generates a query returning a foreign key CTE WITH term."Public Function pForeignKeyList(Optional ByVal Schema As String = "main") As String    pForeignKeyList = Join(Array( _        "fkl AS (", _        "    SELECT tbl_name AS child_table, [from] AS child_col0,", _        "           [table] AS parent_table, [to] AS parent_col0,", _        "           on_update, on_delete, id AS fk_id, seq AS fk_seq", _        "    FROM t", _        "    JOIN " & Schema & ".pragma_foreign_key_list(t.tbl_name)", _        "    ORDER BY child_table, fk_id", _        "),", _        "fk AS (", _        "    SELECT *, group_concat(child_col0, ', ') AS child_cols,", _        "              group_concat(parent_col0, ', ') AS parent_cols,", _        "              min(fk_seq) AS min_fk_seq", _        "    FROM fkl", _        "    GROUP BY child_table, fk_id", _        "    ORDER BY child_table, fk_id", _        ")" _    ), vbNewLine)End Function'''' @ClassMethod'''' This method can also be used on the default instance'''''''' Generates an SQLite CTE WITH term for index info & list.'''' For each index list info and join the tables. Only use <index name> here.'''' For multi-column indices, keep the row with the first column and generates'''' a column list. Generate database-wide list of additional index info columns'''' from the per-table index lists.'''''''' Args:''''   Schema (string, optional, "main"):''''     Schema name/alias'''''''' Returns:''''   String, containing the CTE WITH term'''''@Description "Generates a query returning a CTE WITH term for index info & list."Public Function pIndexInfoList(Optional ByVal Schema As String = "main") As String    pIndexInfoList = Join(Array( _        "ii AS (", _        "    SELECT ib.idx_name, min(ii.seqno) AS seqno, ii.name AS col0_name, group_concat(ii.name, ', ') AS columns", _        "    FROM ib", _        "    JOIN " & Schema & ".pragma_index_info(ib.idx_name) AS ii", _        "    GROUP BY idx_name", _        "),", _        "il AS (", _        "    SELECT name AS idx_name, seq AS idx_seq, [unique], origin, partial", _        "    FROM t", _        "    JOIN " & Schema & ".pragma_index_list(tbl_name)", _        ")" _    ), vbNewLine)End Function'''' @ClassMethod'''' This method can also be used on the default instance'''''@Description "Generates a query returning all foreing keys in the SQLite database"Public Function ForeingKeys(Optional ByVal Schema As String = "main") As String    Dim StmtParts(0 To 5) As String    StmtParts(0) = "WITH"        '''' List all db tables    StmtParts(1) = Tables(Schema, True) & ","        '''' For each table, list foreign keys and join them to get a list of all foreign    '''' keys for the DB. Each row contains info on a foreign key for a single column.    '''' Yield a single row per foreign key, including multi-column keys. For multi-column    '''' keys, keep the row with the first column and generates a column list.    StmtParts(2) = pForeignKeyList(Schema)        StmtParts(3) = "SELECT *"    StmtParts(4) = "FROM fk AS foreign_keys"    StmtParts(5) = "ORDER BY child_table, fk_id"        ForeingKeys = Join(StmtParts, vbNewLine)End Function'''' @ClassMethod'''' This method can also be used on the default instance'''''''' Generates an SQLite query returning database indices, ordering by ROWID.'''' If "NonSys" = True, skip auto indices (prefixed with "sqlite_autoindex_").'''''@Description "Generates a query returning all indices in the SQLite database"Public Function Indices(Optional ByVal Schema As String = "main", _                        Optional ByVal NonSys As Boolean = True) As String    Dim StmtParts(10 To 26) As String    StmtParts(10) = "WITH"        '''' List all db tables    StmtParts(11) = Tables(Schema, True) & ","        '''' List all db indices    StmtParts(12) = IndexBase(Schema, True) & ","        '''' For each index list info and join the tables. Only use <index name> here. For    '''' multi-column indices, keep the row with the first column and generates a column list.    '''' Generate database-wide list of additional index info columns from the per-table index lists    StmtParts(13) = pIndexInfoList(Schema) & ","        '''' After taking care of multi-row descriptions, add aditional columns from index list    StmtParts(14) = "idx AS ("    StmtParts(15) = "    SELECT ib.id, ib.idx_name, ib.tbl_name, ii.col0_name, ii.columns, ib.sql"    StmtParts(16) = "    FROM ib, ii"    StmtParts(17) = "    ON ib.idx_name = ii.idx_name"    StmtParts(18) = "),"        '''' Join additional info columns with index-wise list    StmtParts(19) = "iex AS ("    StmtParts(20) = "    SELECT idx.*, il.idx_seq, il.[unique], il.origin, il.partial"    StmtParts(21) = "    FROM idx, il"    StmtParts(22) = "    WHERE idx.idx_name = il.idx_name"    StmtParts(23) = ")"        StmtParts(24) = "SELECT *"    StmtParts(25) = "FROM iex AS indices"    StmtParts(26) = IIf(NonSys, _                    "WHERE idx_name NOT LIKE 'sqlite_autoindex_%'" & vbNewLine, vbNullString) & _                    "ORDER BY id"        Indices = Join(StmtParts, vbNewLine)End Function'''' @ClassMethod'''' This method can also be used on the default instance'''''''' Indices on child columns of foreing key relations are not mandatory,'''' but generally should be defined. Database engine does not control whether'''' such indices are defined. This query return a summary table showing all'''' child columns and corresponding indices in the "idx_name" column. If this'''' field is empty for a particular child column, the corresponding index has'''' not been defined.'''''@Description "Generates a query returning child columns for all foreing keys and corresponding indices."Public Function FKChildIndices(Optional ByVal Schema As String = "main") As String    Dim StmtParts(10 To 34) As String    StmtParts(10) = "WITH"    StmtParts(11) = Tables(Schema, True) & ","    StmtParts(12) = IndexBase(Schema, True) & ","    StmtParts(13) = pIndexInfoList(Schema) & ","    StmtParts(14) = "idx AS ("    StmtParts(15) = "    SELECT ib.id, ib.idx_name, ib.tbl_name, ii.col0_name, ii.columns, ib.sql"    StmtParts(16) = "    FROM ib, ii"    StmtParts(17) = "    ON ib.idx_name = ii.idx_name"    StmtParts(18) = "),"    StmtParts(19) = "iex AS ("    StmtParts(20) = "    SELECT idx.*, il.idx_seq, il.[unique], il.origin, il.partial"    StmtParts(21) = "    FROM idx, il"    StmtParts(22) = "    WHERE idx.idx_name = il.idx_name AND partial = 0"    StmtParts(23) = "),"    StmtParts(24) = pForeignKeyList(Schema) & ","    '''' Join indices and foreign keys tables to see which child columns do not have indices.    '''' Multi-column indices, having the child column set as the "prefix" are accepted.    StmtParts(25) = "fki AS ("    StmtParts(26) = "    SELECT fk.child_table, fk.child_cols, fk.parent_table, fk.parent_cols,"    StmtParts(27) = "           iex.idx_name"    StmtParts(28) = "    FROM fk"    StmtParts(29) = "    LEFT JOIN iex"    StmtParts(30) = "    ON fk.child_table = iex.tbl_name AND fk.child_cols = substr(iex.columns, 1, length(fk.child_cols))"    StmtParts(31) = ")"    StmtParts(32) = "SELECT *"    StmtParts(33) = "FROM fki AS fkeys_childindices"    StmtParts(34) = "ORDER BY child_table, child_cols"    FKChildIndices = Join(StmtParts, vbNewLine)End Function'''' @ClassMethod'''' This method can also be used on the default instance'''''''' If IDX1 indexes columns (A, B) and IDX2 indexes columns (A, B, C), that is'''' IDX1 indexes a "prefix" of IDX2, IDX2 can replace IDX1. On the other hand,'''' depending on statistics (if for any given pair (A, B), there are very few'''' rows), IDX2 may not be justifiable (unless it is the primary key). This'''' query aims to return all such similar ("prefix") indices, though it has not'''' been thoughroughly verified. It may return some "false" positive. Whether'''' it can miss indices is not clear.'''''@Description "Generates a query returning similar indices."Public Function SimilarIndices(Optional ByVal Schema As String = "main") As String    Dim StmtParts(10 To 39) As String    StmtParts(10) = "WITH"    StmtParts(11) = Tables(Schema, True) & ","    StmtParts(12) = IndexBase(Schema, True) & ","    StmtParts(13) = pIndexInfoList(Schema) & ","    StmtParts(14) = "idx AS ("    StmtParts(15) = "    SELECT ib.id, ib.idx_name, ib.tbl_name, ii.col0_name, ii.columns"    StmtParts(16) = "    FROM ib, ii"    StmtParts(17) = "    ON ib.idx_name = ii.idx_name"    StmtParts(18) = "),"    StmtParts(19) = "iex AS ("    StmtParts(20) = "    SELECT idx.*, il.idx_seq, il.[unique], il.origin, il.partial"    StmtParts(21) = "    FROM idx, il"    StmtParts(22) = "    WHERE idx.idx_name = il.idx_name"    StmtParts(23) = "),"    StmtParts(24) = "fdup AS ("    StmtParts(25) = "    SELECT tbl_name, col0_name, count(*) AS group_size"    StmtParts(26) = "    FROM iex"    StmtParts(27) = "    WHERE partial = 0"    StmtParts(28) = "    GROUP BY tbl_name, col0_name"    StmtParts(29) = "    HAVING group_size > 1"    StmtParts(30) = "),"    StmtParts(31) = "idup AS ("    StmtParts(32) = "    SELECT iex.*, fdup.group_size"    StmtParts(33) = "    FROM iex"    StmtParts(34) = "    JOIN fdup"    StmtParts(35) = "    ON iex.tbl_name = fdup.tbl_name AND iex.col0_name = fdup.col0_name"    StmtParts(36) = ")"    StmtParts(37) = "SELECT *"    StmtParts(38) = "FROM idup AS similar_indices"    StmtParts(39) = "ORDER BY tbl_name, col0_name, columns"        SimilarIndices = Join(StmtParts, vbNewLine)End Function

SQLiteSQLEngineInfo

Engine-related code goes in this module.

'@Folder "SQLiteDB.Introspection"'@ModuleDescription "SQL queries for retrieving information about the engine configuration and available features."'@PredeclaredId'@Exposed'@IgnoreModule ProcedureNotUsed'''' All methods in this module are class methods and can be safely called on the default instance'''' @ClassModuleOption Explicit'@Description "Generates query returning available SQLite collations"Public Property Get Collations() As String    Collations = "SELECT * FROM pragma_collation_list AS collations ORDER BY name"End Property'@Description "Generates query returning compile options"Public Property Get CompileOptions() As String    CompileOptions = "SELECT * FROM pragma_compile_options AS compile_options"End Property'@Description "Generates query returning available SQLite functions"Public Property Get Functions() As String    Functions = "SELECT * FROM pragma_function_list AS functions ORDER BY name"End Property'@Description "Generates query returning available SQLite modules"Public Property Get Modules() As String    Modules = "SELECT * FROM pragma_module_list AS modules ORDER BY name"End Property'@Description "Generates query returning available SQLite pragmas"Public Property Get Pragmas() As String    Pragmas = "SELECT * FROM pragma_pragma_list AS pargmas ORDER BY name"End Property'@Description "Generates query returning SQLite version"Public Property Get Version() As String    Version = "SELECT sqlite_version() AS version"End Property

ADOlib.RecordsetToQT

This routine outputs record data from anADODB.Recordset onto an Excel worksheet via the QueryTable feature. TheADODB.Recordset object is directly provided to the QueryTable constructor keeping the code compact and the process efficient.

'@Description "Outputs Recordset to Excel Worksheet via QueryTable"Public Sub RecordsetToQT(ByVal AdoRecordset As ADODB.Recordset, ByVal OutputRange As Excel.Range)Attribute RecordsetToQT.VB_Description = "Outputs Recordset to Excel Worksheet via QueryTable"    Guard.NullReference AdoRecordset    Guard.NullReference OutputRange        Dim QTs As Excel.QueryTables    Set QTs = OutputRange.Worksheet.QueryTables        '''' Cleans up target area before binding the data.    '''' Provided range reference used to indicate the left column and    '''' Recordset.Fields.Count determines the width.    '''' If EntireColumn.Delete method is used, Range object becomes invalid, so    '''' a textual address must be saved to reset the Range reference.    '''' However, when multiple QTs are bound to the same worksheet,    '''' EntireColumn.Delete shifts columns to the left, so the target range    '''' may not be clear. EntireColumn.Clear clears the contents.    Dim FieldsCount As Long    FieldsCount = AdoRecordset.Fields.Count    Dim QTRangeAddress As String    QTRangeAddress = OutputRange.Address(External:=True)    Dim QTRange As Excel.Range    '@Ignore ImplicitActiveSheetReference    Set QTRange = Range(QTRangeAddress)    QTRange.Resize(1, FieldsCount).EntireColumn.Clear    '@Ignore ImplicitActiveSheetReference    Set QTRange = Range(QTRangeAddress)        Dim WSQueryTable As Excel.QueryTable    For Each WSQueryTable In QTs        WSQueryTable.Delete    Next WSQueryTable        Dim NamedRange As Excel.Name    For Each NamedRange In QTRange.Worksheet.Names        NamedRange.Delete    Next NamedRange        Set WSQueryTable = QTs.Add(Connection:=AdoRecordset, Destination:=QTRange.Range("A1"))    With WSQueryTable        .FieldNames = True        .RowNumbers = False        .PreserveFormatting = True        .RefreshOnFileOpen = False        .BackgroundQuery = True        .RefreshStyle = xlInsertDeleteCells        .SaveData = False        .AdjustColumnWidth = True        .RefreshPeriod = 0        .PreserveColumnInfo = True        .EnableEditing = True    End With    WSQueryTable.Refresh    QTRange.Worksheet.UsedRange.Rows(1).HorizontalAlignment = xlCenterEnd Sub

1 Answer1

Start asking to get answers

Find the answer to your question by asking.

Explore related questions

See similar questions with these tags.