<DIV>Thanks again.<BR><BR>I'm going to try the query process. I have a question. One of my issues is that Table1 where all the transactions go has many fields that get filled dynamically based on a field in the other tables like so...<BR><BR>Table1 has fields...<BR>"Client", "2003R", "2003F", "2003P"<BR><BR>Table2 has...<BR>"Client", "PE", "Amount"<BR><BR><BR>Table2 Data<BR><BR>Client PE Amount<BR>ABC 2003Rx 5<BR>DEF 2003Px 6<BR>XYZ 2003Fx 4<BR><BR>My update query should make Table1 look
like...<BR><BR>Client 2003R 2003F 2003P<BR>ABC 5<BR>DEF 6<BR>XYZ 4<BR><BR>See I have to dynamically select the field that the AMOUNT is going into by whats in a field in the from table. I don't know how to dynamically determine the field name with an SQL statement. This is what I tried...<BR><BR>DoCmd.RunSQL "INSERT INTO Table1( Client, <STRONG>Left([PE],4)</STRONG>)SELECT Client, Amount FROM Table2"<BR><BR>This is why I was using the recordset method because I could dynamically determine the field name with
each iteration.<BR><BR>Any further advise would be appreciated.<BR><BR><BR><B><I>Jim Dettman <jimdettman@earthlink.net></I></B> wrote:
<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=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>_______________________________________________<BR>AccessD mailing list<BR>AccessD@databaseadvisors.com<BR>http://databaseadvisors.com/mailman/listinfo/accessd<BR>Website: http://www.databaseadvisors.com<BR></BLOCKQUOTE></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