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

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

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 . 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.

Reply | Quote

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).

• The file names in tblMaster must NOT include an extension, so instead of Client.dbf, you must have just Client in the table.
• 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.

Reply | Quote

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!

Reply | Quote

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

Reply | Quote