Dbase Numeric Primary Key Search

First of all we don't know what dBase version uses your accounting system. Second, did you try to quesry this table directly from dBase or FoxPro? Third, could you please attach some sample data, like 5 records from your table on which we could reproduce this behaviour? Fourth, did you try to use Microsoft Visual FoxPro ODBC driver instead of dBase?

1.  Dbase III
2.  No.  I don't have Fox Pro or Dbase - just drivers to query against them.  I have done the query through the filter system of DBF Viewer - which did work without difficulty.  
3. Good thought on that.  I am going to attach a table that I am having trouble with.  In the table, I am unable to query on the OETLTRNUM column.  PLEASE NOTE - It is not an XLS File - I just changed the extension so I could upload it.  You will need to change the extension back to DBF
4. No - I hadn't - but I have tried 3 different drivers now with the same results.

I want to stress that the code work excellent - and I am able to query on any column but the first - once I do that it returns no rows.

I did put some code below.  Because I use the data connection in many places I have made it a function - so I had to put stuff back-together.  The function is easy - you pass it SQL - it passes back an Array.

Thank you for your help.

Dim strCougarConnection
 
strCougarConnection = "Driver={Microsoft dBase Driver (*.dbf)};DriverID=277;DBQ=C:\CMSWIN\SCS;"
 
    Dim cxn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rst As New Recordset
    Dim arrString() As Variant
 
    If conn = "Cougar" Then
        cxn.ConnectionString = strCougarConnection
    ElseIf conn = "Web" Then
        cxn.ConnectionString = strWebConnection
    End If
    
    cxn.Open
    
    cmd.ActiveConnection = cxn
 
    cmd.CommandText = SQL
    Set rst = cmd.Execute
        If returnResults Then
            If Not rst.EOF Then
                arrString() = rst.GetRows
            End If
            processSQL = arrString
        End If
    cxn.Close
    Set rst = Nothing

Select allOpen in new window

SCSOL---.XLS

I forgot the value of the SQL variable...  Just for reference - the end result will look something like:

Select OETLTRNUM, OETLDETAIL, OETLSTK, OETLDES, OETLOQTY, OETLPRICE, OETLBNUM from "SCSOL$$$.DBF" where OETLBNUM = 'AAW' and OETLTRNUM = 3

SQL = "Select OETLTRNUM, OETLDETAIL, OETLSTK, OETLDES, OETLOQTY, OETLPRICE, OETLBNUM from ""SCSOL$$$.DBF"" where OETLBNUM = '" & batch & "' and OETLTRNUM = " & MainID

Select allOpen in new window

Your last SELECT example retrieved 3 records.

Excellent - I think you got it!  I moved it into a seperate folder than the index file and the query worked just fine returning records.  Sorry about the version - that is just what DBF viewer told me :).  Anyway, here is the index file (rename back to MDX).  These indexes are generated by the accounting program (Cougar Mountain) so I don't have control of them.  The question I have, is there anything I can do programatically to make it work?  If I have to, I can copy DBF out into a new folder and then copy it back in after doing the selects and edits but that is a bit silly.  Either way - you have got it.  Thank you so much for your help - and happy Easter!
P.S. - In this table, returning multiple records for that query is fine - it is that the OETLTRNUM the unique tie between it and another table.  BTW - I have this same problem on all my tables I am trying to query against the first numeric column.
SCSOL---.XLS

MDX means dBase (IV or V) files. You don't need index file for SELECTs but when you update data, index must be updated also. If you don't update index then your accounting program would crash or calculate wrong numbers without any warning...

The problem is most probably in ODBC driver which is not able to process MDX index files (selected records are wrong for me also - instead OETLTRNUM = 3 it returns 1 and 2).

One possibility exists: If you update DBF data outside the accounting application (which is always dangerous!) then you have to REINDEX these data before their next use. Cougar Mountain should have some Data maintenance functionality and reindexing should be one of possible options (other options are pack and probably data archive).

Instead of copying DBF files to another folder you may simply rename appropriate MDX file and work with DBFs directly, then rename index file back to MDX and reindex in accounting app. But, again, you have to do a backup first.

Reindexing is not necessary if you don't update key columns. But do you know all columns which are not used for indexing?

FYI, I've been looking at index expressions in your MDX:

1) UPPER(oetlbnum)+STR(oetltrnum)+STR(oetldetail)
2) STR(oetlltype)+UPPER(oetlbnum)+STR(oetltrnum)+STR(oetldetail)
3) UPPER(oetlbnum)+UPPER(oetlstk)
4) UPPER(oetlbnum)+UPPER(oetldes)

Thank you, there was new observation for me in your question: "Microsoft dBase ODBC driver does not support MDX indexes".

And now really Happy Easter.

Thank you for your help.  This will get me past this hurdle.  Great responses...  My guess now is that the index does not match the DBF - I will run the Reindex through Cougar and then check - that is probably why I was getting no rows.  Thanks again.

Above indexes cannot be used to optimize your queries because their index expressions are too complex. I would say even the simple presence of MDX file is problematic for ODBC driver.