Access 97 to 2002. Modules Execution Slows

[kineticfrog]

I have taken a MDB from Access 97 to 2002 using the
conversion tool.

The conversion was success full and of the queries I have
tested, so far, all work.

I have reached a point where I am running a Subroutine in
a module. It is a fairly simple SUB and basically writes
a concantenated string to an input table based on an
output table.

In access 97 this code (below) reads about 27,500 records
and inserts a resulting 10,500 in a couple minutes (if
that). In Access 2002, it is barely 25% done after 10
minutes.

After about 3,500 insertions it slows down dramatically
(as well the MDB grows quite big). You can literally see
it happen in the immediate/debug window.

Each of the datasets is an access table, not a query. The
table is already sorted as need to be read by the SUB.

Here is the CODE:

Sub Build_RoutingPath()
Dim oData As Database
Dim oRS As Recordset
Dim oInput As Recordset
Dim sInput, sOutput, sLocale, sPrevLoc, sDebug As String

Dim sPart As String
Dim sSeq As String

sOutput = "ALL_ROUTINGS"
sInput = "ROUTING_PATH"

Set oData = CurrentDb
Set oRS = oData.OpenRecordset(sOutput)
Set oInput = oData.OpenRecordset(sInput)

oData.Execute ("delete * from " & sInput)

oRS.MoveFirst
sPart = ""
sSeq = ""
sLocale = oRS![LOCALE] & ""
Do Until oRS.EOF
sLocale = oRS![LOCALE]
If sPart = oRS![ITEM_REF] Then
sSeq = oRS![OPER_SEQ]
'the movelast is required to insure the insert
occurs at the last position. it has been noted that this
will not always occur if left out.
oInput.MoveLast
oInput.Edit
oInput![PROCESS_PATH] = oInput![PROCESS_PATH]
& ">" & oRS![PROCESS]
oInput![ACRO_PATH] = oInput![ACRO_PATH] & ">" &
oRS![PROCESS_ACRO]
oInput![WC_PATH] = oInput![WC_PATH] & ">" & Trim
(oRS![WORK_CTR])
oInput![RUN_PATH] = oInput![RUN_PATH] & ">" & oRS!
[RUN_RATE]
oInput![SETUP_PATH] = oInput![SETUP_PATH] & ">" &
oRS![SETUP_RATE]
oInput![LOC_PATH] = oInput![LOC_PATH] & ">" &
sLocale
sDebug = oInput![WC_PATH] & ""
Else
oInput.MoveLast
sPart = oRS![ITEM_REF]
sSeq = oRS![OPER_SEQ]
oInput.AddNew
oInput![PART] = oRS![ITEM_REF]
oInput![AREA] = oRS![AREA]
oInput![PROCESS_PATH] = oRS![PROCESS]
oInput![ACRO_PATH] = oRS![PROCESS_ACRO]
oInput![WC_PATH] = Trim(oRS![WORK_CTR])
oInput![RUN_PATH] = oRS![RUN_RATE]
oInput![SETUP_PATH] = oRS![SETUP_RATE]
oInput![LOC_PATH] = sLocale
oInput![ADD_DATE] = oRS![MinOfADD_DATE]
sDebug = oInput![WC_PATH] & ""
End If
oInput.Update
Debug.Print sPart & sDebug
sPrevLoc = sLocale & ""
oRS.MoveNext
Loop

End Sub

 

[Guest]

Not that the line wrapping in my code is a result of the
newsreader