HELP!! Easy... Using recordset Seek() method???

I have a DBF file that I need to be able to open, search, and edit.  Currently I just use a database object and recordset object and use the FindFirst method.  However, I was instructed that there was a much more efficient way.  I was instructed to create a tabledef object, create an index, open the recordset as Table-Type (dbopentable) and use the seek method.  However, I cannot seem to get this to work.  I was able to create the tabledef, and index (which actually creates an index file and places it in the directory of your application), but when I try to use the seek method, it tells me that the operation is not supported.

Here is the logic I am using.  My DBF file is called "Structur.dbf" in case you are wondering.

Set dbDbfDatabase = gwsMsJetRepository.OpenDatabase(App.Path, False, False, "dBASE III;")
Set tbdDstru = dbDbfDatabase.TableDefs("Structur")
Set idxDstru = tbdDstru.CreateIndex("Z_GISID")
idxDstru.Fields = "Z_GISID"
tbdDstru.Indexes.Append idxDstru
Set recDstruRcds = dbDbfDatabase.OpenRecordset(strSql, , dbOpenTable)
recDstruRcds.Seek "=", "Z_GISID='24298'"


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Try setting the index just before doing the Seek, as in:
recDstruRcds.Index = "Z_GISID"

I have no idea if Seek is supported with dBASE III

jdc0724Author Commented:
I tried doing that and got an "operation not supported for this type of object.  
since the index already exists you dont have to create it anymore

dim db as database
dim rs as recordset
set db=OpenDatabase(App.Path, False, False, "dBASE III;")
Set rs = db.OpenRecordset("Structur",dbOpenTable)
rs.Index = "Z_GISID"
rs.Seek "=", 24298
if rs.nomatch then 'not found
else 'found
end if


and i am supposing that 24298 is a number
if it is a string use "24298"
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

oh yes i have not tried this but it should work dBase after all is a ISAM system
however could be you have to say:
Set rs = db.OpenRecordset("Structur.dbf",dbOpenTable)

ie specify the full table name

jdc0724Author Commented:
The index needs to be created each time the job is run.  I will delete the index using winAPI calls at the beginning of each run.  I am able to open the database just fine with the code I listed above.  I also need to open the recordset using at SQL string, not specifying the actual dbf name (this is specified in the sql statement).  

Unfortunately everything works fine until I try to specify the index method on the recordset object.  This does not work.  And it seems without doing that, I cannot use the seek method.

>>The index needs to be created each time the job is run<<
Why does the index NEED to be created each time ???
>> I also need to open the recordset using at SQL string<<
Why do you NEED to do that ???

the bottom line is simple
either you use a dBase III file as it was intended to be used (ie as an ISAM db) and get great speed
or you do it the way you say you need to do it and you will get no speed at all

setting indexes and seeking on an index can only be done with ISAM type db's if you use them as ISAM type db's

if you use them as client-server db's (what you are doing) you can not set indexes and not seek on them
you will have to use the find* methods
jdc0724Author Commented:
The dbf file is recreated everyday using ESRI map objects. There is no primary key, and the column that would be a primary key had this been in an oracle or db2 table is the gisid I am trying to index.  This field is char(12) and is not fixed length (ie: not padded with zeroes), which if I had created it would of been numeric(12).  Long story short, what is done is done and I am given this file everyday.  If I did not recreate the index everyday then I wouldn't be working with a complete file.  Not an option.

As for using a sql string to open the file, well you are right in that I don't have to do that.  Originally, I had to since the GISID is not numeric and I used sql and scaler funtion(int) to convert the GISID to integer and sort the column.  As it stands right now, being character, the column is not sorted at all.
Oh i see now
i think you are out of luck, and will have to do it as you are doing
if i were you,i would suggest to the person creating that file,creating the index to.
if the program creating the file does not create an index on it, there may be a slight chance that the index can be generated automaticly,
in the directory where that file is created, make an .inf file specifying the indexes you want on that file
ie in your case:
Structur.inf --- the .inf file
[dBase III]
and now when that file is created there is a slight chance the index will be created automaticly (it will be created if done with vb) but it will have to be done by the person creating that file (sigh)

however there is a trap, you say the field is a character field, so the index will not be in numeric order but in alfabetic order,so if you do a seek dont expect to getting the next higher number by doing a movenext
however there is a method to use that character field as an numeric index:
have a 2dimentional numeric array say: <number,recordnumber>
loop trough the file and put the index field in number (as a number) and the recordnumber in recordnumber (ie the first record is 1, the second 2, etc...)
now you have an array with in it the GISID in the first element and the position in the second element) and you will have created an index in memory, with this array you can do whatever you want sorting,etc and you can do extremely fast binary searches on it
you will ofcource then have to open the dBase III file for binary access and use the standard vb io methods
the reason that will work is a dBase file has fixed lenght records and fields
this may seem like a lot of work and it is, but it has to be done just once, and you will get unparalleled speed with it (you will even beat foxpro...guaranteed)
and remember a dBase file has a fileheader so the records will be in position ((lenght of fileheader)+(lenght of record*position-lenght of record))
thats why you will have to use binary access instead of random access


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jdc0724Author Commented:
Helped provide alternative options to overcome a rare situation.  This is greatly appreciated.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.