Solved

Getting "index not found" querying DBase file

Posted on 2004-08-23
33
4,071 Views
Last Modified: 2013-12-20
I am trying to do some queries from VB6 into an old DBase file. If I do a "select * from mytable" I can return all the rows. However, it would be MUCH nicer to do a query like "select * from mytable where refnum = '12455'". The problem is that when I use a "where" clause, I get an "Index Not Found" error.

Here is the code I am using:

    Dim sql As String
    Dim MyConn As ADODB.Connection
    Dim RS As New ADODB.Recordset
   
    sql = "SELECT * From mytable where REFNO = '74963'"
    Set MyConn = New ADODB.Connection
    ' tried the following with same results
    ' MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=f:\;Extended Properties=dBASE IV;User ID=Admin;Password="

    MyConn.ConnectionString = "Driver={Microsoft dBASE Driver (*.dbf)};" & _
                  "DriverID=21;" & _
                  "Dbq=f:\;"

    MyConn.Open
    RS.Open sql, MyConn, adOpenForwardOnly, adLockReadOnly, adCmdText
0
Comment
Question by:KerryG
[X]
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
  • 15
  • 11
  • 4
  • +3
33 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 11874416
Have you tried:

sql = "SELECT * From mytable where REFNO = 74963"

Leon
0
 
LVL 8

Author Comment

by:KerryG
ID: 11874477
REFNO is actually a string field and not a numeric field, but the result is the same, the "index not found" error.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11874705
Have you tried this:

sql = "SELECT REFNO  From mytable"

I am just wondering if it would return just that column.

Leon
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 8

Author Comment

by:KerryG
ID: 11875246
yes, that returned the same error.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11875479
When you return the recordset witht he wildcard (*), go through the fields collection and see what are the filed names returned.  There may be extra characters which we are not seeing.  Return it as:

Debug.Print "[" & RS.Fields(intCount).Name & "]"

this way we may be able to spot the exta stuff inside the [] characters.

Leon
0
 
LVL 8

Author Comment

by:KerryG
ID: 11894786
Using that code I get:

"Item cannot be found in the collection corresponding to the requested name or ordinal"
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11894941
Ok, lets try this:

For intCount = 1 to RS.Fields.Count
    If FieldIsString(RS.Fields(intCount)) Then
        Debug.Print "[" & RS.Fields(intCount).Name & "]"
    End If
Next

Private Function FieldIsString(FieldObject As ADODB.Field) As Boolean
' From http://www.freevbcode.com/ShowCode.asp?ID=279
    If Not TypeOf FieldObject Is ADODB.Field Then Exit Function
        Select Case FieldObject.Type
            Case adBSTR, adChar, adVarChar, adWChar, _
               adVarWChar, adLongVarChar, adLongVarWChar
                FieldIsString = True
            Case Else
                FieldIsString = False
        End Select
End Function
0
 
LVL 8

Author Comment

by:KerryG
ID: 11895047
The problem definitly seems to be that I can't do certain queries without accessing the index file. Although the index file (.cdx) is there, ADO/RDO/ODBC doesn't seem to use it. I have read other places about installing the BDE but the main package for it wont install without Delphi installed so that isn't a workable solution.

Granted, I can fetch all 100,000 records and do a compare for the ONE record I am looking for, but that's a bit slow to say the least.
0
 
LVL 8

Author Comment

by:KerryG
ID: 11895527
Here is the output from your code:

[DMVCHK]
[DMVREG]
[VOTECHK]
[PHONCHK]
[CB_CODE]
[EXPORT]
[PDC_DATA]
[OTH_DATA]
    996]
[    997]
[    998]
[    999]

It then errored after that with "Item cannot be found in the collection corresponding to the requested name or ordinal"
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11895559
How many columns are in the table? I do not see REFNO  as one of the column names.

Leon
0
 
LVL 8

Author Comment

by:KerryG
ID: 11895653
There are 88 fields. Those returned ones are not in any order nor are the last 4 numbers one part of the fields.
0
 
LVL 8

Author Comment

by:KerryG
ID: 11895662
I reran it after fixing a small code error:

[ADDRESS1]
[ADDRESS2]
[SQADD]
[AGECOMMENT]
[AGREFNO]
[CITY]
[CLIENTKEY]
[COLLCODE]
[DEBTYPE]
[DRIVERLIC]
[HARD_LETT]
[HPHONE]
[LASTLETER]
[LETTERSEQ]
[LASTNAME]
[FIRSTNAME]
[COMMNAME]
[CONTACT1]
[CONTACT2]
[INDUST]
[TAKEINT]
[NEXTLETTER]
[PAYPLAN]
[PRESTAT]
[REF1]
[REF2]
[SALUTAION]
[SEX]
[SINGLELET]
[COFIRST]
[COLAST]
[SSN]
[STATE]
[STATUS]
[WPHONE]
[ZIP]
[TRANAGCY]
[CLISTAT]
[PATIENT]
[DEBTREASON]
[ORIGCRED]
[EQUIPDESC]
[EQUIP2]
[PRIORITY]
[REASON]
[CASENO]
[CBRCHK]
[XXCHK]
[OPER411]
[CTACHK]
[DMVCHK]
[DMVREG]
[VOTECHK]
[PHONCHK]
[CB_CODE]
[EXPORT]
[PDC_DATA]
[OTH_DATA]
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11895691
I still do not see REFNO  as one of the column names.  Maybe you should try AGREFNO instead?

