VB Forum / Database Access / September 2008

Here's the Download-Link:
www.datenhaus.de/Downloads/ForeignKeyFileScan.zip

Have tried, to match a bit with your Filescan-Scenario.
The Demo uses two Classes (one for ADO/JET and
one for SQLite), which both implement an iFileScan-
Interface, which was defined in an "abstract class" in
the same project.

Tried my best, to implement both Classes with the
fastest known methods (using Command-Objects
for the inserts) - anyway, the ADO/JET approach
is ca. factor 15 away from the SQLite-implementation.

Both versions currently operate directly against the
DB, so in principle should produce consistent results
even in multiuser-mode.

If you always create new DBs for your FileScans and/or
are the only user working on this DB, then you could
speedup both versions, if you "buffer" the FileName-
ExistsInDB-check with a Collection or the SortedDictionary.

Olaf

I would define a small IRecordset-Interface, which
includes the Rs-Methods and -Properties you want
to use in your App.

.EOF, .BOF
.MoveFirst, .MoveNext, .MoveLast
.AddNew
.UpdateBatch
including some Properties/Methods, which allow you
to work with the Field-Names and Field-Values,
without exposing the Field(s)-Object, to keep things
simple inside the IRecordset-Implementation (something
in analogy to VBs Fonts, which you can either address
with Font.Name, Font.Size or more directly one ObjLevel
above with .FontName, .FontSize).

so your IRecordset should also contain:
.FieldsCount() as Long
.FieldValue(KeyOrIndex As Variant) As Variant
.FieldName(KeyOrIndex As Variant) As Variant

this way you can avoid, exposing the Field-
Object-stuff and writing additional IFields and
IField-Interfaces + Wrappers.

Then I would split your combined Method (.ExecuteSQL )
in your current Database-Wrapper-Object into two
Methods - one for the DDL-stuff and the Write-Direction
against the DB, called .Execute (returning only a Long
with the affected Rows-Count if you want) and another one
for the Read-Direction, addressing the "Selects", returning
an IRecordset (maybe called OpenRecordset, like
in my SQLite-Wrapper). That'd make things more
"obvious" in your Client-Code and adds only one Method.

Depending on ADOJet or SQLite, the OpenRecordset-
Implementations would look something like this:

inside cADOJETDatabase (implementing IDataBase):
Private mCnn as Connection 'the internal ADO-Cnn

Private Function IDataBase_OpenRecordset(SQL as String) as IRecordset
Dim Rs as cADOJetRecordset
   'instantiate the implementation of IRecordset
   Set Rs = New cADOJetRecordset

   'now call a Friend-Method on cADOJetRecordset
   Rs.Initialize SQL, mCnn

   Set OpenRecordset = Rs 'and finally cast and return
End Function

inside the IRecordset-Implementation cADOJetRecordset:
(additionaly to the Implementation of IRecordset, which
should be trivial, the Friend-Method on the Default-
Interface of this Class should be something like...)
Private mRs as Recordset 'the internal ADO-Rs

Friend Sub Initialize(SQL as String, Cnn as Connection)
   Set mRs = New Recordset
   mRs.Open SQL, Cnn, adOpenStatic, adLockBatchOptimistic
End Sub

And in analogy you would have to code the two SQLite-
Implementations for IDatabase and IRecordset, using
the appropriate internal Objects (cConnection and
cRecordset of course).

HTH, and let me know, how you come along -
and of course how the performance looks like
in your real-world-app...

Beside that, the SQLite-Wrapper is able to give
you a real ADO-Recordset as a copy from its
internal SQLite-Recordset-Content as a
disconnected ADO-Rs, containing all the Data
of the SQLite-Recordset using the Method:
Set ADORs = SQLiteRs.GetADORsFromContent

But this ADORs is then a real disconnected one,
without any relation to the SQLite-DB or SQLite-
Connection, so .UpdateBatch on this ADORs-Copy
will of course not write its changes back into the
SQLite-DB (you would have to use the SQLite-
Command- or Cnn.Execute-Methods for the
Write-Direction against the DB).
And this Extra-Copy-Method will of course
affect the Read-Performance of SQLite - basically
this should result in ca. the same (slower) Read-Performance
as if retrieving an ADO-Recordset directly from
a JET-*.mdb, maybe in sum (including the Extra-
Copy) some 10 or 20% slower whilst retrieving
smaller Recordsets and ca. the same amount faster,
if the Recordsets become larger.

So I would go with the additional IRecordset-
Implementation for your App, but for a first try
you could use this method, to avoid the Implementation-
efforts for IRecordset, simply returning an
ADO-Recordset from bot IDataBase-Implementations.

Olaf