Have done one simple test now and for now it looks SQLiteDB is about twice
as fast as the ODBC driver. I am a novice with SQLite, so maybe my test is
no good and in that I am interested to know.
I have tested on a large file (few millions rows and 19 fields) and I have
set an index on the field to search. This is the relevant code:
Option Explicit
Private lStartTime As Long
Public Declare Function timeGetTime Lib "winmm.dll" () As Long
Private strConn As String
Private ADOConn As ADODB.Connection
Private cn As SQLiteDb.Connection
Sub StartSW()
lStartTime = timeGetTime()
End Sub
Sub StopSW(Optional ByRef strMessage As Variant = "")
MsgBox "Done in " & timeGetTime() - lStartTime & " msecs", , strMessage
End Sub
Sub SearchSQLite()
Dim sqliteRS As SQLiteDb.Recordset
Dim strSQL As String
Dim arr
'On Error GoTo ERROROUT
SetSQLiteConn
StartSW
strSQL = "SELECT PATIENT_ID, TERM_TEXT, ADDED_DATE, START_DATE FROM
[ENTRY] WHERE TERM_TEXT like '%angina%'"
Set sqliteRS = cn.Execute(strSQL)
If sqliteRS.EOF Then
Exit Sub
End If
arr = sqliteRS.GetRows
StopSW
MsgBox UBound(arr, 2) + 1
Exit Sub
ERROROUT:
MsgBox Err.Description & vbCrLf & vbCrLf & _
"Error number: " & Err.Number & vbCrLf & _
"Error line: " & Erl
End Sub
Sub SetADOConn()
If ADOConn Is Nothing Then
Set ADOConn = New ADODB.Connection
End If
If ADOConn.State = 0 Then
'strConn =
"DSN=SQLite3;Database=C:\SQLite\Terra\rc2.db;SyncPragma=Off;"
strConn = "Provider=MSDASQL.1;" & _
"Extended Properties=DSN=SQLite3;" & _
"Database=C:\SQLite\Terra\rc2.db;" & _
"StepAPI=0;" & _
"SyncPragma=Off;" & _
"NoTXN=0;" & _
"Timeout=100000;" & _
"LongNames=0;" & _
"NoCreat=0"
ADOConn.Open strConn
End If
End Sub
Sub SetSQLiteConn()
If cn Is Nothing Then
Set cn = New SQLiteDb.Connection
End If
If cn.State = 0 Then
cn.ConnectionString = "Data Source=C:\SQLite\Terra\rc2.db"
cn.Open
cn.Execute "PRAGMA synchronous=off;", , slExecuteNoRecords ' Just to
speed up things
cn.Execute "PRAGMA encoding='UTF-8';", , slExecuteNoRecords
End If
End Sub
Sub SearchSQLiteODBC()
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim arr
Dim i As Long
Dim c As Long
On Error GoTo ERROROUT
SetADOConn
StartSW
'MsgBox ADOConn.ConnectionString
strSQL = "SELECT PATIENT_ID, TERM_TEXT, ADDED_DATE, START_DATE FROM
[ENTRY] WHERE TERM_TEXT like '%angina%'"
'strSQL = "SELECT PATIENT_ID, TERM_TEXT, ADDED_DATE, START_DATE FROM
ENTRY WHERE READ_CODE GLOB 'G3*'"
Set rs = New ADODB.Recordset
rs.Open Source:=strSQL, _
ActiveConnection:=ADOConn, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly, _
Options:=adCmdText
If rs.EOF Then
Set rs = Nothing
Exit Sub
End If
arr = rs.GetRows
StopSW
MsgBox UBound(arr, 2) + 1
Exit Sub
ERROROUT:
MsgBox Err.Description & vbCrLf & vbCrLf & _
"Error number: " & Err.Number & vbCrLf & _
"Error line: " & Erl
End Sub
RBS
-----Original Message-----
From: Carlos Avogaro [mailto:[EMAIL PROTECTED]
Sent: 13 November 2006 12:04
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What is the best way to connect to SQLite from VB/VBA?
With the odbc driver, is ease and faster
RB Smissaert <[EMAIL PROTECTED]> wrote: Have spent 2 days
looking at all the different wrappers and the one ODBC
driver and maybe the best one is the commercial dll from Terra...
Still, I would be very interested what opinions are about the best (speed,
ease of use, so methods etc. close to ADO) way to connect.
This is from VB6/VBA. The database I am working with is Interbase 5.6 and
I will have to move data from IB to SQLite.
RBS
----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-
---------------------------------
Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
