Go Premium for a chance to win a PS4. Enter to Win


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

Posted on 2001-09-07
Medium Priority
Last Modified: 2013-11-25
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'"


Question by:jdc0724
  • 4
  • 4
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6464290
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


Author Comment

ID: 6464489
I tried doing that and got an "operation not supported for this type of object.  

Expert Comment

ID: 6465349
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"
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 6465363
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


Author Comment

ID: 6471925
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.


Expert Comment

ID: 6472056
>>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

Author Comment

ID: 6472463
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.

Accepted Solution

pierrecampe earned 200 total points
ID: 6474173
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


Author Comment

ID: 6476624
Helped provide alternative options to overcome a rare situation.  This is greatly appreciated.

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question