multiple dbf's to single table (Access97)

[cviars]

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 and I can't get the code from thread 705-488486 to work. I also thought about just going ahead and manually linking in all 200 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 how to do that either. Am I barking up the wrong tree, here?

 

[Rolliee]

Do this with VB.

If you need help, send me an email.

rollie@bwsys.net

 

[UrbaneRove]

cviars (MIS)

First of all how much VBA coding have you done?

What needs to happen is the Master table needs to be linked to the Acc97 DB. Either Manually or through code.

see docmd.transferdatabase which will work with all odbc compatible db's.

Then open the table using

db = currentdb
Set rst = db.OpenRecordset( _
"SELECT * FROM LinkFProMaster", dbOpenDynaset, dbReadOnly)


Then loop thru the records in the rst and for each record you find then run the link command for another FoxPro Database.

Hope this helps out.

Urbane Rove

inquire@rove.ca