[AccessD] Speed up DAO processing

<SEEK is not usaeable with linked tables. >
 
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)
 
  Function WhichDB(strTableName As String) As Database
Dim dbpath$, SourceTable$, dbTest As Database
 
On Error GoTo whichDB_ERR
Set dbTest = DBEngine(0)(0)
dbpath = Mid(dbTest(strTableName).Connect, InStr(1,
dbTest(strTableName).Connect, "=") + 1)
If dbpath = "" Then
    Set dbTest = CurrentDb()
Else
    Set dbTest = DBEngine(0).OpenDatabase(dbpath)
End If
Set WhichDB = dbTest
whichDB_EXIT:
  Exit Function
  
whichDB_ERR:
  MsgBox Err.Description
  Resume whichDB_EXIT
End Function
 
Now seek will work properly with the backend. I often use seek on small
tables to retrieve parameters such as path names.
 
Function Pathnamefile(catcodekey As String) As String
Dim pathnme As String, rstbase As Recordset, dbs As Database, strTable As
String
 ' Comments   : Retrieves pathname of record where catcode1=catcodekey
  ' --------------------------------------------------------
  On Error GoTo PROC_ERR
  strTable = "Paths"
  Set dbs = WhichDB(strTable)
  'opens the paths table
  Set rstbase = dbs.OpenRecordset(strTable, dbOpenTable)
  rstbase.Index = "catcode1"
  'sets pointer to record where catcode1 field is set to Process
  rstbase.Seek "=", catcodekey
  'retrieves path where catcode1 field is set to chosen case
  Pathnamefile = rstbase.Fields("path")
  Exit Function
 
PROC_ERR:
  MsgBox "The following error occured: " & Error$
  Resume Next
 
End Function

-----Original Message-----
From: Erwin Craps [mailto:Erwin.Craps at ithelps.be]
Sent: Thursday, September 04, 2003 1:46 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Speed up DAO processing



OK see the prob...
I had some speed issues to when comparing 10 or even 100 thousand records.
There is a thing with findfirst that I believe is a bug in A2K, don't know
for A97 or AXP.
 
 
SEEK is not usaeable with linked tables. SEEK u can use only one field.
SEEK is at my knowledge not faster than findfirst, this was only true in
Access v2.
 
The problem with findfirst lies in the AND criteria.
For some reasons findfirst is terribly slow when using more than one field
parameter.
Even when having indexes on both fields or a joint field index. I believe
this is a bug.
 
so don't use multiple citeria in findfirst.
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.
(you gonna have a lot of SQL queries in this loop)
 
Rethink if you cannot do your thing only with queries. this is always
faster.
I see however the prob with this compare and sometimes queries are not
updateable depending on the data.
Please put some indexes or the criteria fields.
 
Another remark thats important when reading/editting large quantities of
records.
 
 rsP!AMOUNT=123 is slower than  rsP(AMOUNT)=123
rsp.fields(AMOUNT)=123 is same speed as rsP(AMOUNT)=123
 
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...
 
Erwin 
 
 
 
 
 
 

-----Oorspronkelijk bericht-----
Van: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] Namens Jim Dettman
Verzonden: woensdag 3 september 2003 20:09
Aan: Access Developers discussion and problem solving
Onderwerp: RE: [AccessD] Speed up DAO processing


Lonnie,
 
  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.
 
Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Lonnie Johnson
Sent: Wednesday, September 03, 2003 2:02 PM
To: 'MS-ACCESS-L at lists.missouri.edu'; AccessDevelopers; ms_access; AccessD
solving'
Subject: [AccessD] Speed up DAO processing


I have four tables.
 
Table1 is where all the records end up. It starts out empty.
 
1. I take all transactions from Table2 and append them into Table1.
 
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.
 
3. I the do the same as in step 2 for Table4.
 
I am using DAO recordset processing and use the RS.FindFirst method to see
if records from Table3 or 4 are in Table1. 
 
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.
 
Option Compare Database
    Dim dbCurr As DAO.Database
    
    Dim rsD As DAO.Recordset
        Dim rsF As DAO.Recordset
            Dim rsP As DAO.Recordset
                Dim rsR As DAO.Recordset
                
    Dim fldName As String
    
    Public Sub TestRun()
       
        Call uTData("t")
        
    End Sub
Public Sub uTData(objType As String)
        
        Dim passVar As String
        
        Set dbCurr = CurrentDb
                
        'Clear the old values
        DoCmd.SetWarnings False
            DoCmd.RunSQL "DELETE * FROM t_DATA"
                DoCmd.SetWarnings True
                
       If objType = "t" Then
       passVar = "T_Data_Files"
       Else
       passVar = "Q_Data_Files"


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030905/15a9fcfb/attachment-0001.html>