Solved

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

Posted on 2008-06-09
21
506 Views
Last Modified: 2008-09-09
Using MS ACCESS 2002 I am trying to link via ODBC to FOxPro 9.0 Free tables.  I can link and access data directly in most of the tables in the FOxPro application from MS ACCESS. However, a few of the key FOxPro tables I need must be using features incomptabile with ODBC. I installed the FoxPro OLEDB stuff and I can import the desired tabels into EXCEL. However, I really need to link them in ACCESS for reporting and analysis purposes. I want to link them is possbile so that the user does not have to do anything to access the FoxPro data outside of the front end I create in MS ACCESS>
Thanks
0
Comment
Question by:gracelandDB
  • 11
  • 7
21 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21748522
gracelandDB,

Make sure all the FoxPro Tables have Primary keys.

JeffCoachman
0
 

Author Comment

by:gracelandDB
ID: 21750195
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.


0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 21750623
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.
0
 

Author Comment

by:gracelandDB
ID: 21750759
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.
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 21751867
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.
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 21752196
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?
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 21752276
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.
0
 

Author Comment

by:gracelandDB
ID: 21752604
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
0
 

Author Comment

by:gracelandDB
ID: 21752615
Thanks. I had the same experience as you. Not clear what is going on, but seems sub par.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:gracelandDB
ID: 21752631
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
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 21754282
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.
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 21759582
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.
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 21783678
0
 

Author Comment

by:gracelandDB
ID: 21784958
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
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 21785587
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?
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 21785744
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.
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 21798669
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.
0
 

Author Comment

by:gracelandDB
ID: 21830109
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
0
 
LVL 33

Accepted Solution

by:
CarlWarner earned 500 total points
ID: 21841354
I just found more on a third-party ODBC driver that I knew had limitations for VFP9, but I had no clue exactly.  Even though this lists one limitation, I'm not sure that really is the only limitation.  But, I am sure in the big scheme of things, it is a minor limitation that the vast majority of folks can live with.

"If you want to stick with ODBC and you need to support any features that were introduced into VFP after 6.0 SP5, then you may want to consider going to http://devzone.advantagedatabase.com/dz/content.aspx?Key=20&Release=12&Product=14&Platform=6 . This is 3rd party VFP 9.0 ODBC driver (and all previous versions). It only has one known limitation - it doesn't support the 9.0 binary index."

YMMV...
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

747 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

16 Experts available now in Live!

Get 1:1 Help Now