Opening Foxpro in Access or VB via ADO behaving strangely.

Well, the information above is just an example.  The equivalent field in the real structure is a number field and if you try to put the search criteria in single quotes, it returns a data type error.

What is a number field?

FoxPro could use all kinds of field types to hold an ID value.  It would be good to know which type it is so we can try to decipher your situation.  VFP9 allows Field Types of Numeric, Double, Float, Integer, and Integer with AutoIncrementing enabled.  I suppose it would be good also to know which version of VFP the data is operating in.

Also, what mechanism are you using to get to the FoxPro data via ADO?

The latest OLE DB Provider for VFP is here (backward compatible to earlier VFP versions):

Microsoft OLE DB Provider for Visual FoxPro 9.0
http://www.microsoft.com/downloads/details.aspx?FamilyID=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&DisplayLang=en

Your other post in the VB section says you're using ODBC.  ODBC is fine IF the Fox data isn't newer than VFP6 and supporting some newer features that have come along in VFP7, VFP8, or VFP9.

Since I surely can't tell what versions of anything you're using from here, I have to ask that you know what versions of everything you are using in order to nail down your environment.

As was the case when I thought you might be using an older OLE DB Provider for VFP, you should check to be sure you are using the latest ODBC driver, if that is the proper connectivity mechansim you need for your setup.

Here is a link to the very latest ODBC driver that was released in the VFP6 timeframe:

Visual FoxPro ODBC Driver
http://msdn.microsoft.com/vfoxpro/downloads/updates/odbc/default.aspx

If you're using a wrong or older connectivity driver, you could get incorrect results.

  I sure do wish I could be more specific.  The original application was written in FoxPro, that's all I know and that's only because it has .dbf, .cdx and .fpt files which do open up in Access or VB when told to use the FoxPro driver.  
    Since FoxPro is not installed on the machine nor does there appear to be a runtime engine, I'm assuming it was written Visual FoxPro but then again, I could REALLY be showing what I don't know about FoxPro.  
   The program has been around for several years but this one shows a build date of June 23, 2003.  I had performed all the latest and greatest updates and my FoxPro ODBC driver is 6.01.8629.01.  Not having a copy of FoxPro, all I can do is tell you what Access thinks the field is.  The particular field in question shows as a Long Integer but it may be worth noting that even if I try "select * from clients where fullname = 'john smith'" I get the same results so the problem occurs with a text field as well.  I can be staring right straight at the record for John Smith but it will return an empty recordset unless there happen to be 2 or more John Smiths.
   I've done it using DSN in Access and both with DSN and a DSN-less connection string in Visual Basic and get the same results all the way around.  I did find out that the company I'm doing this for has upgraded their software recently.  I may just have to call the author and see if he'll cut loose with that info.  You're right, it would definately be more helpful If I knew what version it was written in.

It does sound as though you have the latest ODBC driver.  At least that is good.

But, a bit more info might help; for example, It could be a VFP8 application.

What happens if you try a LIKE clause instead of the equal one?

"select * from clients where UPPER(fullname) LIKE 'JOHN SMITH%'"

I'm just wondering if that even returns a correct result.  ???

Oops.  I may have misspoken earlier.  (May have forgotten about case sensitivity in Access).  However, the latest findings are t hat I CAN match to a single record if it is a text field.  It's the numeric value that won't work. So that prompted me to say "Well, let's change that" and the findings are rather interesting.
"Select * from clients where str(ID) = '5555'" did not work
"Select * from clients where str(ID) like '555%'" did not work
"Select * from clients where str(ID) = '%555'" DID work
So it appears that there is some sort of invisible leading character in that field.

Oops.  Correct the one that DID work to have the "LIKE" operator instead of the "="

If there is an invisible leading character, I sure couldn't tell from here.  And that would make me wonder either about the quality/integrity of the data or again to the setup of the mechanism for connectivity to that data.

Well, surprise surprise.  I just spoke to someone at the company and they tell me it was NOT written in Foxpro but in Delphi using standard windows DBase files (sounds strange to me).  It seems awfully coincidental that .dbf, .cdx and .fpt files are used by both.  I'll do some more playing around tonight and see what it all means.

