SQLight with VB6 and ADO

[Daniel Duta]

These days I was looking for a replacement for Access database as result of some disappointments in my daily work with MS product. The most recent was this week when the entire database crashed after that annoying bloating effect which each time required to re-compact the db after its size increasing (~10 MB). Because as you all I still work in VB6 I found SQLight as a convenient alternative, especially that is free, stable and with periodical performance improvements. The idea is I usually work with ADO and I didn't find in the CodeBank any example regarding SQLight string connection. Could you provide me some links about this topic ? Thank you.

[Carlos Rocha]

You can use MS OLE DB Provider for ODBC Sources after installing SQLite ODBC Driver, but the easiest way to connect to SQLite from VB6 is to use Olaf's wrapper, contained in vbRichClient.

[Schmidt]

... the entire database crashed after that annoying bloating effect which each time required to re-compact the db after its size increasing (~10 MB)...

One can indeed avoid these "re-compacting"-runs with SQLite, when the appropriate "Pragma auto_vacuum" is set to either =1 or =full...
https://www.sqlite.org/pragma.html#pragma_auto_vacuum

Below is Import-Code, how it can be used against a JET-DB-ADO-Connection
(which also shows, how to ensure auto-compacting on the new created SQLite-DB).

Option Explicit

Private Sub Form_Load()
  Const SQLiteFName$ = "C:\Temp\SQLiteConverted1.db"
  
  Dim AdoCnn As New ADODB.Connection 'Import per OleDB-Provider (Jet-4.0, NorthWind-DB)
      AdoCnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\Temp\NWind.mdb"
        ImportFromADOCnn AdoCnn, SQLiteFName
      AdoCnn.Close
  
  Dim Cnn As cConnection
  Set Cnn = New_c.Connection(SQLiteFName) 'open the new imported SQLite-DB
      Cnn.Execute "PRAGMA auto_vacuum=1; Vacuum" 'the DB is from now on in Auto-Compacting-Mode
 
  Debug.Print Cnn.OpenRecordset("PRAGMA  auto_vacuum")(0).Value 'check if the auto-compacting value was persisted in the SQLite-DB
  Debug.Print Cnn.OpenRecordset("Select * From Orders").RecordCount 'just a recordcount-test on the Orders-table
End Sub
 
Public Sub ImportFromADOCnn(AdoCnn As ADODB.Connection, SQLiteFileName As String)
  On Error GoTo 1
  
  If New_c.FSO.FileExists(SQLiteFileName) Then
    If MsgBox("SQLite-TargetFile already exists, delete and proceed?", vbYesNo) = vbNo Then Exit Sub
    New_c.FSO.DeleteFile SQLiteFileName
  End If
  
  Dim SQLiteCnn As vbRichClient5.cConnection
  Set SQLiteCnn = New_c.Connection(SQLiteFileName, DBCreateNewFileDB)
 
  With New_c.Converter
    .ConvertDatabase AdoCnn, SQLiteCnn:  Debug.Print "Table-Schemas created, Table-Data transferred!"
    .ConvertIndexes AdoCnn, SQLiteCnn:   Debug.Print "Index-Import finished!"
  End With
  
1 If Err Then MsgBox Err.Description
End Sub

HTH

Olaf

[Daniel Duta]

Thank you all for your reply. I mention that I have SQLite ODBC Driver installed but I receive the run-time error [Microsoft][ODBC Driver Manager]Data source name too long. Below is my attempt to open the db connection.

Private con As ADODB.Connection
Private rec As ADODB.Recordset
Private Const dbPath As String = "C:\Users\Desktop\myBase.db"

Private Sub Command1_Click()
    Set con = New ADODB.Connection 
    With con
        .Properties("Data Source") = dbPath
        .Open 'open connection
    End With
End Sub

Regarding the Olaf's wrapper, even I have the vbRichClient library registered properly for some reason either I use those 3 samples from site or the Import-Code posted above each time I receive the following error:

