M
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
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