Importing FoxPro tables-tbl name always variable

[bren11]

Hello,

I am trying to work with a directory that contains FoxPro tables, and bring them into Access. Sometimes the directory contains 1 table, sometimes 2 or 3.

I would like to import, rename, then append the tables to a master table. I am quite puzzled over how to go about it, though, since the table names are always different.I know I need a connect string to go out to the directory, but how do I say to import and append all available tables within the directory?

It's got me stumped, any suggestions would be greatly appreciated!

 

[JeremyNYC]

Here's one bit of advice--instead of importing the tables and then appending the records to your own table, just link to the FP table and then append. That will be much quicker and will avoid the massive bloating you would run into with constant importing and deleting.

I don't honestly know whether or not there's a way to walk through the collection of tables in an FP database. I would be that someone here will steer you right, though.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[bren11]

Jeremy,

Thanks for your reply. I took your advice and used linking instead of importing. And I actually did figure out how to step through a directory containing many (FoxPro)tables and link each one, and it works great. However, I am still perplexed on how, within my same loop,that once the link is created, how to append that table to a master table.
Do you know if you can use a wildcard to append all these files once they are linked? All the FoxPro tables begin with the same 3 letters.

I do appreciate your suggestion, I am sure it saved me a maintenance nightmare!
Thanks,bren11

 

[JeremyNYC]

If the tables are always going to have the same layout, just build three append queries and run them once your loop has been run.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[bren11]

Jeremy,

I am a bit confused by the suggestion of 3 queries, but I am also probably not explaining very well.

The FoxPro tbls in the directory are named like: YUD123, YUD133, YUD144...these are dumped into the directory on a daily basis, and the numbers are always incrementing. Then they are copied elsewhere, and the directory is purged.

My loop steps through each table in the directory, links the file, until there are no more tables to pick up. I was wondering, since only the first 3 letters of the file names will ever be the same if I can run an append query from code, within the loop, that appends all files beginning with YUD*. I need all the files linked to append to a master table, then delete the link.

I am unable to build the queries outright because the file names are always different numerically.

Am I making this harder than it is?

Thanks again for your help,
bren11

 

[JeremyNYC]

Ahhh. Not so hard at all. Here's just a dumb append query I created just for the example:
INSERT INTO CultureFest (AccountName)
SELECT tblAccount.AccountName
FROM tblAccount;

In your loop you could just place the name of the table from which you're appending in a string variable and build the sql and then execute it. Assuming you have everything running, and variables declared and all that jazz, try something like this:


for each yadda in whatever
strTableName = yadda.name
strSql = "INSERT INTO CultureFest (AccountName) " _
& " SELECT " & strtablename & ".AccountName " _
& " FROM " & strtablename
db.execute(strSql, dbfailonerrror)
next yadda =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.