Solved

Getting "index not found" querying DBase file

Posted on 2004-08-23
33
4,018 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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:BobLamberson
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sharepoint 2013 Library List View Limitations 9 73
Passing a Text Box name to a Sub 6 102
VBA: Insert New column with specific format type 12 52
Problem to line 23 73
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

735 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