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

Posted on 2001-09-07
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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"
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.


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 50 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

734 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