I know of no other db system other than FoxPro that use the .fpt extension for memo field files.  And, keep in mind, that Delphi is NOT a database system, but a programming language environment.  They should be able to attach their programming code to all sorts of data.  dBase files would be .dbf, ndx or mdx, and dbt, rather than FoxPro's .dbf, idx or cdx, and ftp file extensions.  They are still using FoxPro table conventions to hold the data, regardless of the language they've chosen to get to the data.

So, don't make the assumption that the data is NOT FoxPro data.

If ODBC doesn't work, that's fine.
But, if dBase drivers also don't work (and I see no reason why they should), that's fine.

But, if you find the OLE DB Provider for VFP works on the data, that is proof enough that the data you're hitting is something in the range of Visual FoxPro versions 7 through 9.

BTW, since we seem to get closer to a real definition of what is in place here, are the tables (.dbf files) part of a database; i.e., in all versions of Visual FoxPro, there is the strong possibility that each table (.dbf) is part of a database container (.dbc).  If you have a .dbc file (and a .dcx and .dct file to go with it), you have a database in which there are references to all of the tables it acts as traffic cop for.  In each table's header is a backlink referencing the name of the database container as its owner.  This is why common dBASE drivers won't access many Visual FoxPro tables any longer-- the format has changed enough where they just aren't really compatible any more.  And, if a database container is in the mix, you should reference that when you use the OLE DB Provider for VFP.  Once that driver has a hold of the .dbc, it is smart enough to know what tables it owns and you can simply referenced them in your normal SQL SELECTs.  If it doesn't like the name of one table you reference, most likely that table is not own by the database container and is considered a "free table".

I agree completely but they were more interested in making sure I felt like I didn't know what I was doing than helping me to define the problem.  Apparently they are currently developing the same type of thig I am.   It still looks like I need to find another driver.  I downloaded a viewer called CDBF Viewer and it handled it just fine (it also thinks they are FoxPro Tables).  Here's something new that scares me.  I typed the following query "\

Adodc1.RecordSource = "select * from wvsscli where str(CLI_NUM) like '%" & Text4.Text & "'"

And in my VB App I get an error ADODC1: Variable CLI_BATLYPE is not found.  I'm not sure where it's getting it's info but when I look at that table with any other product (including the CDBF Viewer) The only field in that table is CLI_BALTYP.  So I thought I'd get cute and define the few fields I wanted "select CLI_LAST1, CLI_LAST1, CLI_ADDR1 from ...." and it returned the same error!!  

Do you know of anyone out there who makes a 3rd party Odbc driver for FoxPro files?  I tried DataDirect's but they are looking for a "Database Container File" (I think that's a .DBC extension) and claims FoxPro3 support.  I don't have the .DBC file and in Microsoft's driver I have to select "Free Table Directory".  That may help identify the age/version of FoxPro files.

Using the DSN-Less method of connecting via ODBC:

ODBC Driver for Visual FoxPro
http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForVisualFoxPro

Without a database container meaning "free tables" only:

Without a database container (Free Table Directory)

oConn.Open "Driver={Microsoft Visual FoxPro Driver};" & _
           "SourceType=DBF;" & _
           "SourceDB=c:\somepath\mySourceDbFolder;" & _
           "Exclusive=No"

I forgot to mention that any driver I've seen that supports the .dbc files also supports the free table .dbfs.  They mention/highlight the .dbc extension, because most Visual FoxPro systems done in the last 10 years use the database container (.dbc) and don't rely solely on the "free table" .dbfs.

In case you're wondering what the database container adds to the environment, it adds stuff like stored procedures, rules, triggers, and database event support that was never possible with using free table .dbfs.

This has been going on long enough where I'm wondering just what weirdness is in your data or in your connectivity setup.

Is the file you're using so confidential or proprietary that you couldn't zip it up and e-mail it to me at the address in my profile so I could test some ADO commands against it myself?

Just a thought...

Would that be the same as using the Connection String Property?  I have that set to
Provider=VFPOLEDB.1;Data Source=C:\PROGRAM FILES\DATA;Password="";Collating Sequence=MACHINE
which qualifies as a DSN-less connection.  That is what I've been using in Visual Basic and have been using ODBC in Access.  I get similar results in both arenas.  They use VFPOLEDB.DLL(version 9.0.0.2412) and VFPODBC.DLL (version 6.0.8428.0) respectively so I wonder if the problem is at some common layer.

