<!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><FONT face=Arial color=#0000ff size=2><SPAN
class=540420014-04092003>Lonnie,</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=540420014-04092003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=540420014-04092003><FONT
color=#0000ff><<<FONT face="Times New Roman" size=3>I will attempt to use
the SEEK method. I was avoiding it because of having to do the indexing and my
need for multiple criteria.>></FONT></FONT></SPAN></FONT></DIV>
<DIV><FONT face="Times New Roman" color=#0000ff size=3><SPAN
class=540420014-04092003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=540420014-04092003>
As Erwin pointed out, there are constraints that come with SEEK that you may not
be able to live with, but if you can, it is by far the fastest method of
locating a record.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff 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> Thursday, September 04, 2003 9:53 AM<BR><B>To:</B>
Access Developers discussion and problem solving<BR><B>Subject:</B> RE:
[AccessD] Speed up DAO processing<BR><BR></FONT></DIV>
<DIV>I sincerly thank all of you for your help. I will attempt to use the SEEK
method. I was avoiding it because of having to do the indexing and my need for
multiple criteria. Thanks Erwin for your input and yours as well Jim.</DIV>
<DIV> </DIV>
<DIV>I will be printing your mails to add to my folder of helpful tips.</DIV>
<DIV><BR><BR><B><I>Jim Dettman <jimdettman@earthlink.net></I></B>
wrote:</DIV>
<BLOCKQUOTE class=replbq
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">
<META content="MSHTML 6.00.2800.1226" name=GENERATOR>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=740323211-04092003>Erwin,</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=740323211-04092003></SPAN></FONT> </DIV>
<DIV><FONT size=+0><SPAN class=740323211-04092003><FONT face=Arial
color=#0000ff size=2><<</FONT>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>SEEK is not usaeable with linked tables. SEEK u can use only one
field.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>SEEK is at my knowledge not faster than findfirst, this was only true
in Access v2.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial color=#0000ff size=2>>></FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial color=#0000ff size=2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial color=#0000ff size=2> Although seek is unavailable with
linked tables, it's a simple matter to open the remote database and open the
table directly:</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial color=#0000ff size=2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial color=#0000ff size=2>'<BR>' First, get the path to the MDB for
the attached table.<BR>'<BR> Set wrk =
DBEngine.Workspaces(0)<BR> Set dbCurrent = wrk.Databases(0)<BR>
Set tdfAttached = dbCurrent.TableDefs("tblGeoAllData")<BR> <BR>
strPath = tdfAttached.Connect<BR> strPath = right$(strPath,
Len(strPath) - InStr(strPath, "="))<BR> <BR> Set dbRemote =
wrk.OpenDatabase(strPath, False, True)<BR> <BR>'<BR>' First,
find the postal code. Need to know the country.<BR>'<BR>
intHaveMatch = True<BR> <BR> Set rst1 =
dbRemote.OpenRecordset("tblGeoAllData", DB_OPEN_TABLE)<BR> rst1.index
= "PrimaryKey"<BR> rst1.Seek "=", CountryCode,
PostalCode</FONT></SPAN></SPAN></DIV></SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=740323211-04092003><FONT face=Arial color=#0000ff
size=2><<</FONT>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>SEEK is at my knowledge not faster than findfirst, this was only true
in Access v2.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial color=#0000ff size=2>>></FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial color=#0000ff size=2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial color=#0000ff size=2> Totally disagree. Seek is
consistently faster then any other method for any
version.</FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial color=#0000ff size=2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial color=#0000ff size=2><<</FONT>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>The problem with findfirst lies in the AND
criteria.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>For some reasons findfirst is terribly slow when using more than one
field parameter.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>Even when having indexes on both fields or a joint field index.
I believe this is a bug.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial color=#0000ff size=2>>></FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial color=#0000ff size=2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><SPAN class=740323211-04092003><FONT
face=Arial color=#0000ff size=2> It's documented in the MSKB that in
order for compound indexes to be used, the index must match the sequence of
the criteria. If not, the index will not be used and a table scan be
performed. It's not a bug, just the way it
works.</FONT></SPAN></SPAN></DIV></SPAN></SPAN></DIV>
<DIV></SPAN><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></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>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com]<B>On Behalf Of </B>Erwin
Craps<BR><B>Sent:</B> Thursday, September 04, 2003 2:46 AM<BR><B>To:</B>
Access Developers discussion and problem solving<BR><B>Subject:</B> RE:
[AccessD] Speed up DAO processing<BR><BR></FONT></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>OK see the prob...</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>I had some speed issues to when comparing 10 or even 100 thousand
records.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>There is a thing with findfirst that I believe is a bug in A2K,
don't know for A97 or AXP.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>SEEK is not usaeable with linked tables. SEEK u can use only one
field.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>SEEK is at my knowledge not faster than findfirst, this was only
true in Access v2.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>The problem with findfirst lies in the AND
criteria.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>For some reasons findfirst is terribly slow when using more than
one field parameter.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>Even when having indexes on both fields or a joint
field index. I believe this is a bug.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>so don't use multiple citeria in
findfirst.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>Use SQL strings in a loop with criteria and use only
one in findfirst. This is the fastest you will get if you really need
line-by-line processing. If you can avoid using the ORDER BY parameter
in your SQL string this will speed thing even more.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>(you gonna have a lot of SQL queries in this
loop)</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>Rethink if you cannot do your thing only with queries. this is
always faster.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>I see however the prob with this compare and sometimes queries
are not updateable depending on the data.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>Please put some indexes or the criteria
fields.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>Another remark thats important when reading/editting large
quantities of records.</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003> rsP!AMOUNT=123 is slower
than rsP(AMOUNT)=123</SPAN></DIV>
<DIV><SPAN class=679415605-04092003>rsp.fields(AMOUNT)=123 is same speed
as rsP(AMOUNT)=123</SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>stop using the ! parameter, it is outdated and slows down. There
is only one situation ! is still need and I believe in a form or report
referencing somewhere...</FONT></SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2>Erwin</FONT> </SPAN></DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=679415605-04092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=nl dir=ltr align=left><FONT
face=Tahoma size=2>-----Oorspronkelijk bericht-----<BR><B>Van:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com] <B>Namens </B>Jim
Dettman<BR><B>Verzonden:</B> woensdag 3 september 2003
20:09<BR><B>Aan:</B> Access Developers discussion and problem
solving<BR><B>Onderwerp:</B> RE: [AccessD] Speed up DAO
processing<BR><BR></FONT></DIV>
<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><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV>Table1 is where all the records end up. It starts out
empty.</DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV>1. I take all transactions from Table2 and append them into
Table1.</DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </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><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV>3. I the do the same as in step 2 for Table4.</DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </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><FONT face=Arial color=#0000ff size=2></FONT> </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><FONT face=Arial color=#0000ff size=2></FONT> </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></DIV></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE>_______________________________________________<BR>AccessD
mailing
list<BR>AccessD@databaseadvisors.com<BR>http://databaseadvisors.com/mailman/listinfo/accessd<BR>Website:
http://www.databaseadvisors.com<BR></BLOCKQUOTE><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>