Leon
0
 
LVL 8

Author Comment

by:KerryG
ID: 11895705
sorry, its actually AGREFNO, the 5th one down, I hand typed the first section of code into here.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11895746
So what happens if you try:

sql = "SELECT * From mytable where AGREFNO = '74963'"

or

sql = "SELECT AGREFNO From mytable"

Are you still getting an error?

Leon
0
 
LVL 8

Author Comment

by:KerryG
ID: 11904558
sql = "SELECT * From mytable where AGREFNO = '74963'"

Run-time error '-2147217900 (80040e14)':
[Microsoft][ODBC dBase Driver] Index not found.
-------------------
sql = "SELECT AGREFNO From mytable"

Returns all of the records
-------------------
sql = "SELECT AGREFNO From mytable where AGREFNO = '99475'

Run-time error '-2147217900 (80040e14)':
[Microsoft][ODBC dBase Driver] Index not found.


As it is right now, the only way I seem to be able to get around this is to select * and then do a string compare against every record to look for the match I am looking for. Highly ineffecient to say the least.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11904790
Ok, one more thing, lets try:

sql = "SELECT AGREFNO From mytable where AGREFNO LIKE '%99475%'

Leon
0
 
LVL 8

Author Comment

by:KerryG
ID: 11904838
Guess dBase doesn't like LIKE. Every variation on it returns a syntax error.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11904885
Have you tried limiting the WHERE clause on other fields?  I would like to know if it acts like this in every case or only for certain cases.

Leon
0
 
LVL 8

Author Comment

by:KerryG
ID: 11905021
Ok, I tried it on a few other fields, Status, FirstName, LastName, and State all work, but an Index Not found occurs on AGREFNO, SSN, Tickle, and ActionDate. So it seems to be pretty hit or miss and of course, the one way I really would like to search on (with SSN as a secondary) doesnt work.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11905094
It seems like the fields you are having problems with are numeric in nature.  Lets try this as a test:

sql = "SELECT * From mytable where AGREFNO > 1"
0
 
LVL 8

Author Comment

by:KerryG
ID: 11905322
Nope, index not found.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11905537
Ok, I am out of ideas.  Try posting a pointer (20pts) to this question in the Database area.  Maybe someone there will know wha is happening.

Leon
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 11913203
I suspect what is happeing is that it is not using ISAM.  Take a look at this article from MSDN
How to control whether Access loads the Borland Data Engine (BDE)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;307455

And make sure the registry entry is set to 2.  

Caveat: If you are not familiar with the Registry or not comfortable with changing thing there, do not even attempt this.
0
 
LVL 8

Author Comment

by:KerryG
ID: 11913289
I have tried installing the BDE but that registry entry is never there.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11913314
My point was that you do not need BDE.  Try creating the registry entry.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11913340
But is seems BobLamberson has the right idea in your original question here:
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21076398.html#11913277
so I will bow out.
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 11914671
Hi KerryG,
This idea might solve part of your immediate problem.
> sql = "SELECT * From mytable where REFNO = '74963'"
try
sql = "SELECT * From mytable where val(REFNO) = 74963"

for some reason my dbf would find this when it wouldn't find the string.


Bob
0
 
LVL 8

Author Comment

by:KerryG
ID: 11914934
Manually adding the registry key did the trick. Its not fast, but it works.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11916528
>>Its not fast, but it works.<<
Exactly.  You are not using any index.
0
 
LVL 8

Author Comment

by:KerryG
ID: 11916783
I wish there was a way to use the index but at least this will work for now.
0
 

Expert Comment

by:khinvra
ID: 12981533
Did you able to to solve the problem ? as I am getting the same problem.

Rahul
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 13835484
For a .cdx index file, you have to use the proper connectivity driver to get the benefit of it.

This is not dBASE and BDE doesn't apply at all.

If you have a .dbf file nowadays, more often than not, it is from a FoxPro application.  And, since you have a .cdx file, that tells you it is a FoxPro compound index file and dBASE can't use it and certainly can't take advantage of it to get any speed advantage that you would like in your queries.

Download the free MS ODBC Driver for VFP at the MS Downloads site and use it rather than any lowest common denominator dBASE driver or any other Borland database add-on.
0

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

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
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…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

728 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