Just the fact that you are calling out VFPOLEDB means you aren't using the ODVC driver, which you keep insisting that you are using.  You are calling the OLE DB Provider for VFP by using the call to VFPOLEDB.

OLE DB Provider for Visual FoxPro
http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForVisualFoxPro

oConn.Open "Provider=vfpoledb;" & _
           "Data Source=C:\vfp8\Samples\Data\myVFPDB.dbc;" & _
           "Mode=ReadWrite|Share Deny None;" & _
           "Collating Sequence=MACHINE;" & _
           "Password=''"

You don't have to use the .dbc extension, even though they always seem to call it out by default.  You just point to the drive/folder location where the dbf files exist and leave off the database container name and extension, to the best of my recollection.

Oh, yeah, just to refresh your memory, the latest OLE DB Provider for VFP is here (backward compatible to earlier VFP versions):

Microsoft OLE DB Provider for Visual FoxPro 9.0
http://www.microsoft.com/downloads/details.aspx?FamilyID=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&DisplayLang=en

To quickly get to a list of OLE DB Providers on any given PC....

    Right-click the desktop.
    Select New>Text Document
    Change the name to LookatOLEDB.UDL (note the extension).
    Tell Windows "HELL YES!" when it warns about changing the file extension.
    Now, DblClick that puppy and check the Providers tab.

You can use this to make an OLE DB connection string that you can just read out of LookatOLEDB.UDL with NotePad, or to browse providers, etc.

The utility that launched when I created said LookAtOLEDB.UDL was the exact same utility that ran when I told ADODC1's connection string to Build.  That is the utility I used to build the string I posted above.  I probably get my terms mixed up and I apologize for that but I'm using the string above with VB and I'm using ODBC in MS Access just to compare and test.  I'm currently trying to do it completely manually.  I'll post my findings.

Okay, I get the exact same resulth.  Here is the code below that DOESN't work:

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConn As String
Set conn = New ADODB.Connection

strConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=c:\Program Files\Data;Exclusive=No"
conn.Open strConn
    Set rsPets = New ADODB.Recordset
   rsPets.CursorType = adOpenKeyset
   rsPets.LockType = adLockOptimistic
   rsPets.Open "select * from WVSSPET where PET_CASE = 5555", strConn, , , adCmdText
    rsPets.Update

Debug.Print "  " & rsPets!PET_CLINUM & " " & _
      rsPets!PET_CASE & "  " & rsPets!PET_NAME
rsPets.Close
conn.Close

It errors with an empty recordset.  However, if I change it to read
rsPets.Open "select * from WVSSPET where str(PET_CASE) like '%5555'", strConn, , , adCmdText
It pulls back the record with 5555 as it's Case Number.

Of course it isn't working.  You keep giving it the ODBC string setup.  

strConn = "Driver={Microsoft Visual FoxPro Driver};........   <<<<==== That is ODBC, not for the OLE DB Provider

Use the OLE DB Provider setup and you should have at least a fighting chance at getting something more to work.

oConn.Open "Provider=vfpoledb;" & _
           "Data Source=c:\Program Files\Data;" & _
           "Mode=ReadWrite|Share Deny None;" & _
           "Collating Sequence=MACHINE;" & _
           "Password=''"

Sometimes the registry entry gets a reference to vfpoledb.1.  But, if that can't be remembered or is cryptic to you, you can simply supply the text that the UDL method on the desktop I gave you above lists-- just supply the string, Microsoft OLE DB Provider for Visual FoxPro, which is shown in the OLE DB Provider(s) list under the Providers tab of that Data Link Properties dialog box.

oConn.Open "Provider=Microsoft OLE DB Provider for Visual FoxPro;" & _
           "Data Source=c:\Program Files\Data;" & _
           "Mode=ReadWrite|Share Deny None;" & _
           "Collating Sequence=MACHINE;" & _
           "Password=''"

I should mention, too, that if you don't see the entry, Microsoft OLE DB Provider for Visual FoxPro, in the OLE DB Provider(s) list under the Providers tab of that Data Link Properties dialog box, you have not installed it or have not properly installed it on your PC.

