<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Bericht</TITLE>
<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=740323211-04092003>Erwin,</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=740323211-04092003></SPAN></FONT> </DIV>
<DIV><FONT><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></BODY></HTML>