When you create your indexes, you can do so without regard to casing by using the COLLATE NOCASE statement.
CREATE INDEX [Company] ON [Customers] ([Company] COLLATE NOCASE);
Also, when you create your index, shouldn't the fields making up the multiple index key be separated?
You are doing this:
slExe "CREATE INDEX Company ON Customers UPPER ((Company & rowid));", "E0"
When maybe you could do this?
slExe "CREATE INDEX Company ON Customers (Company COLLATE NOCASE, rowid);", "E0"
Also, when creating all of the tables and indexes, you can combine multiple slExe calls into one:
'We are doing a transaction, sending several SQL statements to the server
'Build tables and indexes
t1 = "BEGIN IMMEDIATE;" & _
" CREATE TABLE Customers (" & Flds & ");" & _
" CREATE INDEX NCPDP ON Customers (NCPDP);" & _
" CREATE INDEX Company ON Customers (Company, RowID);" & _
" CREATE INDEX [A-Exp] ON Customers ([A-Exp]);" & _
" CREATE INDEX [L-Exp] ON Customers ([L-Exp]);" & _
" CREATE INDEX [O-Exp] ON Customers ([O-Exp]);" & _
" CREATE INDEX State ON Customers (State, RowID);" & _
"END;"
slExe t1, "E0"
I see what you are trying to do with the current record and displaying the 3 most previous and 3 records afterwards. Unless I was dealing with a million records then I would simply do a SELECT with an ORDER BY and bring the whole data set into a local array and dealing with navigating that array of data rather than constantly trying to SELECT the 3 previous and 3 next records. It just seems like it would be a hell of a lot simpler design and easier to maintain. That's just my opinion of course. You may have a much more valid reason for doing what you are doing.
