I'm working on a project and wasn't sure if this post should go here or in the VBA section. Since its more of a theory question figured I'd start it here.
I need to read data from a 3-rd party Access database and pull the data from 3 separate tables in the remote database into a single table in my database. The remote tables are similar in design but do contain some fields in one table but not the others. In some cases the field in one table matches what is in a different field name in the other tables. Bottom line, I'm reading the data, parsing it and consolidating the fields I need into a single row in my table for each row in each of the 3 tables.
So far, I'm looking at having to parse a combined 1000 rows maximum in the 3 tables at a time. I don't want to link to the data, I do need to copy it to my database. What I'm trying to decide is:
1. Should I create 3 separate append queries where I do the necessary parsing for each table as part of the append? Means that for some fields, I have to duplicate the parsing logic but might be the fastest.
2. Should I write one parse script that looks for each field name in the .Fields collection and if found does the parsing for that field? Means I check for every field in all 3 tables so there are some wasted lookups and extra if statements to see if the field was found. But, I only need to write the parsing for the field once.
Example for #2:
Probably wasting time and horsepower with the NZ's too.
Thanks,
Lution
I need to read data from a 3-rd party Access database and pull the data from 3 separate tables in the remote database into a single table in my database. The remote tables are similar in design but do contain some fields in one table but not the others. In some cases the field in one table matches what is in a different field name in the other tables. Bottom line, I'm reading the data, parsing it and consolidating the fields I need into a single row in my table for each row in each of the 3 tables.
So far, I'm looking at having to parse a combined 1000 rows maximum in the 3 tables at a time. I don't want to link to the data, I do need to copy it to my database. What I'm trying to decide is:
1. Should I create 3 separate append queries where I do the necessary parsing for each table as part of the append? Means that for some fields, I have to duplicate the parsing logic but might be the fastest.
2. Should I write one parse script that looks for each field name in the .Fields collection and if found does the parsing for that field? Means I check for every field in all 3 tables so there are some wasted lookups and extra if statements to see if the field was found. But, I only need to write the parsing for the field once.
Example for #2:
Code:
Set fld = Records.Fields("VERS_DT")
If Len(fld & "") > 0 Then
rst!VERS_DT = Nz(Records!VERS_DT, "")
End IfProbably wasting time and horsepower with the NZ's too.
Thanks,
Lution
