<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1226" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=200040718-03092003><FONT face=Arial color=#0000ff
size=2>Lonnie,</FONT></SPAN></DIV>
<DIV><SPAN class=200040718-03092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=200040718-03092003><FONT face=Arial color=#0000ff size=2>
Seek is the fastest way to go. Outside of that, it sounds like you should
be able to do this just with bulk SQL Inserts/updates, which would be even
faster.</FONT></SPAN></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2>Jim Dettman<BR>President,<BR>Online Computer Services of WNY,
Inc.<BR>(315) 699-3443<BR>jimdettman@earthlink.net </FONT></DIV>
<BLOCKQUOTE>
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com]<B>On Behalf Of </B>Lonnie
Johnson<BR><B>Sent:</B> Wednesday, September 03, 2003 2:02 PM<BR><B>To:</B>
'MS-ACCESS-L@lists.missouri.edu'; AccessDevelopers; ms_access; AccessD
solving'<BR><B>Subject:</B> [AccessD] Speed up DAO
processing<BR><BR></FONT></DIV>
<DIV>I have four tables.</DIV>
<DIV> </DIV>
<DIV>Table1 is where all the records end up. It starts out
empty.</DIV>
<DIV> </DIV>
<DIV>1. I take all transactions from Table2 and append them into Table1.</DIV>
<DIV> </DIV>
<DIV>2. I then check each record in Table3 and see if there is a corresponding
record in Table1 based on two fields. If so, then I edit the existing record
in Table1. If not, I add it.</DIV>
<DIV> </DIV>
<DIV>3. I the do the same as in step 2 for Table4.</DIV>
<DIV> </DIV>
<DIV>I am using DAO recordset processing and use the RS.FindFirst method to
see if records from Table3 or 4 are in Table1. </DIV>
<DIV> </DIV>
<DIV>It appears that this FindFirst method is greatly slowing my process. Is
there a faster way. I pasted my code below if anyone wants to see it.</DIV>
<DIV> </DIV>
<DIV>Option Compare Database</DIV>
<DIV> Dim dbCurr As DAO.Database<BR>
<BR> Dim rsD As
DAO.Recordset<BR> Dim rsF As
DAO.Recordset<BR>
Dim rsP As
DAO.Recordset<BR>
Dim rsR As
DAO.Recordset<BR>
<BR> Dim fldName As String<BR>
<BR> Public Sub
TestRun()<BR>
<BR> Call
uTData("t")<BR>
<BR> End Sub</DIV>
<DIV>Public Sub uTData(objType As
String)<BR>
<BR> Dim passVar As
String<BR>
<BR> Set dbCurr =
CurrentDb<BR>
<BR> 'Clear the old
values<BR> DoCmd.SetWarnings
False<BR>
DoCmd.RunSQL "DELETE * FROM
t_DATA"<BR>
DoCmd.SetWarnings
True<BR>
<BR> If objType = "t"
Then<BR> passVar =
"T_Data_Files"<BR>
Else<BR> passVar =
"Q_Data_Files"<BR> End If</DIV>
<DIV>Call DataFiles(passVar, objType)</DIV>
<DIV>End Sub</DIV>
<DIV>Private Sub DataFiles(objName As String, objType As String)</DIV>
<DIV>'******************************************T_Data_Files************************************<BR>'******************************************************************************************<BR>
Dim passVar As String<BR>
<BR> Set rsD =
dbCurr.OpenRecordset("SELECT * FROM T_Data WHERE REGION = 'CENTRAL'",
dbOpenDynaset)<BR> Set rsF =
dbCurr.OpenRecordset(objName, dbOpenDynaset)</DIV>
<DIV> Do Until rsF.EOF 'Move records
from the
T_Data_Files<BR> <BR>
fldName = Left(rsF!PE, 4) & "_" & Right(rsF!PE, 2) &
"F"<BR>
rsD.AddNew<BR>
rsD!CLIENT =
rsF!CLIENT<BR>
rsD!REGION =
"FILE"<BR>
rsD!REV_TYPE =
"FILE"<BR>
rsD!FILE =
rsF!FILE<BR>
rsD(fldName) =
rsF!AMOUNT<BR>
rsD.Update<BR> <BR>
rsF.MoveNext<BR> Loop</DIV>
<DIV>Set rsF = Nothing</DIV>
<DIV>'******************************************************************************************<BR>'******************************************T_Data_Files************************************</DIV>
<DIV> If objType = "t"
Then<BR> passVar =
"T_Data_Products"<BR>
Else<BR> passVar =
"Q_Data_Products"<BR> End If</DIV>
<DIV>Call DataProducts(passVar, objType)</DIV>
<DIV>End Sub</DIV>
<DIV>Private Sub DataProducts(objName As String, objType As String)</DIV>
<DIV>'******************************************T_Data_Products*********************************<BR>'******************************************************************************************<BR>
<BR> Dim passVar As
String<BR>
<BR> Set rsD =
dbCurr.OpenRecordset("T_DATA",
dbOpenDynaset)<BR> Set rsP =
dbCurr.OpenRecordset("SELECT * FROM " & objName
_<BR>
& " ORDER BY CLIENT, FILE",
dbOpenDynaset)<BR>
<BR> Do Until rsP.EOF 'Move records
from the
T_Data_Products<BR>
<BR>
<BR>
rsD.FindFirst ("NZ(CLIENT,'NULL') = '" & Nz(rsP!CLIENT, "NULL") & "'
AND "
_<BR>
& " FILE = '" & rsP!FILE &
"'")<BR> If
rsD.NoMatch = True Then</DIV>
<DIV>
<BR>
fldName = Left(rsP!PE, 4) & "_" & Right(rsP!PE, 2) &
"P"<BR>
rsD.Edit<BR>
rsD(fldName) =
rsP!AMOUNT<BR>
rsD.Update<BR>
<BR>
Else 'If one is not found, then add
one<BR>
<BR>
fldName = Left(rsP!PE, 4) & "_" & Right(rsP!PE, 2) &
"P"<BR>
rsD.AddNew<BR>
rsD!CLIENT =
rsP!CLIENT<BR>
rsD!FILE =
rsP!FILE<BR>
rsD!REGION =
"FILE"<BR>
rsD!REV_TYPE =
"PRODUCT"<BR>
rsD(fldName) =
rsP!AMOUNT<BR>
rsD.Update</DIV>
<DIV>
End If</DIV>
<DIV> rsP.MoveNext<BR> Loop</DIV>
<DIV> Set rsP = Nothing</DIV>
<DIV>'******************************************************************************************<BR>'******************************************T_Data_Products*********************************</DIV>
<DIV> If objType = "t"
Then<BR> passVar =
"T_Data_Rev"<BR>
Else<BR> passVar =
"Q_Data_Rev"<BR> End If</DIV>
<DIV>Call DataRev(passVar, objType)</DIV>
<DIV>End Sub</DIV>
<DIV>Private Sub DataRev(objName As String, objType As String)</DIV>
<DIV>'******************************************T_Data_Rev**************************************<BR>'******************************************************************************************</DIV>
<DIV> Set rsD =
dbCurr.OpenRecordset("T_Data",
dbOpenDynaset)<BR> Set rsR =
dbCurr.OpenRecordset("SELECT * FROM " & objName
_<BR>
& " ORDER BY CLIENT, FILE, REV_TYPE, REGION", dbOpenDynaset)</DIV>
<DIV> Do Until rsR.EOF 'Move records
from the T_Data_Rev</DIV>
<DIV>
rsD.FindFirst ("NZ(CLIENT,'NULL') = '" & Nz(rsR!CLIENT, "NULL") & "'
AND "
_<BR>
& " FILE = '" & Nz(rsR!FILE, "No File Revenue") &
"'")<BR>
If rsD.NoMatch = True
Then<BR>
<BR>
fldName = Left(rsR!PE, 4) & "_" & Right(rsR!PE, 2) &
"R"<BR>
<BR>
rsD.AddNew<BR>
rsD!CLIENT =
rsR!CLIENT<BR>
rsD!REV_TYPE =
rsR!REV_TYPE<BR>
rsD!FILE = IIf(IsNull(rsR!FILE), "No File Revenue",
rsR!FILE)<BR>
rsD!RE! GION =
rsR!REGION<BR>
rsD(fldName) =
rsR!AMOUNT<BR>
rsD.Update<BR>
<BR>
Else</DIV>
<DIV>
fldName = Left(rsR!PE, 4) & "_" & Right(rsR!PE, 2) &
"R"<BR>
<BR>
rsD.Edit<BR>
rsD(fldName) =
rsR!AMOUNT<BR>
rsD.Update<BR>
<BR>
End If<BR>
<BR>
rsR.MoveNext<BR>
<BR>
Loop<BR>
<BR> Set rsR = Nothing</DIV>
<DIV>'******************************************************************************************<BR>'******************************************T_Data_Rev**************************************</DIV>
<DIV>End Sub<BR></DIV><BR><BR>
<DIV>
<DIV>
<P align=center><STRONG>Lonnie Johnson<BR></STRONG><STRONG>ProDev</STRONG>,
Professional Development of MS Access Databases<BR>Visit me at ==> <A
href="http://www.prodev.us/">http://www.prodev.us</A></P>
<P><BR><A
href="http://www.galaxymall.com/software/PRODEV"><BR></A><BR> </P></DIV></DIV>
<P>
<HR SIZE=1>
Do you Yahoo!?<BR><A
href="http://us.rd.yahoo.com/evt=10469/*http://sitebuilder.yahoo.com">Yahoo!
SiteBuilder</A> - Free, easy-to-use web site design
software</BLOCKQUOTE></BODY></HTML>