Solved

Dbase Numeric Primary Key Search

Posted on 2009-04-11
10
639 Views
Last Modified: 2013-11-24
I am able to query a DBase table with every field except the first field which is both numeric and the primary key.  When I involve this field in any select query (in the where clause) the records returned are zero.  This is not an issue of NULL (none of them are null).  I found an almost exact question and answer in the following link but the answer was basically sucks-to-be-you if you are trying to query that field.  Unfortunatly, this is a database table for our accounting software so I have no control over the table stucture and there is no other way to select (or, more specifically, do an update to a single record) that ensures that you are getting a unique record.  Is there any workaround to this?  Any ideas?  I have made sure my Microsoft Dbase drivers are the most up to date and still no luck...    Previous question:  http://www.experts-exchange.com/Microsoft/Applications/FoxPro/Q_20912375.html

Any and all help is appreciated...  I have been banging my head against this for about 12 hours...
0
Comment
Question by:brandonmonahan
  • 6
  • 4
10 Comments
 
LVL 41

Expert Comment

by:pcelba
ID: 24125472
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?
0
 

Author Comment

by:brandonmonahan
ID: 24126011
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

Open in new window

SCSOL---.XLS
0
 

Author Comment

by:brandonmonahan
ID: 24126184
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

Open in new window

0
 
LVL 41

Accepted Solution

by:
pcelba earned 500 total points
ID: 24126370
This file is not dBase III file because it contains "structural index file" bit set to 1. Either it is dBase IV or FoxPro DBF but it should not affect results. Unfortunately, you did not provide the index file, so my tests are not complete. I've created index on the first numeric column by Visual FoxPro.

I've opened your file (renamed to SCSOL.DBF) using the Microsoft dBase driver and executed several queries, like "select * from SCSOL WHERE oetltrnum = 1" etc. And results were correct... Everything worked even without index file.

All SQL commands were executed via ODBC connections established from Visual FoxPro. So the only possibility is the index which is probably not compatible to your ODBC driver.

Options are:
1) Look at your ODBC driver version (my ODBC supports even dBase V and its version is 6.00.6001.18000)
2) Copy your DBF data tables to another folder without index files and try it again

I've found one more driver - Microsoft Access dBase driver which works fine also.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 24126376
Your last SELECT example retrieved 3 records.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:brandonmonahan
ID: 24126708
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
0
 
LVL 41

Expert Comment

by:pcelba
ID: 24126803
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?
0
 
LVL 41

Expert Comment

by:pcelba
ID: 24126829
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.
0
 

Author Closing Comment

by:brandonmonahan
ID: 31569265
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.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 24129324
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.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
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 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…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

746 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

11 Experts available now in Live!

Get 1:1 Help Now