MIcrosoft, MS ACCESS abd NS FoxPRo, MS ACCESS 2002/2003 and FOxPRo 9.0, LInking to FOxPro 9.0 Tables from within MS ACCESS

gracelandDB,

Make sure all the FoxPro Tables have Primary keys.

JeffCoachman

Good Morning. I do not have FOxPro on my PC so I am not sure about the primary key issue. I wil tell you that the other tabels that I can link to prompt me in ACCESS to identify a possible primary key. THis is typical when using ODBC from ACCESS. I have experienced it with linking to MYSQL on numerous occassions. The tables that I am trying to link to are the main ones in the database application (customer data records), so I would assume they have primary keys. THe research I have done implies that there must be some new features in FoxPro since version 6.0 that you can applyt to a tables schema (maybe some field types) that do not allow these tables to be linked via ODBC. As I stated in my question, I can use OLEDB to import records from the tables in question into EXCEL. THis does not really solve my problem as I am trying to setup a dynamic environment whereby the records can be read from ACCESS. There was a previous post on this site per doing what I want to do with Crystal Reports. Did not seem like the linking of the tables was ever actually resolved.  I have spoken with the vendor that wrote the application and they stated that they also have trouble with this issue as they routinely have to import their new customers data into their FoxPro database.  I will tell you that if I had my way, my customer would not have purchased software using FOxPro. I thought that had gone the way of DBASE. IN any event, thanks for the comment. If you have any other ideas, le me know.

NOte that if you set up a DSN/ODBC connection in WIndows, you can see the tables I ned to link to. However, when you try to link to them with ACCESS, they are not visible.

You are correct in your research in believing that the ODBC may not get to newer features in versions beyond VFP6.  If a developer uses newer features, but especially newer data types and even newer indexing schemes through version VFP9 SP2, the ODBC driver will have no idea what those new features are-- how could it.

The only sure way to get to the newer VFP data and ge tto all of it and not just some of it is to use the OLE DB Provider for VFP.

Microsoft OLE DB Provider for Visual FoxPro (through) 9.0-- dated 5/16/2008
http://www.microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en

I have personally tried to figure out how to use MS Access 2003 to use that OLE DB Provider for VFP within an Access Data Project (.adp).  While I can setup the Data Link Properties, I never see any of the tables once I get the "project" setup knowing I want to link to the VFP9 database.  But, then again, I am not an expert in the area of setting up and using MS Access.  Maybe someone else knows how to use the ADP to grab linked tables that are unseen to me.

Thanks for your repsonse. THis is what I am running into as well. I have a suspicion that the software vendor put some wierd field type in a few of the tables to keep customers "on the payroll" whenever they need to move data into or out of the application. I have also tried the OLEDB thing. Really not sure what that is supposed to solve. Seems like the ODBC was a more straightforward solution. My opinion is IF you are going to make your data inaccessbile to other systems via some technical limitation, you should be clear about it. In any event. Thanks for your response. Hopefully someone has figured this out.

In case I wasn't clear enough, the OLEDB is supposed to solve any issues with getting to newer data types in the newer versions of VFP that the ol ODBC driver couldn't even dream of getting to.  The ODBC driver was issued for VFP6 SP4 and it won't be able to recognize the newer data types that exist in today's VFP9 while the OLE DB Provider for VFP9 will.

Since MS says they don't support the VFP ODBC driver any longer and that we should use the OLE DB Provider for VFP instead, they really should publish a KnowledgeBase article on just how the hell MS Access is supposed to do that.  I can't figure it out in MS Access.  But, again, I'm not a regular user of MS Access to be doing something that most Access users/developers have never even attempted themselves.  :)  What is the secret to getting the OLE DB Provider for VFP to work within MS Access.  It seems Access is setup mostly to work with SQL Server in that area.  It almost makes one believe MS kept it unclear on purpose for every other database.

BTW, for clarification, are the VFP tables you want to grab associated with a VFP database container (.dbc) or are they considered "free" tables?

I just tried a free table setup in an Access Data Project using the OLE DB Provider for VFP9.  Amazingly, after I pointed it to the free table directory/folder where I have some free tables, MS Access presented me with a list of those tables, but asked me to pick a database I wanted to use.  Well, duh!  I just told it previously I wasn't use a database but a free table arrangement.  So, I couldn't pick any of the clearly listed free tables it presented to me because they weren't a database.  That is just plain dumb.  Again, how does MS expect us to use something that doesn't work as expected?

I just tried to address a real VFP9 database container (.dbc) and, while it found it and saw the tables in it, it again wouldn't allow me to select anything for use in the Access Data Project.  What a waste of time and energy.  It just doesn't seem that Access 2003 is setup to use this stuff properly.

I have Access 2007 on another Pc that I'm nowhere near right now.  I'll try that later and I'm fully prepared to hit more dead ends.

CarlWarner

Yes, I understood the OLEDB issue. They are Free tables. You are running into the same issue I am. I have been developing with ACCESS, SQL< and VB since 1993. This one has be baffled.
Thanks

Thanks. I had the same experience as you. Not clear what is going on, but seems sub par.

Thanks for the effort. Let me know if 2007 works any better. I am wondering where I could get a demo or trial version (read free) to look at the problem tables. Maybe see if I can identify the specific field types that are locking out the ODBC connecntion. Maybe I could zip up the few tables and send them to you?
\
Thanks

Yes, zip up any free tables you have that you think I might need to try and look at and send them to one of my e-mail addresses in my profile.

I never saw any tables come through to look at.

I looked at Access 2007-- because I have never ever been in it before in the newest version which uses the ribbon toolbar, I was thoroughly confused as to how to navigate to anything, even the simplest of things.  I ran out of spare time to fool with looking for how it might use an OLE DB Provider for VFP.  If I get some spare time, I will get back there (my home PC) sometime this week.  I still am wondering if even MS's latest Access version has made it even doable.

About OLE DB and Data Links
http://office.microsoft.com/en-us/access/HP052606601033.aspx?pid=CH063667501033

Good Morning: I believe I looked at this aready, but I will try it again. I will get some zipped files to you early next week.

Thanks

I only got to that MS reference through the supposed Help (online) with MS Access 2007.  I have found zero help when it comes to grabbing anything other than MS SQL Server data via an OLE DB Provider within MS Access.  Does MS actually support their own technologies in this area?

Throwing up my hands on how to get it all to workin within MS Access 2003, I posted a question on a MS newsgroup where MS MVPs in Access hang out.  I am hopeful one of them will have an answer, good or bad.  If it in fact doesn't work, I'd like to know that as well so I can tell anyone who asks not to bother wasting their time unless they are trying to get to MS SQL Server.

Two gentleman said you can't do it, unless you do it programmatically via ADO commands.

Here is the third and most recent response:

"You can open an ADODB connection within a MDB or whereever you have access to the ADODB typelibrary. You don't need ADP to use ADODB. If you are using an ADODB.Recordset instead of a DAO.Recordset it should support the delivered datatypes. Just give it a try. Add the ADODB reference and remove the DAO reference and use what you get then."

Since I am not an Access user, I have no idea whether what he is saying has any bearing on the question I asked or even what you are really looking for.

Thanks Carl: Sorry for the delay. I am a COreComm customer and i have had no email for 3-4 days. In any event, I also found the ADO stuff. Not really what I needed but I will take a look and see if I can somehow work this. ONce again, Microsoft does not really support all of the other applications or technology that they buy out. FoxPro is yet another example of this.

Thanks

Jerry Unger