Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2005

    Unanswered: Linking to Foxpro - edit in datasheet view?

    I'm trying to link and edit some FoxPro tables.
    I achieve the linking fine, but the tables always end up read-only in datasheet view.
    This is my first time working with ODBC files. Any advice to enable editing within Access's datasheet view please? Is it possible ?

    I've worked through many posts here on the same subject but can't find the answer -
    I tried a query but got rebuffed with a query-is-not-updateable message.

    In more detail:
    I've no problem creating tables (DBF and matching FPT files) with FoxPro and editing within FoxPro browse window. I've linked by creating a DNS entry with Free Table Directory, achieving this read-only linking with two drivers:
    Microsoft FoxPro VFP Driver (*.dbf) and
    Microsoft dBase VFP Driver (*.dbf)

    I'm using Visual FoxPro 6.0.8961.00, Access 2002, DAO 3.6, with XP.
    I've downloaded and installed the latest Jet upgrade and Foxpro 6.0 Service Pack 5 from Microsoft.
    My VFPODBC.DLL file is dated 16/6/98

    I pretty sure I've followed the procedure as described in the post (assuming the procedure for Access2002 is the same as Access97) here:

  2. #2
    Join Date
    Feb 2004
    Chicago, IL
    I am only familiar with Free Tables. My understanding, after many hours of tinkering, is that the ODBC driver will not let you update records. All I have been able to do is Append records and Delete records. And the only way I have ever tried is by using SQL (an UPDATE or DELETE query)

    Recently I tried another approach, Ole Db. You may have more flexibility with this but I guess it depends on waht you are doing. To give you a head start here is some code that I never got working (because of data issues) but it should help with the connection string etc.

    Public Sub UpdateFoxProTable(strScreenName As String, _
                                 strNewSettings As String, _
                                 booNewSettings As Boolean)
        Dim cnnFoxPro As New ADODB.Connection
        Dim cmdFoxPro As New ADODB.Command
        Dim strSQL As String
        Dim strPath As String
        strPath = COMPAFolderPath("COMPA Processing Program Support")
        cnnFoxPro.ConnectionString = "Provider=VfpOleDB.1;Data Source=" & strPath & ";"
        cmdFoxPro.CommandType = adCmdText
        cmdFoxPro.ActiveConnection = cnnFoxPro
        If Not booNewSettings Then
            'Start by deleting the old settings
            strSQL = "DELETE FROM azsettings WHERE screen=""" & strScreenName & """"
            cmdFoxPro.CommandText = strSQL
        End If
        'Now add the new settings
        strSQL = "INSERT INTO test (screen, settings) "
        strSQL = strSQL & "VALUES (""" & strScreenName & """,""" & strNewSettings & """)"
        cmdFoxPro.CommandText = strSQL
        Set cnnFoxPro = Nothing
        Set cmdFoxPro = Nothing
    End Sub

  3. #3
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    I link to Foxpro tables in our canned PR/HR package. I've had no problems adding or editing records, but as I recall I did have to specify the key field(s) when linking (it pops a box up). I also use the Free Table method.

  4. #4
    Join Date
    Mar 2005
    Thanks DCKunkle/PBaldy for your help ... some success...
    (Unfortunately I only work in DAO and thus could not try the ADO code)

    The second method worked for me, povided the linked table has an index field as mentioned:
    Delete, Append and Update queries working,
    Edit in datasheet view still not possible.

    So at least I'm able to progress and thus most grateful for the tips.

    If anyone else knows how to get edit in datasheet view, I would still be very interested.

  5. #5
    Join Date
    May 2004
    New York State
    Sorry, scratch my idea.
    Last edited by Sam Landy; 06-08-07 at 14:38.

Posting Permissions

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