Simply I cannot open the database and I try to find out what is going wrong. Thank you.

[techgnome]

Where's the rest of your connection string? You can't simply point to a file and decide that's good enough... you need to tell it what driver/provider to use.

-tg

[Schmidt]

Regarding the Olaf's wrapper, even I have the vbRichClient library registered properly for some reason either I use those 3 samples from site or the Import-Code posted above each time I receive the following error:

Simply I cannot open the database and I try to find out what is going wrong.

To rule out any potential "Networking-issues" with UNC-Paths or "mapped Network-drives"... -
could you try to open both Files:
- the "Source-MDB" and
- the "Destination-SQLiteDB"
from a Path on a local Disk? (as I did in the example, working against "C:\temp\..")

What also might help is, to download and install (by using regsvr32 or the register_in_place-scripts)
the latest version of the RC5 in a "fixed location" on your local dev-machine (keeping all Dlls together in their own local Folder).

Olaf

[Daniel Duta]

Thank you for your hints, Olaf. Your routine should be very useful to me in this phase of transferring tables from Access to SQLight, considering that my SQLight Expert app doesn't allow to import data other than from csv or txt files. At the moment I don't have any issue to open the Access db from ADO but I feel something goes abnormal with vbRichClient5 library because your function New_c.FSO.FileExists(SQLiteFileName) took 23-28 seconds variably. On the other hand, I was not able to run your samples from SQLiteDemos (published on your site). The library is registered at this address: C:\WINDOWS\SysWow64\vbRichClient5.dll. Could be other details involved in this behaviour ? Of course, besides my knowledge

[Arnoutdv]

@Daniel, if you placed vbRichClient5.dll in C:\WINDOWS\SysWow64\ then you didn't follow the instruction given by Olaf

download and install (by using regsvr32 or the register_in_place-scripts)
the latest version of the RC5 in a "fixed location" on your local dev-machine (keeping all Dlls together in their own local Folder)

[Daniel Duta]

@Daniel, if you placed vbRichClient5.dll in C:\WINDOWS\SysWow64\ then you didn't follow the instruction given by Olaf

Thank you Arnoutdv for your reply. It is not very clear to me what exactly mean "fixed location"...I am admin on my machine and SysWow64 folder seems to me a fixed location. Also, I do not understand what dll's should be kept together in their own folder. Is it ADO with vbRichClient5, is vbRichClient5 with SQLite3.dll or maybe together with vb_cairo_sqlite.dll and DirectCOM.dll ? Just suppositions.

[Arnoutdv]

I thought it was quite clear on the download page of vbRichClient5:

Downloads

The 3 Base-Dlls of the toolset are contained in this minimum-package:

vbRC5BaseDlls.zip (~ 2.7MB, current version: 5.0.66, last update: 2018-02-04 ... latest SQLite-version: 3.21)

Out of the contained dlls you'd have only:
vbRichClient5.dll <-- this one here to register (either per regsvr32 in Admin-Mode - or per incl. Script: RegisterRC5inPlace.vbs)
vb_cairo_sqlite.dll
DirectCOM.dll
In addition, please keep those Dlls always together in their Folder - also when you deploy them with your App.

[Daniel Duta]

Well, I noticed that DirectCOM.dll only was missing from that location. Anyway, I unregistered my old RC5 version and I added all 3 libraries required in the same SysWow64 folder. Now, I get a new error:

Below is the connection adapted to my Access database:

Private Sub Form_Load()
   Const SQLiteFName As String = "C:\TEMP\myCustomer.db"
  
   Dim con As ADODB.Connection
   Set con = New ADODB.Connection

   With con
       .Provider = "Microsoft.ACE.OLEDB.12.0"
       .Properties("Data Source") = "C:\TEMP\myCustomer.accdb"
       .Open
   End With

   Call ImportFromADOCnn(con, SQLiteFName)
   
   con.Close
   Set con = Nothing
End Sub

