Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    May 2002
    Location
    Wytheville,VA, Virginia, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    multiple dbf's to a single Access table (A97)

    I have a "master" *.dbf(FoxPro) with a list of clients and a field that is the name of a seperate *.dbf file that contains details for the "master record". These are all stored in one directory. I would like to consolidate all the "child" dbf's into one table in Access97. I thought of looping through the records in the master and finding the child for that record, linking that dbf in, appending all the records to a table i created with an extra field that is the name of that dbf. Then killing the link and going to the next master. My logic would look like:

    For each client in tblMaster
    MyClient = tblmaster.client

    Link Myclient.dbf in "G:dbf_files"
    I was thinking maybe using the Dir command???

    Insert into tblMyNewTable(clientname,Field1, Field2...) values(myclient,Field1,Field2....)

    Kill the link to the dbf

    next client

    However, I have never linked tables with code before. I also thought about just going ahead and manually linking in all 100 odd tables and stepping through each in a similar fashion to the above. This would just eliminate the linking through code. Unfortunately, I don't know cycle through the tables and find the one named the same as the client value from the master table. Am I barking up the wrong tree, here?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: multiple dbf's to a single Access table (A97)

    You may not have to import or link the child tables at all. The following is air code; I can't test it, because I don't have FoxPro. Don't forget to substitute the appropriate names. I have assumed that the master table has already been imported into the Access database.

    Sub ImportFoxPro()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String

    On Error GoTo ErrHandler

    ' Reference to current database
    Set dbs = CurrentDb
    ' Open master table as record set
    Set rst = dbs.OpenRecordset("tblMaster", dbOpenForwardOnly)
    ' Loop through records
    Do While Not rst.EOF
    ' Construct SQL for append query
    strSQL = "INSERT INTO tblChild (ClientName, Field1, Field2) " & _
    "SELECT '" & rst!ClientName & "', Field1, Field2 FROM " & _
    rst!ClientFile & " IN 'G:dbf_files' 'FoxPro;'"
    ' Execute append query
    dbs.Execute strSQL, dbFailOnError
    ' Move to next record
    rst.MoveNext
    Loop

    ExitHandler:
    ' Cleaning up
    If Not rst Is Nothing Then
    rst.Close
    Set rst = Nothing
    End If
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    ' Go to cleaning up section
    Resume ExitHandler
    End Sub

  3. #3
    Lounger
    Join Date
    May 2002
    Location
    Wytheville,VA, Virginia, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple dbf's to a single Access table (A97)

    Thanks, Hans!
    The code seems to work ok except that it looks for the wrong file. For example, it returns a message saying that it can't find C:My Documents"theClientFile".mdb. The "theClientFile" part is the correct name of the dbf file I want, but it obviously is not an mdb and is not in My Documents. I could very well copy the dbf's to My Docs but they would still not be mdb's, so it would seem to defeat the purpose.
    As sort of a troubleshooting measure, I tried to create an append query, filling in actual values for the variables and leaving in the " IN 'G:dbf_files' 'FoxPro;'" line. I get an error that says "Couldn't find an installable ISAM." I don't know what that means, but it doesn't sound good [img]/forums/images/smilies/wink.gif[/img]. I don't know which version of FoxPro these dbf's are. I have Visual FoxPro 7 installed and it opens them with no trouble. In the Access97 Help file for TableDef.Connect, it only lists up to version 2.6 of FoxPro. Could that be part of my problem? I've never constructed an "Insert into" SQL string from an external source, so I'm lost.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: multiple dbf's to a single Access table (A97)

    When I wrote my reply, I couldn't test with FoxPro files; I'm on another machine now, and it turns out I made a few mistakes (I warned you that it was air code).
    <UL><LI>The file names in tblMaster must NOT include an extension, so instead of Client.dbf, you must have just Client in the table.
    <LI>You must specify a version. Try 'FoxPro 3.0;' instead of 'FoxPro;' in the code. FoxPro 3.0 is the most recent version supported by the Jet Engine on my PC with Access 97; you can also try 'FoxPro 2.6;'.[/list]With these changes, the code works for me. I hope it helps you too.

  5. #5
    Lounger
    Join Date
    May 2002
    Location
    Wytheville,VA, Virginia, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multiple dbf's to a single Access table (A97)

    Hans,
    It works like a charm. The names in the tblMaster did have the file extensions, but I just concatenated the string returned and it was fine.
    That's pretty good to get that close with air code! I just keep learning more and more!

    Thanks Again!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: multiple dbf's to a single Access table (A97)

    I'm glad it worked. This is much more efficient than importing or linking all those FoxPro files.

Posting Permissions

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