Jim Dettman
jimdettman at earthlink.net
Wed Sep 3 13:08:34 CDT 2003
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"
End If
Call DataFiles(passVar, objType)
End Sub
Private Sub DataFiles(objName As String, objType As String)
'******************************************T_Data_Files*********************
***************
'***************************************************************************
***************
Dim passVar As String
Set rsD = dbCurr.OpenRecordset("SELECT * FROM T_Data WHERE REGION
= 'CENTRAL'", dbOpenDynaset)
Set rsF = dbCurr.OpenRecordset(objName, dbOpenDynaset)
Do Until rsF.EOF 'Move records from the T_Data_Files
fldName = Left(rsF!PE, 4) & "_" & Right(rsF!PE, 2) &
"F"
rsD.AddNew
rsD!CLIENT = rsF!CLIENT
rsD!REGION = "FILE"
rsD!REV_TYPE = "FILE"
rsD!FILE = rsF!FILE
rsD(fldName) = rsF!AMOUNT
rsD.Update
rsF.MoveNext
Loop
Set rsF = Nothing
'***************************************************************************
***************
'******************************************T_Data_Files*********************
***************
If objType = "t" Then
passVar = "T_Data_Products"
Else
passVar = "Q_Data_Products"
End If
Call DataProducts(passVar, objType)
End Sub
Private Sub DataProducts(objName As String, objType As String)
'******************************************T_Data_Products******************
***************
'***************************************************************************
***************
Dim passVar As String
Set rsD = dbCurr.OpenRecordset("T_DATA", dbOpenDynaset)
Set rsP = dbCurr.OpenRecordset("SELECT * FROM " & objName _
& " ORDER BY CLIENT, FILE", dbOpenDynaset)
Do Until rsP.EOF 'Move records from the T_Data_Products
rsD.FindFirst ("NZ(CLIENT,'NULL') = '" & Nz(rsP!CLIENT,
"NULL") & "' AND " _
& " FILE = '" & rsP!FILE & "'")
If rsD.NoMatch = True Then
fldName = Left(rsP!PE, 4) & "_" & Right(rsP!PE, 2) &
"P"
rsD.Edit
rsD(fldName) = rsP!AMOUNT
rsD.Update
Else 'If one is not found, then add one
fldName = Left(rsP!PE, 4) & "_" & Right(rsP!PE, 2) &
"P"
rsD.AddNew
rsD!CLIENT = rsP!CLIENT
rsD!FILE = rsP!FILE
rsD!REGION = "FILE"
rsD!REV_TYPE = "PRODUCT"
rsD(fldName) = rsP!AMOUNT
rsD.Update
End If
rsP.MoveNext
Loop
Set rsP = Nothing
'***************************************************************************
***************
'******************************************T_Data_Products******************
***************
If objType = "t" Then
passVar = "T_Data_Rev"
Else
passVar = "Q_Data_Rev"
End If
Call DataRev(passVar, objType)
End Sub
Private Sub DataRev(objName As String, objType As String)
'******************************************T_Data_Rev***********************
***************
'***************************************************************************
***************
Set rsD = dbCurr.OpenRecordset("T_Data", dbOpenDynaset)
Set rsR = dbCurr.OpenRecordset("SELECT * FROM " & objName _
& " ORDER BY CLIENT, FILE, REV_TYPE,
REGION", dbOpenDynaset)
Do Until rsR.EOF 'Move records from the T_Data_Rev
rsD.FindFirst ("NZ(CLIENT,'NULL') = '" &
Nz(rsR!CLIENT, "NULL") & "' AND " _
& " FILE = '" & Nz(rsR!FILE, "No File
Revenue") & "'")
If rsD.NoMatch = True Then
fldName = Left(rsR!PE, 4) & "_" & Right(rsR!PE, 2)
& "R"
rsD.AddNew
rsD!CLIENT = rsR!CLIENT
rsD!REV_TYPE = rsR!REV_TYPE
rsD!FILE = IIf(IsNull(rsR!FILE), "No File
Revenue", rsR!FILE)
rsD!RE! GION = rsR!REGION
rsD(fldName) = rsR!AMOUNT
rsD.Update
Else
fldName = Left(rsR!PE, 4) & "_" & Right(rsR!PE, 2)
& "R"
rsD.Edit
rsD(fldName) = rsR!AMOUNT
rsD.Update
End If
rsR.MoveNext
Loop
Set rsR = Nothing
'***************************************************************************
***************
'******************************************T_Data_Rev***********************
***************
End Sub
Lonnie Johnson
ProDev, Professional Development of MS Access Databases
Visit me at ==> http://www.prodev.us
----------------------------------------------------------------------------
--
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030903/cb10962f/attachment-0001.html>