In case it is of use to any VB or VBA coders, I think this is all the code needed to work with this dll. Also added a bit of code to do timings.
Option Explicit Private Declare Sub sqlite3_open Lib "SQLite3VB.dll" _ (ByVal FileName As String, _ ByRef handle As Long) Private Declare Sub sqlite3_close Lib "SQLite3VB.dll" _ (ByVal DB_Handle As Long) Private Declare Function sqlite3_last_insert_rowid _ Lib "SQLite3VB.dll" _ (ByVal DB_Handle As Long) As Long Private Declare Function sqlite3_changes _ Lib "SQLite3VB.dll" _ (ByVal DB_Handle As Long) As Long Private Declare Function sqlite_get_table _ Lib "SQLite3VB.dll" _ (ByVal DB_Handle As Long, _ ByVal SQLString As String, _ ByRef ErrStr As String) As Variant() 'Now returns a BSTR Private Declare Function sqlite_libversion _ Lib "SQLite3VB.dll" () As String '// This function returns the number of rows from the last sql statement. '// Use this to ensure you have a valid array Private Declare Function number_of_rows_from_last_call _ Lib "SQLite3VB.dll" () As Long 'holding all the DB handles and paths Public collSQLiteDB As Collection Private Declare Function timeGetTime Lib "winmm.dll" () As Long Private lStartTime As Long Sub StartSW() lStartTime = timeGetTime() End Sub Sub StopSW(Optional ByRef strMessage As Variant = "") MsgBox "Done in " & timeGetTime() - lStartTime & _ " msecs", , strMessage End Sub Function OpenDBConnection(strDB As String, _ Optional bClose As Boolean) As Long Dim i As Long Dim lDBHandle As Long Dim strTempCurDrive As String Dim strTempCurDir As String Dim strErrors As String Dim bFile As Boolean Dim strLocalDrive As String Dim bNewDBCollection As Boolean Dim arr(1 To 2) As Variant On Error GoTo ERROROUT If collSQLiteDB Is Nothing Then Set collSQLiteDB = New Collection bNewDBCollection = True End If strTempCurDir = CurDir strTempCurDrive = Left$(strTempCurDir, 1) strLocalDrive = Left$(Application.Path, 1) 'this is to point to SQLite3VB.dll '--------------------------------- ChDrive strLocalDrive ChDir strLocalDrive & ":\SQLite3VB_Test\" If InStr(1, strDB, ":memory:", vbTextCompare) = 0 Then bFile = True End If 'for in case there was a duplicate key in the DB collection On Error Resume Next If bClose Then For i = 1 To collSQLiteDB.Count If collSQLiteDB(i)(2) = strDB Then sqlite3_close collSQLiteDB(i)(1) collSQLiteDB.Remove i Exit For End If Next Else If collSQLiteDB.Count = 0 Then sqlite3_open strDB, lDBHandle arr(1) = lDBHandle arr(2) = strDB 'so a db path can only be in the collection once collSQLiteDB.Add arr, strDB OpenDBConnection = lDBHandle Else 'see if there is a valid connection already for this DB '------------------------------------------------------ For i = 1 To collSQLiteDB.Count If collSQLiteDB(i)(2) = strDB Then If collSQLiteDB(i)(1) > 0 Then OpenDBConnection = collSQLiteDB(i)(1) ChDrive strTempCurDrive ChDir strTempCurDir Exit Function End If sqlite3_close collSQLiteDB(i)(1) collSQLiteDB.Remove i End If Next sqlite3_open strDB, lDBHandle arr(1) = lDBHandle arr(2) = strDB 'so a db path can only be in the collection once collSQLiteDB.Add arr, strDB OpenDBConnection = lDBHandle End If 'these speed up inserts enormously, particulary the first one '------------------------------------------------------------ sqlite_get_table lDBHandle, "PRAGMA synchronous=off;", strErrors sqlite_get_table lDBHandle, "PRAGMA encoding='UTF-8';", strErrors 'not sure default_cache_size applies to a memory database, probably not If bFile Then sqlite_get_table lDBHandle, _ "PRAGMA default_cache_size = 32768;", strErrors End If sqlite_get_table lDBHandle, "PRAGMA vdbe_trace = OFF;", strErrors sqlite_get_table lDBHandle, "PRAGMA page_size=4096;", strErrors End If 'to return to the CurDir at the start of the procedure '----------------------------------------------------- ChDrive strTempCurDrive ChDir strTempCurDir Exit Function ERROROUT: ChDrive strTempCurDrive ChDir strTempCurDir MsgBox Err.Description, , "error number: " & Err.Number End Function Function AlterDB(strCommand As String, _ Optional lDBHandle, _ Optional strDB As String) As Variant Dim strErrors As String Dim lChanges As Long Dim lLastInsertRowID As Long Dim arr(1 To 3) As Variant On Error GoTo ERROROUT If lDBHandle = 0 Then lDBHandle = OpenDBConnection(strDB) End If sqlite_get_table lDBHandle, strCommand, strErrors lChanges = sqlite3_changes(lDBHandle) lLastInsertRowID = sqlite3_last_insert_rowid(lDBHandle) arr(1) = lChanges arr(2) = lLastInsertRowID arr(3) = strErrors AlterDB = arr Exit Function ERROROUT: arr(1) = lChanges arr(2) = lLastInsertRowID arr(3) = strErrors AlterDB = arr End Function Function GetFromDB(strSQL As String, _ Optional lReturnedRows As Long, _ Optional strError As String, _ Optional lDBHandle As Long, _ Optional strDB As String) As Variant Dim arr On Error GoTo ERROROUT If lDBHandle = 0 Then lDBHandle = OpenDBConnection(strDB) End If arr = sqlite_get_table(lDBHandle, strSQL, strError) lReturnedRows = number_of_rows_from_last_call() If lReturnedRows = 0 Then GetFromDB = strError Else GetFromDB = arr End If Exit Function ERROROUT: GetFromDB = strError End Function Sub Test() Dim i As Long Dim r As Long Dim c As Long Dim strDB As String Dim strSQL As String Dim strError As String Dim arr Dim strResult As String Dim lRows As Long Dim lDBHandle As Long strDB = "C:\test.db3" 'strDB = ":memory:" lDBHandle = OpenDBConnection(strDB) On Error Resume Next strSQL = "DROP TABLE TEST" AlterDB strSQL, lDBHandle strSQL = "CREATE TABLE 'TEST' ([col1] INTEGER, [col2] TEXT)" AlterDB strSQL, lDBHandle StartSW strSQL = "BEGIN TRANSACTION" AlterDB strSQL, lDBHandle For i = 1 To 6 strSQL = "INSERT INTO TEST (col1, col2) " & _ "VALUES(" & i & ", 'test value" & i & "')" AlterDB strSQL, lDBHandle Next strSQL = "CREATE INDEX IDX_TEST_col1 ON TEST(col1)" AlterDB strSQL, lDBHandle strSQL = "COMMIT TRANSACTION" AlterDB strSQL, lDBHandle StopSW "time for inserting" StartSW strSQL = "SELECT * FROM TEST WHERE col1 > -1" arr = GetFromDB(strSQL, lRows, strError, lDBHandle) StopSW "time for retrieving" If lRows = 0 Then If Len(strError) > 0 Then MsgBox strError, , "no rows returned" Else MsgBox "no rows returned", , "" End If OpenDBConnection strDB, True Exit Sub End If 'close the DB connection, don't have to do this '---------------------------------------------- OpenDBConnection strDB, True For r = 0 To UBound(arr) For c = 0 To UBound(arr, 2) If r = 0 And c = 0 Then strResult = arr(r, c) Else If r = 1 And c = 0 Then strResult = strResult & vbCrLf & _ "-------------------------" End If If c = LBound(arr, 2) Then strResult = strResult & vbCrLf & arr(r, c) Else strResult = strResult & vbTab & arr(r, c) End If End If Next Next MsgBox strResult, , strDB End Sub Sub ShowDBCollection() Dim i As Long Dim strResult As String If collSQLiteDB Is Nothing Then Exit Sub End If If collSQLiteDB.Count = 0 Then Exit Sub End If For i = 1 To collSQLiteDB.Count If i = 1 Then strResult = collSQLiteDB(i)(1) & vbTab & collSQLiteDB(i)(2) Else strResult = strResult & vbCrLf & _ collSQLiteDB(i)(1) & vbTab & collSQLiteDB(i)(2) End If Next MsgBox strResult, , "connected DB's" End Sub RBS -----Original Message----- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 24 January 2007 21:41 To: sqlite-users@sqlite.org Subject: RE: [sqlite] SQLite3VB.dll Thanks, that makes it clear. > SQLite implements the command "DELETE FROM table" without a WHERE clause > by dropping and recreating the table. Interesting, didn't know that. Maybe I should re-create when the majority or a lot of rows are deleted. RBS -----Original Message----- From: Trey Mack [mailto:[EMAIL PROTECTED] Sent: 24 January 2007 21:20 To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQLite3VB.dll > I have just one question. > What exactly does this function do: > > Private Declare Function sqlite3_changes _ > Lib "SQLite3VB.dll" (ByVal DB_Handle As Long) As Long > > It looks it will always give one, unless you have just created a database > and done nil with it. Is this how it is? It returns the number of rows affected by a delete, update, or insert (not updated by selects). It's not the number _changed_, but the number of rows addressed by the WHERE clause, whether an update actually changes a value or not. One special case is when you DELETE FROM table with no where clause.. explained below. >From sqlite3.h: /* ** This function returns the number of database rows that were changed ** (or inserted or deleted) by the most recent called sqlite3_exec(). ** ** All changes are counted, even if they were later undone by a ** ROLLBACK or ABORT. Except, changes associated with creating and ** dropping tables are not counted. ** ** If a callback invokes sqlite3_exec() recursively, then the changes ** in the inner, recursive call are counted together with the changes ** in the outer call. ** ** SQLite implements the command "DELETE FROM table" without a WHERE clause ** by dropping and recreating the table. (This is much faster than going ** through and deleting individual elements form the table.) Because of ** this optimization, the change count for "DELETE FROM table" will be ** zero regardless of the number of elements that were originally in the ** table. To get an accurate count of the number of rows deleted, use ** "DELETE FROM table WHERE 1" instead. */ - Trey ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------