By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

Recordset operation seems to hang

P: n/a
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
Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.