Need help with VBA performance!!

  • Thread starter Thread starter dave
  • Start date Start date
D

dave

I have some VBA code in Access 2000 that uses recordsets
to append or delete data from local tables in access. On
the first time around the speed performance is excellent.
However, if I immediately run the code again, it is
extremely slower. But if I close the database, open it
back up and rerun the code, then it is just as fast as
before.

If anyone can help me understand what is causing it to
slow down on the second time around or knows how I can
fix this, I would greatly appreciate it.

Thanks, Dave
 
This is one example of many modules that give me give
grief on the second time around in Access 2000. I did
not have this problem in Access 97, which leads me to
consider converting my databases back to the earlier
version.

Thanks in advance for your help.

Dave

Dim dbs As DAO.Database
Dim rstW As DAO.Recordset, rstP As Recordset
Dim intX As Integer

SetNo
DoCmd.RunSQL "DELETE * FROM tbl0186_1;"

DoCmd.RunSQL "INSERT INTO tbl0186_1 SELECT tbl0186_Imp.*
FROM tbl0186_Imp;"

SetYes

Set dbs = CurrentDb
Set rstP = dbs.OpenRecordset("fsa_PeriodTranslation2")
rstP.Index = "WeekDate"
Set rstW = dbs.OpenRecordset("tbl0186_1")
If rstW.RecordCount > 0 Then
rstW.MoveFirst
Do Until rstW.EOF
MeterStep MS, "Converting Julian Date", 0.0245, -1
rstW.Edit
rstW!OrderDate = DateSerial(Int(rstW!
[OrderDateJulian] / 1000), 1, rstW![OrderDateJulian] Mod
1000)
rstW!ExpectDate = DateSerial(Int(rstW!
[ExpectDateJulian] / 1000), 1, rstW![ExpectDateJulian]
Mod 1000)
rstW!RcvdDate = DateSerial(Int(rstW!
[RcvdDateJulian] / 1000), 1, rstW![RcvdDateJulian] Mod
1000)
rstP.Seek "=", rstW![RcvdDate]
If Not rstP.NoMatch Then
rstW!FYear = rstP!FYear
rstW!ABSWeekNum = rstP!ABSWeekNum
End If
rstW.Update
MS = MS + 1
rstW.MoveNext
Loop
End If

rstW.Close
rstP.Close
dbs.Close
 
Change rstP As Recordset to...rstP As DAO.Recordset. Remove the last line.
Aside from that, is there a specific part of the routine that takes more
time than others the second time around? You might insert some debug.print
Now() statements to ascertain what is slow.

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


dave said:
This is one example of many modules that give me give
grief on the second time around in Access 2000. I did
not have this problem in Access 97, which leads me to
consider converting my databases back to the earlier
version.

Thanks in advance for your help.

Dave

Dim dbs As DAO.Database
Dim rstW As DAO.Recordset, rstP As Recordset
Dim intX As Integer

SetNo
DoCmd.RunSQL "DELETE * FROM tbl0186_1;"

DoCmd.RunSQL "INSERT INTO tbl0186_1 SELECT tbl0186_Imp.*
FROM tbl0186_Imp;"

SetYes

Set dbs = CurrentDb
Set rstP = dbs.OpenRecordset("fsa_PeriodTranslation2")
rstP.Index = "WeekDate"
Set rstW = dbs.OpenRecordset("tbl0186_1")
If rstW.RecordCount > 0 Then
rstW.MoveFirst
Do Until rstW.EOF
MeterStep MS, "Converting Julian Date", 0.0245, -1
rstW.Edit
rstW!OrderDate = DateSerial(Int(rstW!
[OrderDateJulian] / 1000), 1, rstW![OrderDateJulian] Mod
1000)
rstW!ExpectDate = DateSerial(Int(rstW!
[ExpectDateJulian] / 1000), 1, rstW![ExpectDateJulian]
Mod 1000)
rstW!RcvdDate = DateSerial(Int(rstW!
[RcvdDateJulian] / 1000), 1, rstW![RcvdDateJulian] Mod
1000)
rstP.Seek "=", rstW![RcvdDate]
If Not rstP.NoMatch Then
rstW!FYear = rstP!FYear
rstW!ABSWeekNum = rstP!ABSWeekNum
End If
rstW.Update
MS = MS + 1
rstW.MoveNext
Loop
End If

rstW.Close
rstP.Close
dbs.Close
-----Original Message-----
Post your code

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com





.
Click to expand...
Click to expand...
 
Back
Top