The error does occur in ImportFromADOCnn at this line: New_c.Converter.ConvertDatabase AdoCnn, SQLiteCnn.

[Arnoutdv]

Can you show more of the code in ImportFromADOCnn

[Daniel Duta]

Can you show more of the code in ImportFromADOCnn

Sure, it is easy because it remained unchanged

Public Sub ImportFromADOCnn(AdoCnn As ADODB.Connection, SQLiteFileName As String)
  On Error GoTo 1
  
  If New_c.FSO.FileExists(SQLiteFileName) Then
     If MsgBox("SQLite-TargetFile already exists, delete and proceed?", vbYesNo) = vbNo Then Exit Sub
     New_c.FSO.DeleteFile SQLiteFileName
  End If

  Dim SQLiteCnn As vbRichClient5.cConnection
  Set SQLiteCnn = New_c.Connection(SQLiteFileName, DBCreateNewFileDB)
 
  With New_c.Converter
    .ConvertDatabase AdoCnn, SQLiteCnn:  Debug.Print "Table-Schemas created, Table-Data transferred!"
    .ConvertIndexes AdoCnn, SQLiteCnn:   Debug.Print "Index-Import finished!"
  End With
  
1 If Err Then MsgBox Err.Description
End Sub

An explanation could be that ConvertDatabase doesn't understand the newer ACCDB file format but only the old MDB format, who knows.

[Schmidt]

An explanation could be that ConvertDatabase doesn't understand the newer ACCDB file format but only the old MDB format, who knows.

If you want to import AccDBs, you will have to install a recent OleDB-driver for these DBs first.
Here is one for AccDB (as used by Access 2010), which usually works:
https://www.microsoft.com/en-us/down....aspx?id=13255
Or a somewhat newer one (included in the runtime for Access 2013):
https://www.microsoft.com/en-us/down....aspx?id=39358

Though, trying to import a (quite large) *.accdb recently (after installing the 2010 Driver-package from the first of the above links) -
I was forced, to first use the "Split into Remote-DB"-export-feature of Access, before I was able to import the thing (the "RemoteDB-exported" version).
(the exported Remote-DB is still in *.accdb format then, but not including all the "Forms and Macros" of the original, only "plain Tables and Indexes").

Another export-option from AccDB would be, when you'd simply convert it into "Access2000-Format" (as an *.mdb) before making an SQLite-import-attempt.

That said, I've also updated the cConverter-Class (with regards to "Auto-TimeStamp"-Handling, when defined as a Default-option in a JET-Field-Def).
Here is my little Import-Test-Project in a Zip (which contains cConverter.cls as well as an NWind.mdb, but also an NWind.accdb for the import-tests).
http://vbRichClient.com/Downloads/SQLiteImports.zip

In case this works out well for you (regarding the Imports of your *.accdb-Files), I'd include the updated code of cConverter.cls
(which is used in the Test-Project directly "as code", not from the RC5-lib) - "officially" back into the next version of the RC5.

So please let me know, what you find out...

Olaf

[Daniel Duta]

