Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    using Apollo OLEDB as server provider (Access 2K)

    Hi,
    I have written a program that uses SQL queries in the code to find ADODB recordsets and using the recordsets to update and insert records into the tables. The problem is that I have to connect to live data that are actually FoxPro tables. I found that I could not link the tables using Access 2000, but could import them. The program works on the imported tables which I used as a test, but now I have to link to the foxpro tables to access the actual live data. I am trying an OLEDB server called Apollo, I can open the connection (I think) but when I go to open a recordset using the connection, the whole Access program is switched off. I don't understand why, and I'm wondering if anyone has any ideas as to which OLEdb server provider to use or whether I am using the correct terminology to make the program run properly. Below is the code that establishes the connection, it dies when it gets to .open SQLCentre (SQLCentre is a SQL select statement). Any ideas?
    strConnect = "Vista Software OLE DB provider for SDE;"

    '------------------------------------------------------------------
    'open connection, set rs to SQL statement
    Set currDB = CurrentDb
    Set curcon = New ADODB.Connection

    With curcon
    .Provider = strConnect
    .ConnectionString = "data source= " & "C:Aurorastandard*.dbf" & _
    "Mode=ReadWrite;SDE OLEDB: RDE Type=FoxPro"
    .Open
    End With
    Set rsCentre = New ADODB.Recordset

    With rsCentre
    .CursorType = adOpenForwardOnly
    .LockType = adLockPessimistic
    .Open SQLcentre, curcon, , , adCmdText
    End With

    Thanks for any help, I'm really stuck.
    Sue.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: using Apollo OLEDB as server provider (Access 2K)

    I've not worked with FoxPro tables in a very long time, but my understanding is that you can link the them just as you do Access tables in a back-end, or an Excel Workbook, or SQL tables, so I don't see why you shouldn't be able to. Is there any reason why you don't want to have permanent connections to the FoxPro tables?
    Wendell

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: using Apollo OLEDB as server provider (Access 2K)

    Hi Wendell,
    What you say is correct about linking FoxPro tables, but in Access 97, it's not available in Access 2000.
    What you have to do in Access 2000 is to Connect then append the tabledef object.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: using Apollo OLEDB as server provider (Access 2K)

    Must confess I haven't done it, but according to MS Knowledge Base article Q225861 you should be able to do an ODBC link with no problems. Of course that isn't the same as an ADO connection, but we find that ODBC is typically more reliable and easier to use than ADO with Access2K - it does get to be about a toss-up with 2002.
    Wendell

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Linking FoxPro Tables (Access 2K)

    When working with Visual FoxPro 6.0 tables in Access 2K (which I do on regular basis) I use ODBC connection to link or import data (in A97 you were able to use installed ISAM drivers for FoxPro tables, but support for same was discontinued in A2K). Since I work with these tables on regular basis I usually just link "manually" after creating DSN source in Control Panel ODBC Administrator. You can use TransferDatabase method to link in code. Example:

    Sub LinkFoxProTableODBC()

    Dim strDbName As String
    strDbName = "ODBC;DSN=Visual FoxPro DB;SourceDB=C:FOXPRODB1.DBC;" & _
    "SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;C ollate=Machine;" & _
    "Null=Yes;Deleted=Yes;;TABLE=TBL1"
    DoCmd.TransferDatabase acLink, "ODBC", strDbName, acTable, "TBL1", "TBL1", False

    End Sub

    For Database Name (3rd argument) use complete ODBC connection string (strDbName in example). Easiest way to construct this is to link table "manually", open in design mode, then copy & paste full string from table's Description property. One drawback with linked FoxPro tables is that you cannot turn off the annoying SubDataSheet property from Auto to None in design mode, this can only be done in code. This property causes the linked tables to load slower, affecting performance.

    I haven't tried linking FoxPro tables using an ADO connection so cannot offer any advice in that area. The ODBC method normally works reliably.

    HTH

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Linking FoxPro Tables (Access 2K)

    PS - I neglected to mention, when linking Visual FoxPro tables, when the "Select Unique Record Identifier" dialog pops up you need to select the field or fields in that table that will act as an unique index for that table, or you will not be able to update records or insert new records. The dialog should pop up for tables that do not have a single-field primary index already defined, whether linking manually or by using code as previously described.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: using Apollo OLEDB as server provider (Access 2K)

    I must confess I was going on memory there. I knew I could not link normally and had tried a convoluted method I described earlier.
    Mark's suggestion looks the goods, in fact I have a database where I have to link to FoxPro tables using Access 2000.
    Pat

  8. #8
    New Lounger
    Join Date
    Jul 2002
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using Apollo OLEDB as server provider (Access 2K)

    Thanks for all your suggestions people. I tried linking the tables using the ODBC driver but ended up with such a slow connection that the computer would hang for minutes at a time. I assumed it was a problem with the driver and tried using the OLEDB driver instead, connecting through code. My biggest problem with using the ODBC connection code (and the OLE as well) is that the tables I'm working with don't appear to be linked through a database since they are connected through an executable piece of software (an accounting package written in FoxPro and distributed as an executable). I can't seem to track down a .dbc (database) extension that I can use, just all the table files.
    (I'm still new to the firm I work for and am still trying to find my way around all the intricacies of the accounting software.)
    However, you've given me some ideas to start trying. Thanks.
    Sue.

  9. #9
    New Lounger
    Join Date
    Jul 2002
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking FoxPro Tables (Access 2K)

    Hi
    Using ODBC connection does not appear to support ADO (or the other way around!) - all I get is an ODBC call fail when I get to the update part of my code.
    However, I hope you - or anyone - can furthur help here - using an OLEDB server - Apollo DB5.1 - causes Access2000 to shut down when I get to the ".open SQLcentre," part of the code (see the code in my previous e-message). Does anyone have any ideas WHY that should happen?
    Sue.

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linking FoxPro Tables (Access 2K)

    What do you mean about Apollo being an "OLEDB server"? Why not use the OLEDB provider for FoxPro?
    Charlotte

  11. #11
    New Lounger
    Join Date
    Jul 2002
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking FoxPro Tables (Access 2K)

    Is there one? How do I get to it? I can only find an ODBC driver for foxpro when I try linking the tables as a permanent connection. (As you may be aware by now I am ver y new to all this business with having to link tables using providers other than jet or SQL7, and I am floundering somewhat)
    Thanks, Sue.

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linking FoxPro Tables (Access 2K)

    I don't know if there is a specific OLE DB driver for FoxPro, but you can always use the OLEDB Provider for ODBC Drivers, which is slower than a native OLE DB driver would be but will work.
    Charlotte

  13. #13
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: FoxPro OLEDB Provider (Access 2K)

    As far as I can tell from MS's less than totally enlightening published info on this topic, it seems the only way you can obtain the Visual FoxPro OLEDB provider (VFPOLEDB.DLL) is by purchasing VFP 7.0. Otherwise you are stuck with the Visual FoxPro ODBC provider (VFPODBC.DLL). For example, these MSKB articles, while may not be directly related to current situation, provide following info:

    HOWTO: Execute a Stored Procedure in a VFP Database with the VFP OLE DB Provider
    <hr>VFP 7.0 ships with an OLE DB provider that can be used to directly execute a stored procedure in a VFP database. This article describes how to do this.

    NOTE: Purchasing VFP 7.0 is the only way to gain access to this OLE DB Provider; it is not posted on the Microsoft Web site, nor does it ship with any version of Microsoft Data Access Components (MDAC). <hr>
    (Emphasis added.)

    PRB: ASP/COM+ Solutions Using VFP ODBC Driver May Fail Under Stress

    This article states in part:
    <hr>SYMPTOMS
    When designing a solution that uses the Visual FoxPro ODBC (VFPODBC) driver, developers may find that their COM objects fail (hang or crash) when the solution is under heavy load. This occurs regardless of the version of the VFPODBC driver.
    CAUSE
    COM objects that are developed with Microsoft Visual Basic, Microsoft Visual C++, or other development languages are widely used as middle-tier business objects in large-scale, multiuser solutions. These COM objects are often resident in MTS/COM+ packages, and use the VFPODBC driver to query and update data from Microsoft Visual FoxPro and Microsoft FoxPro 2.x tables.

    The problem with this design is that the Visual FoxPro ODBC driver is not thread-safe, and as a result, MTS/COM+ (or the objects themselves if they are stand-alone) can fail.

    NOTE: The Drvvfp.hlp and Vfpodbc.txt files that are installed with the driver both state that the VFPODBC driver is thread-safe; this is incorrect. <hr>
    And further, under "Resolution":
    <hr>Use the Visual FoxPro OLEDB Provider. The VFP OLEDB Provider is thread-safe and may be used without causing failures. Currently, the VFP OLEDB Provider ships with Visual FoxPro 7 and later. It is not available for download.<hr>
    The current version of the VFP 6.X ODBC driver can be downloaded from:

    <A target="_blank" HREF="http://msdn.microsoft.com/vfoxpro/downloads/odbc.asp">Microsoft Visual FoxPro ODBC Driver (6.1.8629.1)
    </A>

    The FoxPro application I work with is written in VFP 6.0. It installs the VFP ODBC driver but there's no VFP OLEDB driver to be found. I've never heard of the "Apollo" provider. As for VFP 7.0, last time I checked it sells for over $500, unless you qualify for an upgrade, which is a bargain at appx $250 (joke). It's no longer included in the Visual Studio package, but is included in MSDN subscriptions.

    HTH

  14. #14
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: FoxPro OLEDB Provider (Access 2K)

    One additional article:

    INFO: Visual FoxPro ODBC Driver Not Included in MDAC 2.6 and Later

    This article states in part:
    <hr>The FoxPro ODBC driver is in maintenance mode only. No further updates to this product are planned, unless they are designed to specifically address a problem identified to have a wide impact on its users.

    The OLE DB provider for FoxPro is the recommended replacement for the FoxPro ODBC driver. The provider ships with Microsoft Visual FoxPro 7. It can be distributed as part of your solutions, similar to the VFP runtime files. It will not be available for download.<hr>
    Again, emphasis added. So it looks MS is saying the ODBC driver is obsolete, but you cannot download the "recommended replacement" - you have to buy VFP 7.0 to obtain the OLEDB provider....

  15. #15
    New Lounger
    Join Date
    Jul 2002
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use VFPOLE.1 driver to access foxpr(Access 2K)

    Hi people,

    thanks for your help - I've just logged in here again after a while and just found these extra topics.

    Sue

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •