-
I have a situation where I need to compare an external text file (with one field) to an Access table. The program needs to determine two conditions: when a value exists on the text file and not in the Access table and visa versa. The values that meet either of these two conditions must be placed in two separate reports.
My question is how can this be done? My first thought was to dynamically create a temp. database and import the text file into it every time the program is run (deleting it on termination). That way I could use SQL statements to easily do the compares. But since this app will be used by many unique users at different facilities, I don't think this is the way to go. The Access DB is a constant while the text files will be unique to each location.
Any and all helpful advice or coding examples would be greatly appreciated as I am pretty stumped at this point. My VB skills are novice level. Thank you all in advance!
-
Suggestion:
On program startup, load an ADO recordset (or DAO would work, for that matter) from your MS Access database, and you can also do the same with the text file (or load the text file data into an array)
You can then use the recordset.find/filter method to find data in the recordset and compare the data that way, or loop thru the array of text file data to do the comparisons.
Because the MS Access data is static, you will want to get the data one time into an ADO recordset (plus your query should be pretty simple, with only one field being queried), instead of issuing SQL statements against the MS Access database (which would require a network round-trip every query)
HTH
Tom
-
Thanks, Tom. I'll play around with that to see if I can get it to work...thanks for showing me the light :)
-