Thank you for your reply, Olaf. None of the drivers above worked with accdb format within RC5 method ConvertDatabase. The Access version that I used is 2016 as below: .
After these failures, I took your advice and I saved the Access db into the old mdb format (Provider="Microsoft.JET.OLEDB.4.0) and it worked without any issue from beginning. So, at the moment, ConvertDatabase doesn't recognize the newer AccDB format. However, cConverter class from your sample was able to convert both formats and it would be useful to consider in the future an update of RC5 accordingly.
What is less clear to me is the fact that you used in this class a reference to Microsoft ADO. Do you intend to integrate in your RC5 routines that are dependent on MS libraries? I understood from you in the past that RC5 has some features that are even over ADO possibilities. And coming back to the topic, all I want to know is how do you open a db3 and how do you make a simple query "Select * From Customers" (considering the already converted file NWind.db3) having the similar ADO possibility to load the content into an array via GetRows method.
Thank you for all.

[DataMiser]

These days I was looking for a replacement for Access database as result of some disappointments in my daily work with MS product. The most recent was this week when the entire database crashed after that annoying bloating effect which each time required to re-compact the db after its size increasing (~10 MB). Because as you all I still work in VB6 I found SQLight as a convenient alternative, especially that is free, stable and with periodical performance improvements. The idea is I usually work with ADO and I didn't find in the CodeBank any example regarding SQLight string connection. Could you provide me some links about this topic ? Thank you.

Are you saying that you are having trouble with an Access database that is only 10mb in size? That is tiny, can't say I have ever ran into an issue with one that small. Back when i was using the 97 format I did see some issues when the db grew near 0.5gb or larger and perhaps a small performance hit at 250mb+ but never in a db smaller than 100mb and the 2000 format seems to be more stable than the 97 format was. I still have not moved to the ACCDB formats, haven't saw a need as the 2003 format seems to be very stable and solid for smaller dbs <250mb for larger more complex ones I always use SQL Server instead.

[Schmidt]

...However, cConverter class from your sample was able to convert both formats and it would be useful to consider in the future an update of RC5 accordingly.

That was my question - and thanks for answering it...
(the cConverter.cls which was included in source in the little Demo above, will be identically to the one in the next RC5-release, coming in a few weeks).

ADO is a nice abstraction-layer to work against different DB-Engines, but with regards to the
results which are returned by AdoCnn.OpenSchema, there are apparently subtle differences, as you've found
out (the new cConverter.cls being more tolerant now with regards to AccDb-AdoConnections).

What is less clear to me is the fact that you used in this class a reference to Microsoft ADO.
Do you intend to integrate in your RC5 routines that are dependent on MS libraries?

The RC5 is aiming for platform-independence, yes - and once you have a converted SQLite.db-File on your disk,
you will not need ADO anymore, if you stick to the RC5.cConnection and RC5.cRecordset-classes.

At the same time, we all *are* currently working on Windows-OSes with VB6-stuff - and thus we need ways
to be able to "migrate stuff softly" (to platform-independence) in our projects. In that light, the RC5 does contain
several Classes and functionalities, which "ease the pain" whilt applying these first "soft migration"-steps...
- the RC5.cConverter-Class being one of those "migration-helpers"
- the cRecordset.GetADORsFromContent being another (e.g. when you have to feed older Reporting-Tools, which only accept ADO-Rs)
- the Cairo-stuff (althoug being able to render into SVG or PDF as well) has ways, to render things on "good old Windows-hDCs"
- and Cairo-Widgets can also be mixed on "normal VB6-Forms" (together with "normal Controls") in said migration-phase
- (although you can build entirely MS-free Forms, which only contain RC5-Widgets... that should be the goal, in case you prepare for platform-independency over the next years)

I understood from you in the past that RC5 has some features that are even over ADO possibilities.

Sure, InMemory-DBs are nice to have, Triggers are nice to have, recursive SQL-CTEs are nice to have, SQL-JSON-support is nice to have -
and there's a whole lot of fast support-functions, which cover all kind of "disconnected and serialization scenarios" (with regards to DB- and Resultset-Handling).

And coming back to the topic, all I want to know is how do you open a db3 and how do you make a simple query "Select * From Customers" (considering the already converted file NWind.db3) having the similar ADO possibility to load the content into an array via GetRows method.

I'd suggest to work intensively with the VB6-ObjectExplorer in the beginning (the F2-Key is your friend).
As for cRecordsets - they can be opened by cConnection.OpenRecordset
... and pressing <F2>, followed by typing the search-term:
GetRows
... will give you appropriate responses in the Object-Explorer...
The cRecordset is quite similar to an ADORs (and its GetRows-Methods being no exception).

The features of the SQLite-wrapper-Classes are covered quite completely in the larger SQLite-tutorial-Demos here:
http://vbrichclient.com/#/en/Demos/SQLite/

Olaf