<!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>Bericht</TITLE>
<META content="MSHTML 6.00.2800.1126" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=624113414-05092003><FONT face=Arial color=#0000ff size=2><SPAN
class=679415605-04092003><FONT face=Arial color=#0000ff size=2><SEEK is not
usaeable with linked tables. ></FONT></SPAN></FONT></SPAN></DIV>
<DIV><SPAN class=624113414-05092003><FONT face=Arial color=#0000ff size=2><SPAN
class=679415605-04092003></SPAN></FONT></SPAN> </DIV>
<DIV><SPAN class=624113414-05092003><FONT face=Arial color=#0000ff size=2>Seek
can be made to work with linked tables. the secret is in pointing the database
object to the backend with the following function: (I believe this is from a M$
article if I remeber correctly)</FONT></SPAN></DIV>
<DIV><SPAN class=624113414-05092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=624113414-05092003><FONT face=Arial color=#0000ff size=2>
Function WhichDB(strTableName As String) As Database<BR>Dim dbpath$,
SourceTable$, dbTest As Database</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=624113414-05092003><FONT face=Arial color=#0000ff size=2>On
Error GoTo whichDB_ERR<BR>Set dbTest = DBEngine(0)(0)<BR>dbpath =
Mid(dbTest(strTableName).Connect, InStr(1, dbTest(strTableName).Connect, "=") +
1)<BR>If dbpath = "" Then<BR> Set dbTest =
CurrentDb()<BR>Else<BR> Set dbTest =
DBEngine(0).OpenDatabase(dbpath)<BR>End If<BR>Set WhichDB =
dbTest<BR>whichDB_EXIT:<BR> Exit Function<BR>
<BR>whichDB_ERR:<BR> MsgBox Err.Description<BR> Resume
whichDB_EXIT<BR>End Function</FONT></SPAN></DIV>
<DIV><SPAN class=624113414-05092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=624113414-05092003><FONT face=Arial color=#0000ff size=2>Now
seek will work properly with the backend. I often use seek on small tables to
retrieve parameters such as path names.</FONT></SPAN></DIV>
<DIV><SPAN class=624113414-05092003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=624113414-05092003><FONT face=Arial color=#0000ff
size=2>Function Pathnamefile(catcodekey As String) As String<BR>Dim pathnme As
String, rstbase As Recordset, dbs As Database, strTable As String<BR> '
Comments : Retrieves pathname of record where
catcode1=catcodekey<BR> '
--------------------------------------------------------<BR> On Error GoTo
PROC_ERR<BR> strTable = "Paths"<BR> <STRONG>Set dbs =
WhichDB(strTable)<BR></STRONG> 'opens the paths table<BR> Set
rstbase = dbs.OpenRecordset(strTable, dbOpenTable)<BR> rstbase.Index =
"catcode1"<BR> 'sets pointer to record where catcode1 field is set to
Process<BR> rstbase.Seek "=", catcodekey<BR> 'retrieves path where
catcode1 field is set to chosen case<BR> Pathnamefile =
rstbase.Fields("path")<BR> Exit Function</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=624113414-05092003><FONT face=Arial color=#0000ff
size=2>PROC_ERR:<BR> MsgBox "The following error occured: " &
Error$<BR> Resume Next</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=624113414-05092003><FONT face=Arial color=#0000ff size=2>End
Function</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> Erwin Craps
[mailto:Erwin.Craps@ithelps.be]<BR><B>Sent:</B> Thursday, September 04, 2003
1: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>