Recordset operation hangs

[Mark]

Hi everyone.

I'm doing some updating from one recordset to another and the
operation seems to get part way through and then seems to hang (as if
it was a bad loop) but then comes alive again with the operations
successfully completed.

Perhaps its the way the information is cached?

Both tables are linked: the target table is in MSDE (SQL) and the
source is a text file. Its not gonna be efficient (i know) but will
be a tool to help me achieve another task

I've included my code below if anyone is interested in having a look.

Thanks,
Mark



Option Compare Database

Private Sub Import(strSource As String, strTarget As String,
strMappings As String)

Dim wrkDefault As Workspace
Dim dbsSIMMS As Database
Dim rstSource As Recordset
Dim rstTarget As Recordset
Dim rstMappings As Recordset

Dim strCriteria As String

Set dbsSIMMS = OpenDatabase(Application.CurrentDb.name)
Set rstSource = dbsSIMMS.OpenRecordset(strSource, dbOpenDynaset)
Set rstTarget = dbsSIMMS.OpenRecordset(strTarget, dbOpenDynaset)
Set rstMappings = dbsSIMMS.OpenRecordset(strMappings,
dbOpenDynaset)

While Not rstSource.EOF

strCriteria = ""
rstMappings.MoveFirst
While Not rstMappings.EOF
If rstMappings("PrimaryKey") = True Then
If strCriteria <> "" Then
strCriteria = strCriteria & " AND "
End If
strCriteria = strCriteria & rstMappings("TargetField")
& " = '" & _
rstSource(rstMappings("SourceField")) & "'"
End If
rstMappings.MoveNext
Wend

rstTarget.FindFirst strCriteria
If rstTarget.NoMatch Then
rstTarget.AddNew
Else
rstTarget.Edit
End If

rstMappings.MoveFirst
While Not rstMappings.EOF
rstTarget(rstMappings("TargetField")) =
rstSource(rstMappings("SourceField"))
rstMappings.MoveNext
Wend

rstTarget.Update
rstSource.MoveNext

Wend

rstSource.Close
rstTarget.Close
rstMappings.Close
dbsSIMMS.Close

End Sub

Private Sub cmdProceed_Click()
Import "students", "dbo_students", "mappings"
End Sub

 

[Wayne Morgan]

Since you say it completes properly, I would guess that Access has gotten
tied up updating indexes or some other item as the changes are made. You may
want to try adding DoEvents into the loop to give Access the chance to work
on something else momemtarilly while the code is running. It may or may not
help.

At the end of your code, you are Closing you recordsets. This is good, but
you should also set the object variables =Nothing also. The general rule is
that if you open it, close it and if you set it, then set it to nothing when
done. You will need to do the Close first then the set to nothing or you'll
get an error message.

rstSource.Close
Set rstSource = Nothing

Also, you show Option Compare Database. I recommend that you also add Option
Explicit. This will require you to declare your variables (Dim). It helps
when trying to find typo errors, because the misspelled variable won't
compile since it hasn't been declared. You will need to manually add this to
all current modules, but to add it automatically to new modules go to
Tools|Options in the code editor and choose Require Variable Declaration on
the Edit tab. If you have several modules already, an easy way to add this
to all current modules is with the Replace dialog (Ctrl+H). In Find What
type

Option Compare Database

for Replace With type

Option Compare Database: Option Explicit

and for the scope, choose Entire Project. VBA will accept two commands on
one line if they are separated by a colon.