Re: Homegrown synchronization

I'm not sure, though, why your current code is so slow. It's just an
outer join (like you get using the unmatched query wizard). That
really ought to be quite fast if the fields you're joining on are
indexed.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/


Well, I haven't really posted any code so here it is for the deletes
(not complete just the relevant parts) .
The code is comparing two complete backends (the snapshot when
the user opens a session in MyApp and the current state of the
backend at the time the code executes. zqry* is the RWOP
query for the relevant table in the "snapshot" backend. Please
remember
that I'm not an advanced programmer.

Note: I had to reformat each line to get the code to fit the width
so that it could be read, so there's likely some errors but not
in the actual code.

Maybe it's the dynamic building of the SQL statement that
causes it to execute slowly.
========================================================================================================================

For Each tdf1 In db1.TableDefs 'Tables in database #1

'replace the table name with the corresponding RWOP query.
'for both the actual backend and the tmp copy of the backend
QueryToUse = Replace(tdf1.Name, "tbl", "")
CompareQuerytoUse = Replace(tdf1.Name, "tbl", "zqry")

strSQLDELETE = "SELECT A.* FROM [" & CompareQuerytoUse & "]" & _
AS A LEFT JOIN [" & QueryToUse & "] As B ON"

'Now build the "ON" phrase based on the primary key in the table.
'The keys for the two tables are identical so just get the primary
'key for the table in the current database

For Each idx1 In tdf1.Indexes
If idx1.Primary = True Then
For Each fld1 In idx1.Fields
If Right(strSQLDELETE, 2) = "ON" Then
strSQLDELETE = strSQLDELETE & _
" (B." & fld1.Name & " = A." & fld1.Name & ")"
Else
strSQLDELETE = strSQLDELETE & _
" AND (B." & fld1.Name & " = A." & fld1.Name & ")"
End If
Next fld1
End If
Next idx1

'Now add the Is Null Where clause
For Each idx1 In tdf1.Indexes
If idx1.Primary = True Then
For Each fld1 In idx1.Fields
If InStr(strSQLDELETE, "WHERE") Then
strSQLDELETE = strSQLDELETE & _
" AND B." & fld1.Name & " Is Null"
Else
strSQLDELETE = strSQLDELETE & _
" WHERE B." & fld1.Name & " Is Null"
End If
Next fld1
End If
Next idx1

'Now determine if anything is found. If so, then write the record,

'if not, then move to the next table.
Set rsDELETE = dbDELETE.OpenRecordset(strSQLDELETE, dbOpenDynaset,
dbSeeChanges)
rsDELETEFlag = True

If rsDELETE.RecordCount > 0 Then

strSQLDELETE = "INSERT INTO [" & UpdateDBName & "].[" & _
tdf1.Name & "] " & strSQLDELETE

CurrentDb.Execute strSQLDELETE, dbFailOnError + dbSeeChanges

'Now set the flag in the table so that the importing code will
'know to delete the record from the table
strSQLSETFLAG = "Update [" & UpdateDBName & "]" & _
".[" & tdf1.Name & "]" SET UpdateorDeleteFlag = True"
CurrentDb.Execute strSQLSETFLAG, dbFailOnError + dbSeeChanges

'This lets subsequent code know that there is an update file
'that needs to be sent to the server.
SendFlag = True

End If


500: Next tdf1

========================================================================================================================

.