Hi - I'd try creating the name field as an additional index.
You can read about it here on the
SQLite site.
As a side note, you can create tables by storing the creation script as a file, reading it in and executing it as a SQL command.
This is an example that I've used to create a 'Category' table.
CREATE TABLE 'Category' (
'Id' INTEGER PRIMARY KEY,
'CategoryName' VARCHAR(128),
'ParentId' INTEGER,
'CategoryUse' INTEGER,
'Information' TEXT,
'DateCreated' DATE,
'DateChanged' DATE);
I find this better than using the code creation method as you can edit an existing file and tweak for new fields and name and it takes far less time to create large table sets.
If you have an existing database with a lot of tables, you can create a backup of all your tables with this subroutine:
Public Function CreateSQLiteScriptText(DropTableClause As Boolean, InName As String) As String
Dim TmpLine As String
Dim KeyStr As String = ""
Dim LastLine As Integer
Dim TmpTable As Table
Dim TmpField As Field
Dim ReturnStr As String = ""
TmpTable = $Con.Tables[InName]
If DropTableClause Then TmpLine = "DROP TABLE IF EXISTS '" & InName & "';" & Gb.NewLine
TmpLine &= "CREATE TABLE '" & InName & "' (" & Gb.NewLine
LastLine = TmpTable.Fields.Count - 1
For Each TmpField In TmpTable.Fields
KeyStr = ""
TmpLine &= "'" & TmpField.Name & "' "
If CheckPrimaryKey(TmpTable, TmpField.Name) <> "" Then KeyStr = " PRIMARY KEY"
If TmpField.Type = Gb.String Then
If TmpField.Length = 0 Then
TmpLine &= "TEXT"
Else
TmpLine &= GetFieldType(False, TmpField.Type) & "(" & TmpField.Length & ")" & KeyStr
Endif
Else
TmpLine &= GetFieldType(False, TmpField.Type) & KeyStr
Endif
If LastLine > 0 Then
TmpLine &= "," & Gb.NewLine
Else
TmpLine &= ");" & Gb.NewLine
Endif
LastLine -= 1
ReturnStr &= TmpLine
TmpLine = ""
Next
Finally
Return ReturnStr
Catch
Message( Error.Text & Gb.CrLf & Error.Backtrace)
End
...and create them all with this one.
Public Sub CreateTablesFromSuppliedScripts(InFolder As String, ByRef $InCon As Connection)
Dim TmpFile As String
Dim FilesAry As String[]
Dim SQLCommand As String
FilesAry = Dir(InFolder, "*.sql", Gb.File)
If FilesAry.Count > 0 Then
If Not $InCon.Opened Then $InCon.Open
For Each TmpFile In FilesAry
SQLCommand = File.Load(InFolder &/ TmpFile) ' Load the entire script from the file
$InCon.Exec(SQLCommand) 'Execute the Create Table script.
Next
$InCon.Close
Endif
Catch
Message( Error.Text & GB.CrLf & Error.Backtrace)
End
Cheers - Quin.
I code therefore I am