Here's where it is again, in case you find you need to instal lit for it to show up in that Sata Link Properties dialog: <s>

Microsoft OLE DB Provider for Visual FoxPro 9.0
http://www.microsoft.com/downloads/details.aspx?FamilyID=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&DisplayLang=en

When I change the code to read strConn = "Provider=VFPOLEDB.1;Data Source=c:\Program Files\AIS\AisData;Mode=ReadWrite|Share Deny None;Collating Sequence=MACHINE;Password=''" I get the identical problematic behavior.

When you query a VFP table, you must use the VFP syntax for the VFP driver to return the correct data.  Since that ID field seems to be text (you never did tell us what that field type really is), you will need to try to convert it to a numeric field to use in your numeruc evaluation.  In VFP, the VAL() function will convert a Character field to a numeric one.  Of course, if there's alpha characters in that field as well, it will return something numeric, but not necessarily what you want.  There are sometimes when a developer makes a field Character type because the developer says to himself he's never going to use that field for a calculation.  Of course, I bet there probably is a calculation goi9ng on to get the next unused value. <s>  Regardless, try the VAL() function which has been around a long time in the xBASE language.  [The CAST() function has been introduced to VFP recently to get it more compatible with SQL Server.]

Here is the meaning and use of those two functions:

VAL( ) Function
http://msdn.microsoft.com/library/en-us/dv_foxhelp9/html/3ad0c474-0a70-445e-8d49-a1ecfa2d95db.asp?frame=true

CAST( ) Function
http://msdn.microsoft.com/library/en-us/dv_foxhelp9/html/55c599e9-18d1-4ac6-b311-e6677dc3b0b7.asp?frame=true

The ID Field appears to be Numeric.  From a previous posting above:

"Not having a copy of FoxPro, all I can do is tell you what Access thinks the field is.  The particular field in question shows as a Long Integer "

but you're right, there is more there than meets the eye.  Obviously FoxPro's numeric variable is being misidentified by any of the microsoft products in question.  I'm sure the VAL() function will fix the problem.  I don't know why I didn't think of that long ago instead of worrying about converting it to a string etc.  It's been around in Visual Basic for ever as well.  I just forget to apply some of those things to SQL sometimes.  I'll verify that when I get home tonight and It looks like I may be on my way

============ DUPE FROM VB Database ===============
Carl,
Now we're down to the nitty gritty in the incompatibility realm.  
"Select * from wvsspet where val(PET_CASE) = 5555" returns "ERROR: Function argument value, type, or count is invalid."
This is the first time I've seen this error and I guarantee the VAL() function work in VB6.  However,
"select * from WVSSPET where CAST(PET_CASE AS INT) = 5555" works like a champ!  I have no idea what gives and I've done a bunch of this kind of stuff for both Oracle and Access files and have never had these problems.  

Nope, we're not out of the fire yet.  With the second table I need access to, I've created this query:
rsPets.Open "select * from WVSSCLI where CAST(PET_CLINUM AS INT) = " & Val(Text4) and it returns
"ERROR:  variable CLI_BALTYPE is not found".  Now I can ASSURE you that CLI_BALTYPE is not a variable anywhere in my code.  There is a field, however, in that table called CLI_BALTYP.  I see this when I open it in Access, VB or CDBF Viewer.  I really need to find a non Microsoft driver.  The folks who wrote CDBF Don't use ADO or ODBC but have written their own API DLL file to handle these files and it seems to work beautifully but I"m not bright enough to figure out how to get it into Visual Basic to use it although it is written as a developer tool.  The author doens't know VB and can't assist unfortunately.  I'm beginning to think I need an exorcist for this machine!!

If you have a bad index file associated with that table, obviously fixing it will help things a lot.

I know you now have VFP6 handy.

The quick and dirty method to repair an index file is to open up the table in VFP6 and then issue a REINDEX command.  I say quick and dirty, because if there's any corruption in the header portion of the index file that the REINDEX command would use to re-create the index keys, it still may come out corrupt.  However, if you only have a problem with outdated index keys, the REINDEX command will most likely be sufficient.

