Any Suggestions?

[ACE]

Hi, I have 160 txt files that I want to bring into Access to run different models on and do reports with. These files are updated daily using a different program. The only way to tell the difference in them is their filename. The column headings are the same and the data is all of the same type. How can I bring these tables in Access in a way that I can join them to a master sheet that has all of the info for each table? I can't merge all of the tables together outside of Access.

Thanks for the help!!!

 

[FoFa]

Do you need to bring them all in at once? Maybe only one or a few when you run a model. Can you explain more.

 

[ACE]

I need to bring them all in at once. These are hourly values from a series of weather stations. We use them to predict disease outbreaks for the agriculture industry. I would like to keep updating each stations file daily so they're always up to date. I was linking tables at first to the txt files but then I can't edit the tables if there happens to be bogus values for some reason.

 

[FoFa]

OK, how about importing them into a single table with a unique identifier for each file. You could use a table of file names to drive a VBA routine to handle it for you. Just a thought.

 

[ACE]

I think I know what you mean, could you expand on that a little more though?

 

[FoFa]

Say you had a table of file names (might include a UNC/path/etc.) and a unique ID for that file. You could have a VBA routine roll through that table, and bring the data in assigning the unique ID to each row (so you know which file it came from). Now how you do that is up to you. You may have a work table you have VBA do a texttransfer to then run a dynamic query that pulls that data, puts it in a main table with the unique ID. or you could have VBA read the file and insert it into the main table. But once you have a table of the file names, you can automate the process. And having al the data in one table, should help acess to the data, and a unique ID for each file so you can base your model on it. If you add or delete files, you should just have to add or delete from the files table. Just thinking out load, I don't know you app, so it is hard to pin it down.