Link the table with text files.

Hello All,
I have a table in Access db with some Customer Information like Customer No, Name, Address, etc.
On the other hand, I have thousands of text files which contain some Customer information that I need to use.
The problem is the text files are named randomly so the information for one customer can be in any of the text file. The key words I can use are Customer No and Name which can be found anywhere in the text files. So what I have to do is take my first customer from the table and based on its No search through all the text files to find the match. Once it is found I need to get the name of this text file and input it in my table.

I am not sure if this is possible at all and how long would it take ( my table might have about 50,000 records and the number of my text files might be much bigger).

Please help me with any suggestions.

Kaska :whoops:

well if you need to seach a text file for a specific string, you need to read in the entire text file and go until EOF.

I see this being quite ugly, but I am sure you could do it...but depending on the size and number of text files you might eat up a lot of memory while doing this because you are ALSO comparing your database records at the same time. If you are looking for a match you could probably open your recordset and movefirst...now you are on the first record. Once there, open your text file and read in each line, and do a string comparison (instr() the field value and the text file value) if they match insert the string name of the text file into your database field, if not, keep looping the text file until EOF, But then, you'd have to open the next text file, and repeat the procedure. Once you have finished all text files, then you move to the next database record and repeat the process over again.

This is one potential method that could work, but it wouldn't be quick and your computer would be crying from the amount of memory it would take. And since you need to open EVERY text file and compare all the potential text values with each record in a 50,000 count database table, I am not sure of a quicker or easier method.

Thanks! I do see it ugly too!