Re: Homegrown synchronization

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance


I would say that 1 minute is really too short an interval, because
many update operations will take that long to complete. I wouldn't
do anything less than 5 minutes, I think, and probably would settle
on 10 minutes.

Of course for testing purposes, timers are short. I currently have two
timers in the synch app. One timer would be for the synchapp to check
if it needs to take over as the 'manager' of the production backend.
The other timer would be for checking for updates to apply and for
creating zip files of the production backend. Perhaps I should include
a third form timer, however, to check for updates. Here is how I would
think the timers should be set

1) Short duration timer - check if SyncApp needs to take over as the
'manager'. If already the 'manager' the code in the timer is bypassed.

2) Longer duration timer - code in the timer only executes if the
SyncApp is the 'manager' of the production backend. This code will
look for updates, in the server 'Inbox', to apply to production
backend.

3) Longest duration timer - creation of a zipped file of the production
backend file (or possibly a copy created from TransferDatabase). This
zipped file is the one that is placed in the server 'Outbox' and is the
file that will be downloaded by the remote copies of MyApp for updating
the remote local backend file.


2) Question about how to use FileCopy. I currently check that the
file to copy exists then I kill the file to be copied over and
thereafter accomplish the FileCopy using the target name as the
same name of the copy I just killed.

But if FileCopy fails, I've lost the original. I'm curious as to
how you would do this. It probably would be better to rename the
copy to be copied over, accomplish the FileCopy and then kill the
renamed copy if FileCopy succeeds. I guess I would need to
restore the renamed copy if FileCopy fails; so that might mean
knowing all the possible error codes.

I'm not sure which file you're dealing with. I thought you were
archiving the user files, so this must be the zip file? If you lose
it, you can just recreate it, no?

Actually, this is how I generally do FileCopy, I was just beginning to
think that my method is dangerous.

3) I found out that users have registered copies of WinZip on
their machines (yoohoo!!).

Now I have some questions related to this. With WinZip when a
zipped file is to be unzipped users get to answer Yes, Yes To All,
No, etc. After my code calls the Unzip routine, it writes a record
to the table that that zipped file was unzipped, so that the code
doesn't keep trying to unzip the same file every time it is
invoked. The problem is if the user answers 'No' to Winzip or
Winzip fails, that update can no longer be applied by the code as
written, because the code doesn't know this happened (since it is
an external app event). So a record always gets written to the
table that the file was unzipped.

One way to handle this is to give remote users the ability to open
the UpdateInbox and select files to try and unzip. A kind of last
resort method. But if users select poorly, they might update the
local backend file with really old data and that could cause
problems.

This is another reason why I'd want to use the zlib DLL, because you
should be able to get response codes from it (I haven't looked,
actually), rather than depending on shelling out to an external app.

What about including a file in each zip file that is a semaphore
that echoes the name of the zip file and if it's not there, you know
they didn't complete the unzip operation? If it's there, you can
kill it and continue.

I'll have to check how to do this from code using WinZip, but a text
file for example. I'll have to test it out, because I don't know what
happens if you cancel out of the Unzip operation.


This is not a very satisfactory option. Any thoughts on how to
handle the WinZip failing or User answering 'No' to Winzip
messagebox? If I could trap those events, then the code would not
write the record to the table. The next time around code would try
to unzip it again.

I can't think of anything other than the semaphore file included in
the zip archive without getting rid of WinZip.

4) I have added menu items to allow users to check if Updates are
available on the server and download the zip file and also to send
out updates from the remote machine. Previously these actions
only occurred on opening the Myapp and closing the MyApp
respectively.

I always think that's good, that in addition to automatic updates
you give the user the option of running it manually.

Now I know you don't agree with simply replacing the local backend
with the zipped backend downloaded from the server. But this is
just a general question.

The tricky part in doing something like this is that one is trying
to replace a backend file that MyApp is currently connected to.
Errors will occur.

You'll want to close all the forms that are bound to tables in the
back end, which is pretty easy to do, no?

So the way I'm getting around this is by doing two things:

a) Alert the user that all forms will be closed. If they agree all
forms are closed.

b) Invoking three lines of code to close all open recordsets.

In doing this, I find that the FileCopy over the existing local
backend file proceeds without a problem (at least so far).

Sounds right. Dunno why you'd have any open recordsets hanging
around, though.
Just a safety precaution in case I missed closing some recordsets in
code. The vast majoirty of my data access operations are done via DAO
and not through Form Recordsets.


Now I'm not saying that I'm going to continue to do this (I'm
still considering your other suggestion). I'm just curious that
if one were to do this is this the right way or have I missed
something.

Sounds to me like the only way to do it.

5) Creating the update file to send from the remote copy to the
server is taking a relatively long amount of time. Virtually all
of it is due to the method for determining which records were
deleted. Just to refresh reader's memories, this non-Jet
synchronization method is being implemented well after the app has
been coded. Currently, deleted records from tables are deleted;
we are not using a DeleteFlag in tables.

I thought you were copying them to the update file at the time of
deletion?

So this means that the only way I can think of to check for
deletes without adding code to the already coded app, is to
compare the current state of the local backend file with a copy of
the backend file that was made at session start. The code looks
for differences but it has to go through every table to do it;
that makes it relatively slow.

I think that copying the deleted records into the update file would
be rather similar to the replication tombstones table (though it
wouldn't be persistent).

After thinking about it, I realized that although I have about 100
tables, there are really only four very large tables. So I added
some code to ignore these four tables and ran the createUpdateFile
code again. The time was cut in half to accomplish this.

So for these four tables I may actually add some code to handle
deletes for the update file immediately when the user actually
deletes records from those tables. Then in my CreateUpdateFile
code the code will ignore those tables and that will speed it up
substantially. This needs more thought but is a possibility.

I think I'd want to use the same deletion method for all tables,
because I know that when I come back to apps that have things
implemented in different ways in different places that I confuse
myself. In any event, if you use both methods, document it
extensively.

So here would be the method. In my code, whereever I have a Delete
operation, I add code that uses an SQL Insert statement and inserts the
record(s) that are to be deleted. So this Insert code would be just
before the Delete action query and would be after the user confirms
that they want to delete the record(s). One of the things I didn't
like about Jet replication is how a backend file dramatically increases
in size when it is replicated (in my case by a factor of 2 or more).
The tombstone table seemed to be a major reason why. I guess I could
just keep the records in the tombstone table for that session only and
then clear the table the next time the app opens.

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

.

Relevant Pages

  • Re: Homegrown synchronization
    ... Under the assumption that the SyncApp is relatively simple coding ... But if FileCopy fails, I've lost the original. ... The problem is if the user answers 'No' to Winzip or ... to replace a backend file that MyApp is currently connected to. ...
    (microsoft.public.access.replication)
  • Re: Homegrown synchronization
    ... you set the timer to 3 hours in the future. ... backend file, I'll probably just close all forms and recordsets ... and create a zipped copy of the remote backend file to send; ... update to the server in the 1 minute or so range. ...
    (microsoft.public.access.replication)
  • Copy or BackUp data from linked tables
    ... I want to Copy a backend file or Copy the Data and Structure (Incl. ... So far I cannot "filecopy" because the file is in use. ... Should I somehow drop the link to the backend and then filecopy and relink ...
    (microsoft.public.access.externaldata)