Solved

Getting "index not found" querying DBase file

Posted on 2004-08-23
33
3,856 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
  • 15
  • 11
  • 4
  • +3
33 Comments
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
Have you tried:

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

Leon
0
 
LVL 8

Author Comment

by:KerryG
Comment Utility
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
Comment Utility
Have you tried this:

sql = "SELECT REFNO  From mytable"

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

Leon
0
 
LVL 8

Author Comment

by:KerryG
Comment Utility
yes, that returned the same error.
0
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
Ok, one more thing, lets try:

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

Leon
0
 
LVL 8

Author Comment

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

Expert Comment

by:leonstryker
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Nope, index not found.
0
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
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
Comment Utility
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
Comment Utility
I have tried installing the BDE but that registry entry is never there.
0
 
LVL 75

Expert Comment

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

Expert Comment

by:Anthony Perkins
Comment Utility
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:BobLamberson
Comment Utility
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
Comment Utility
Manually adding the registry key did the trick. Its not fast, but it works.
0
 
LVL 75

Expert Comment

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

Author Comment

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

Expert Comment

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

Rahul
0
 
LVL 33

Expert Comment

by:CarlWarner
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now