I don't know where or not your index file is a .CDX file extension (I hope it is) or the older .IDX file extension.  The .CDX file extension type is preferred for various reasons, one of which is that it is smaller/more compact than the older .IDX files and secondly, if it has the same file root name as the .dbf, it is automatically opened up with the table when the .dbf file is opened.  This assures that any changes to the table also get index keys updated without having to remember to explicitly open the index file, which is what we used to have to do with that older .IDX file extension.

Anyhow, if the index file header is corrupt, you would have to re-create the index key(s) from scratch with the INDEX ON command which requires you to know what all of the keys you had indexed are.

Just more info for you...

Damn, you're good ;-)
Here's today's saga.  It looks like we are working with either a newer version or a bastard version of those foxpro tables.  I picked that one table and "reindexed" it the "quick and dirty" way and all of the sudden Visual Basic & ADO just loved it.  The problem is the original application crashed upon opening it.  So, I found the data repair utility they shipped with the product and "Packed" the table and the applicaiton once again ran like a champ but I'm back to the same errors in the database when accessing it.  Is it possible that the DBF is compatible with FoxPro 6 but the indexes are not if it were from FoxPro 7 or 8?  

So I just used my wife's status as a student at Portland Community College to get an academic version of FoxPro 9.  Maybe this will answer some questions.  It's coming from Beaverton which is ony about 20 miles away so hopefully I'll have it in a day or three.  Until I can get some answers about the origin of this data, I'm kind of stuck wouldn't you say?

I used to live in Beaverton many years ago up there in Intel and Nike country. <lol>

I wonder now if they have some unusual index keys going on.  Sometimes, a developer can go crazy and have index keys on concatenated expressions and worse, have an index based on a UDF (User Defined Function) that would only be available when used within the VFP program environment.  I can't say that is what is happening.  I'm just offering worst scenario possibilities.  If you try to use a table and index file outside of the native environment where unusual requirements are the order of the day, that could trip up any reasonable outside querying.

One more (and what could potentially be the key) thing to think about.  According to the companies web page, I finally dug up that their product is based on an "Advantage Database"  And I quote from Delphizine..

Advantage Database Server (Advantage) got its start as a database server using the dBASE table format. Although it still supports dBASE tables with both Clipper and FoxPro indexes, Advantage has moved on to include its own proprietary table format that provides many features not available with dBASE tables. Advantage proprietary tables are stored in files with the .ADT extension; this review describes the features of the ADT table format.

End Quote.  

I had originally missed the reference to the newer versions of VFP.  If they are using newer data types, re-indexing in VFP6 will definitely change the nature of the index file which could easily cause it to crap out when you try to use those files in the original application.

Here's a quick trick to determine what version of VFP an .exe was compiled in so you have a better idea of what you're working with.  Open up the .exe file in MS WordPad and search for the string "VisualFoxProRuntime".  The numeric designation following that string, assuming it is found, is the version of VFP it was compiled in.  I just looked at one I've compiled here and it returned a ".9" after the "VisualFoxProRuntime" string.  And, the good news is, I did compile this exe under VFP9.  FWIW...

Yes, I've heard of Advantage.  But, you never said you had files ending in .adt.  I'm not so sure their proprietary format is in effect here if the tables don't end in .adt.

There is one .adt file but it's called PrintTest.adt so I gave it no nevermind.  I'm searching like mad to find a free or demo OLE DB driver to test it out.  Not having much luck yet.

Once you loaded VFP9, open that table with its associated index file in place.

If you simply type DISPLAY STATUS at the Command Window, you will quickly and easily see the info on the open table including all of the index key information that should be available with that setup.

You sir, are a wealth of knowledge and I appreciate it.  According to the vendor, they are ODBC compatable but that same person didn't know what database they were using.  There are definage ODBC tools on Advantages web site but I can't find a client only install and don't want to poney up the cash for a SQL server when this is a charity project.  I've listed another posting into the Visual Basic Database section asking if anyone knows where I can get it.

I'd try to use VFP9 and open up the table and look to see what the index keys are.

I wish I had your files here.

I'm glad you made some real progress.

Yeah, once over that hurdle, I almost completely wrapped up the first half of the project last night.  But now I have to tackle the reformatting of pasted text from a web page to a text box.  Why do I do this to myself?  ;-)  I can't thank you enough for all your insight and advice.  I feel lucky to have made you acquaintance.