Prev: function for date of first and last day through weeknumber
Next: Find Replace values when they are not null
From: Wendell on 6 Apr 2010 15:51
I currently have C++ code that processes a raw data file containing multiple
types of records. The code creates records in multiple tables in an Access
database depending on the record type. The records are created one at a time
as the data is read. I don't think this is very efficient. But I can't
change this part of the processing. There are hunderes of thousands of
records in raw data and the tables.
I now need to update fields in created records from data contained in other
tables in the database. The options I've come up with are:
1. In the C++ code, read the data from the table at the time the new record
is created.
2. Run an update query after all of the new records have been created to
update the fields in the new table. There is a recordID in both the old and
new tables that allows me to easily join the tables together.
3. Run some VBA code after the new records have been created to update the
new fields in the new table.
What method has the best performance for accomplishing this? I would guess
the update query. But I haven't found a good resource for understanding this.

Thanks for your help!
Wendell
From: PieterLinden via AccessMonster.com on 6 Apr 2010 16:37
Wendell,

Think of it this way - the fastest way of dealing with sets of data is to use
set-based operations. In other words, since you have your data in a database,
why not use the database for what it's good for? You can write *one*
statement to update one or more columns in an entire table (or part of it) at
once. What's not to like? The speed difference between doing it in code vs
doing it in SQL becomes more obvious the larger your files become.

If necessary, you could dump your records into a temporary table/database and
the run all the updates on them to clean them up, and then append the cleaned
records to a final table. The QBE grid does most of the work and the
solution is pretty much pushbutton... The only time I ever use code to
process records is when the structures are so variable that there's simply no
other way to do what I want.

If you post some sample data and the transformations you need to do, I'm sure
someone can point you in the right direction. You *should* be able to do the
vast majority or all of it without code.

HTH,
Pieter

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201004/1

From: Wendell on 6 Apr 2010 17:27
Thanks Peiter! Your explanation makes a lot of sense.
 | 
Pages: 1
Prev: function for date of first and last day through weeknumber
Next: Find Replace values when they are not null