<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>Message</TITLE>
<META content="MSHTML 6.00.2722.900" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><SPAN class=392372716-05032003><FONT face=Arial color=#0000ff size=2>Oh, I
hear ya. I would take a computer that catches fire over a 'powers that be'
problem ANY DAY! <VBG></FONT></SPAN></DIV>
<DIV><SPAN class=392372716-05032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=392372716-05032003><FONT face=Arial color=#0000ff size=2>What I
would recommend then..and again, I'm still a little fuzzy on the specifics, but
I would say take the smaller of the two (which I am assuming would be the
imported file), and dump it into a Collection of custom Classes. Base the
ID for each class you put into the collection on the 'key' that you are going to
be looking for in the SQL records. Then open the SQL records as a
recordset, and as you run through them, take the ID, and do a check for a
matching class in your collection. If there is a match, do the update, if
there isn't move on.</FONT></SPAN></DIV>
<DIV><SPAN class=392372716-05032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=392372716-05032003><FONT face=Arial color=#0000ff size=2>That
way you are running in memory with one of the lists, and you aren't opening and
closing a recordset to find a match.</FONT></SPAN></DIV>
<DIV><SPAN class=392372716-05032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=392372716-05032003><FONT face=Arial color=#0000ff size=2>Just a
thought.</FONT></SPAN></DIV>
<DIV><SPAN class=392372716-05032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=392372716-05032003><FONT face=Arial color=#0000ff
size=2>Drew</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Swisher, Timothy B.
[mailto:tswisher@gfnet.com]<BR><B>Sent:</B> Wednesday, March 05, 2003 10:12
AM<BR><B>To:</B> 'accessd@databaseadvisors.com'<BR><B>Subject:</B> RE:
[AccessD] Editing Records<BR><BR></FONT></DIV>
<DIV><SPAN class=917271116-05032003><FONT face=Arial size=2>The powers that be
do not want links. I need to use code.</FONT></SPAN></DIV>
<DIV><SPAN class=917271116-05032003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=917271116-05032003><FONT face=Arial
size=2>Tim</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B> Drew Wutka
[mailto:DWUTKA@marlow.com] <BR><B>Sent:</B> Wednesday, March 05, 2003 10:51
AM<BR><B>To:</B> 'accessd@databaseadvisors.com'<BR><B>Subject:</B> RE:
[AccessD] Editing Records<BR><BR></FONT></DIV>
<DIV><SPAN class=444195015-05032003><FONT face=Arial color=#0000ff
size=2>What is preventing you from making a link to the table(s) in
SQL? If you have a linked table, you can make a query that pulls both
recordsets up, which would allow for the dynamic criteria and the dynamic
update data.</FONT></SPAN></DIV>
<DIV><SPAN class=444195015-05032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=444195015-05032003><FONT face=Arial color=#0000ff
size=2>Drew</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Swisher, Timothy B.
[mailto:tswisher@gfnet.com]<BR><B>Sent:</B> Wednesday, March 05, 2003 9:32
AM<BR><B>To:</B> 'accessd@databaseadvisors.com'<BR><B>Subject:</B> RE:
[AccessD] Editing Records<BR><BR></FONT></DIV>
<DIV><SPAN class=335401515-05032003><FONT face=Arial size=2>I think I need
to use a recordset because I do not have the validation data available to
the app in any other form.</FONT></SPAN></DIV>
<DIV><SPAN class=335401515-05032003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=335401515-05032003><FONT face=Arial size=2>More
background...</FONT></SPAN></DIV>
<DIV><SPAN class=335401515-05032003><FONT face=Arial size=2>We get a file
from one of our apps, I import that data into an Access db, that data
needs to be validated against our SQL Server. The data from the SQL
Server is unbound, no table links. I basically send the server a
project number, if it returns a record, its valid, if not its an
error. We are checking for errors on project numbers, whether its
active etc. We also use the project number and other data to
find information about the project and add that to the record, who it
belongs, client, etc. There is different criteria for each
validation. For each record, I could have anywhere from no
changes to 13 changes. </FONT></SPAN></DIV>
<DIV><SPAN class=335401515-05032003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=335401515-05032003><FONT face=Arial size=2>Thanks
again.</FONT></SPAN></DIV>
<DIV><SPAN class=335401515-05032003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=335401515-05032003><FONT face=Arial
size=2>Tim</FONT></SPAN></DIV>
<DIV><SPAN class=335401515-05032003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV></DIV>
<DIV><FONT face=Tahoma size=2>-----Original Message-----<BR><B>From:</B>
Susan Harkins [mailto:harkins@iglou.com] <BR><B>Sent:</B> Wednesday, March
05, 2003 10:03 AM<BR><B>To:</B>
accessd@databaseadvisors.com<BR><B>Subject:</B> Re: [AccessD] Editing
Records<BR><BR></DIV></FONT>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV><FONT face=Arial size=2>I didn't thoroughly check, but is there a
reason you need to use a recordset to do this -- it looks to me like an
Update query might be faster. </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Susan H. </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=tswisher@gfnet.com href="mailto:tswisher@gfnet.com">Swisher,
Timothy B.</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=accessd@databaseadvisors.com
href="mailto:accessd@databaseadvisors.com">accessd@databaseadvisors.com</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Wednesday, March 05, 2003
8:06 AM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> [AccessD] Editing
Records</DIV>
<DIV><BR></DIV>
<P><FONT face=Arial size=2>Hello group, I have a procedure (A2k) that
opens a recordset (DAO) and cycles through the data
validating/updating several fields for each record. My question
is, what is the best way to do this, </FONT></P>
<P><FONT face=Arial size=2>Like this</FONT> </P>
<P><FONT face=Arial size=2>.edit</FONT> <BR><FONT face=Arial size=2>If
validation1 = false then</FONT>
<BR> <FONT face=Arial
size=2>change data</FONT> <BR><FONT face=Arial size=2>End if</FONT>
</P>
<P><FONT face=Arial size=2>If validation2 = false then
</FONT><BR> <FONT face=Arial
size=2>change data</FONT> <BR><FONT face=Arial size=2>End if</FONT>
<BR><FONT face=Arial size=2>.update</FONT> </P>
<P><FONT face=Arial size=2>Or </FONT></P>
<P><FONT face=Arial size=2>If validation1 = false then</FONT>
<BR> <FONT face=Arial
size=2>.edit</FONT> <BR>
<FONT face=Arial size=2>change data</FONT>
<BR> <FONT face=Arial
size=2>.update</FONT> <BR><FONT face=Arial size=2>End if</FONT> </P>
<P><FONT face=Arial size=2>If validation2 = false then
</FONT><BR> <FONT face=Arial
size=2>.edit</FONT> <BR>
<FONT face=Arial size=2>change data</FONT>
<BR> <FONT face=Arial
size=2>.update</FONT> <BR><FONT face=Arial size=2>End if</FONT> </P>
<P><FONT face=Arial size=2>I have about 13 validations that need to be
done on about 90,000 records. Each validation goes to a SQL
Server BE and check the validity of the data. There is a huge
difference in speed opening and closing the recordset so many times,
but if that is the better way to go, then so be it. All help is
appreciated. TIA.</FONT></P>
<P><FONT face=Arial size=2>Tim</FONT>
</P></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>