Results 1 to 3 of 3
  1. #1
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Joining DBF files - suggestions needed (ADO 2.7 and/or Access 2000)

    <P ID="edit" class=small>(Edited by jscher2000 on 23-Apr-03 23:54. Changed BackgroundFetch to No based on MSKB advice.)</P>Background
    I'm trying to build a little web-based reporting tool. It will read some configuration files that appear to be maintained with a FoxPro runtime (main file = DBF, index = CDX, memo = FPT). However, I only care about the DBF file, so this could just as well be a dBASE question. Because my scenario is web-server based, I prefer DSN-less options.

    Problem
    The one file that contains nearly all of the information I need does NOT contain the name of the workstation; instead, it contains a GUID. By "joining" on the GUID with another DBF file, in another folder, I could get everything I need. An intermediate MDB linking to these files would seem to be the obvious answer. But, as I've learned from two long threads (using Apollo OLEDB as server provider (Access 2K) and FoxPro ODBC Import (Access 2000)), and Microsoft Knowledge Base Article 225861 - ACC2000: No Option to Import, Link, or Export to the FoxPro File Type, because my files do not have an accompany "container" file (DBC), Access wants a User or System DSN. As noted above, I am trying to avoid using a DSN on the server.

    (I do have MSDN, so I could dig out the fabled OLEDB provider for FoxPro, but it, too, seems to want to connect with a DBC file.)

    Partial Solution
    After much trial and error, I found a DSN-less way to connect to one of the databases:

    <pre>Sub BrowseDBFviaVisualFoxProDriver()
    ' Test in VBA hosted in a Word document; set a reference to
    ' Microsoft ActiveX Data Objects 2.7 Library (or whatever)
    Dim adoConn As New ADODB.Connection, adoRS As New ADODB.Recordset
    Dim intRecNo As Integer
    With adoConn
    ' Settings after the first semicolon derived from a FileDSN viewed in Notepad
    .Provider = "MSDASQL;Driver={Microsoft FoxPro VFP Driver (*.dbf)};" & _
    "UID=;Deleted=Yes;Null=Yes;Collate=Machine;Backgro undFetch=No;" & _
    "Exclusive=No;SourceType=DBF;" & _
    "SourceDB=path-to-folder-containing-the-DBF-file"
    .Open
    ' CursorLocation defaults to adUseServer
    End With
    With adoRS
    .ActiveConnection = adoConn
    ' File name is myDatabase.dbf
    .Open "SELECT * FROM myDatabase"
    ' CacheSize defaults to 1
    ' CursorType defaults to adOpenForwardOnly
    ' LockType defaults to adLockReadOnly
    End With
    ' Stop ' Uncomment to inspect connection or recordset properties
    Do Until adoRS.EOF
    intRecNo = intRecNo + 1
    ActiveDocument.Content.InsertAfter "Record " & intRecNo & ": " & _
    adoRS.Fields("first-field-name-from-first-row").Value & vbCrLf
    adoRS.MoveNext
    Loop
    adoConn.Close
    Set adoRS = Nothing
    Set adoConn = Nothing
    End Sub</pre>

    Missing Link
    This is great, and I can open recordsets on both files, but here I'm out of my depth. How do I do a "join" between these two recordsets? Or do I relate the data in some other manner? Note that there should be a one-to-one relationship between the tables, but I haven't actually checked whether it's a perfect match.

    Apologies for the length, and thanks for reading.

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Joining DBF files - suggestions needed (ADO 2.7 and/or Access 2000)

    There are no DBC files. This is not my application, I'm just trying to create a new report against some tables maintained by a third party app.

    I was able to create a page this way: two static recordsets, #1 sorted by the workstation name, #2 sorted by GUID. I loop through #1 and use the Find method on #2 to locate the matching record by GUID. Good results on a test server, not deployable to the production server. The latter seems to lack some drivers.

    Very late here... must sleep.

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

    Re: Joining DBF files - suggestions needed (ADO 2.7 and/or Access 2000)

    >>because my files do not have an accompany "container" file (DBC)<<
    Why don't you have the accompanying DBC files? If you had these there would be no problems linking to the FoxPro files unless they are the greater than Version 2.5

    Pat

Posting Permissions

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