jrc4728
asked on
VBA SQL DBF Query - How can I speed this up?
I have a VBA script that does a SQL query of a Visual Fox Pro (DBF) file. I have to use wildcards in the query. The query runs really slow (approx 40 seconds). Any ideas on how to speed things up. See attached code. If I specify a single record, results are almost instant. Since I dont have the ability to add a key field to the DBF file, I fear I am out of luck getting any more speed.
Dim con_var As String
var1 = "AB"
var = "______________" & var1 & "%"
Set cont = CreateObject("ADODB.Connection")
Set rcont = CreateObject("ADODB.Recordset")
constr = "Driver={Microsoft Visual FoxPro Driver}"
constr = constr & ";UID="
constr = constr & ";SourceDB=\\server\path"
constr = constr & ";SourceType=DBF"
constr = constr & ";Exclusive=No"
constr = constr & ";BackgroundFetch=No"
constr = constr & ";Collate=Machine"
constr = constr & ";Null=No"
constr = constr & ";Deleted=Yes"
constr = constr & ";Mode=Read;"
cont.Connectionstring = constr
cont.Open
c1SQL = "select keys, field1, field2 "
c1SQL = c1SQL & "from 'datafile' "
c1SQL = c1SQL & "where keys like '"
c1SQL = c1SQL & var
c1SQL = c1SQL & "'"
rcont.CursorLocation = 3
DoEvents
rcont.Open c1SQL, cont, 3, 3
rcont.fields("keys").Properties("optimize").Value = True
rcont.Sort = "keys"
rcont.movefirst
cr = 2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry. I thought you could create the index. You can try to get the index created from whereever you got the database from. It will make a huge difference to the speed of your select. If you need, I can attach an exe that will create the index for you. Though from your code, it looks like you have readonly access to the tables. In which case nothing can be done.
ASKER
Yes. It is read only. I sort of figured I was stuck here. Just looking for any way to speed things up.
ASKER