Re: Homegrown synchronization

"rdemyan" <rdemyan@xxxxxxxxxxx> wrote in
news:1167266561.984368.281860@xxxxxxxxxxxxxxxxxxxxxxxxxxx:

David W. Fenton wrote:
"rdemyan" <rdemyan@xxxxxxxxxxx> wrote in
news:1167243172.045423.134430@xxxxxxxxxxxxxxxxxxxxxxxxxxx:

[]

b) In order to determine if there are record that were deleted,
the code has to check each table. I don't see how a filter can
be applied in the current setup. That means that the process
for over 100 tables is relatively long.

If you used a DELETED flag and left the deleted records in the
tables and just hid them from users in forms and reports and your
processing routines, then you wouldn't have to anything at all,
since the UPDATE process would propagate the DELETED flags.

I've created my own "sync" program (that I mentioned in the
earlier post) that launches with my app but is minimized with no
focus. Its purpose is to work in the background using a form timer
to check for update files in the Import DropBox for the server.
Preliminary tests are showing good results (the code is very
similar to the import code used to update a remote backend). But
it's rudimentary and it's thinking of all the ways people might
"break" it, that is probably where most of the effort is. You
noted that I should only allow one instance to run. I'll have to
research how to do that; I've never done that before.

Here's a suggestion:

Have a separate front end with linked tables to your shared
database. Have the synch program use that back end, instead of the
real shared back end. Then, before it runs, have it check to see who
is using that front end, and if anybody else is using it, prohibit
it from running. You might also do things like try to delete the LDB
file, which would mean that it's actually *not* in use. You can use
the ADO UserRoster for this, or the LDBUSR.DLL (look that up on
Microsoft's website, or on the Access Web, mvps.org/access).

Also, I'm thinking that when my app gets closed, I won't include
code to close the "sync" app. That way it'll be on the job longer
so to speak, probably until a user, on the LAN with the server,
shuts down their computer.

I'd have an administrative user just leave their machine on
overnight, or when they are not in the office.

Or, if you implement what I described above, you could have an
administrator and a backup admin whose computers both launch the
synch app, but only one at a time would be able to do synchs. If you
had your synch app check for the other user each time it attempts to
synch, a second one could take over if the first one was shut down.

Ah, yes, just thought of a different method: store the synch app on
the server, and use *its* LDB file to figure out if it's already in
use. That makes more sense. And you could make sure it launches
exclusively.

However, now I'm thinking about how to send out updates from the
server to the remote users. I think the way I will do this is,
when the "sync' program detects update files in the server Import
Drop Box, it will run the code to apply those updates to the
server backend. This already appears to be working fine.

I assumed that's what you were doing already?

After the import, code will be run in the "sync" program to create
an export update file from the changes applied to the server
backend. This export update file will be stored in the Export
DropBox on the server (as opposed to Import DropBox). This is the
server folder where my app used by remote users will look to find
new update files and if found, copy them to a local PC Import Drop
box for processing to update the local backend file. This too is
working.

I foresee an issue with this -- you'll never know for certain when
you can delete the files in the export dropbox.

However, creating this export update file from the server backend
file is where it gets a bit tricky I think. When this update file
is created from a LOCAL backend file, my app just logs the session
start for the app. If when closing the app records are found that
have a Last_Updated date field that is greater than the session
start date/time, then these records are written to the temp
updateDB that will be sent to the server's Import Dropbox.

Seems to me the import update process should create the export
database. That way, each import creates a new export. This would be
like a Jet replication generation number.

But for the server backend file this is different. There isn't a
session start time. I guess I'll have to use the last date that a
temp updateDB was created from the server backend as the
comparison date. This info can be stored in the backend file.

Ah, I see the issue. The problem is updates to the back end by LAN
users, which aren't done by imports. I'd use my synch program to run
an export on a schedule, then (basically the same way you'd do with
a replication hub and a production replica).

Again, though I'll have the same issue with deletes. Your
suggestion about using a delete flag in the tables also occurred
to me, but I dismissed it quickly. However, now that you raise
it, I should give it some consideration. It would have the
tremendous advantage of allowing the code to quickly find the
records that were "deleted" for inclusion in the temp updateDB
(essentially it would work the same as finding updates).

Yes. There's be no separate process required for deletions.

Now, if you want to really clean them out, you can keep track of
which users have processed the deletes, and when everyone's gotten
word on the deletion of a particular record, you could delete it
from the master. Obviously, you'd delete the records from the user
database as soon as they've been exported to synch with the master.

Whoops, no, that won't work, since the next update from the master
would include the deletes, unless the central database kept track of
which users had already deleted it. I guess if you've stamped the
identity of the user who deleted the record, you'd know who was
already deleted it and could ignore deletions that come back to the
user who deleted it already.

But that's pretty complicated.

I'd just go with the delete flag and not worry about ever purging
the actual records.

The downside, of course, is the code changes that will be required
to my app.

Here's where a tool like Speed Ferret can come in handy. It allows
you to do global search and replaces, so you could write a query
that filters out the deletes in a table, and then search and replace
for every place where the table is used and replace it with the
query.

Another alternative is to rename the base linked table and replace
it with a query named the same as the original table link that
filters out the deletes. This would mean you don't have to edit
everything.

Further I have to add a field to each table (actually I know
how to do this in code, so it's not really a big deal). But not
only does the delete code in the app have to be modified, but all
select code does as well. If it had been designed in at the
beginning, not much of a problem. But at this stage, this seems
like a tremendous amount of work.

Not with Speed Ferret. It costs about $100 and will pay for itself
the first time you use it. It's made by Black Moshannon Systems.

Having a human adminstrator apply the updates via your Access
code should be a huge improvement, no? That way you won't have to
worry about all the problems that come with trying to do your
updates automatically.

Huh? You don't mean that what we're doing now with all of the
manual updating and sending out of emails is better than what I'm
planning, do you?

No. All I'm saying is that automating the update part is an
improvement even if you do have to have a human being initiate some
parts of it on the central database.

The new ACCDB format does not support replication. Nor does it
support user-level security. However, you can still create MDB
files in A2K7 and replicate them and implement user-level
security. So, from my point of view, it's just about the same,
except for the fact that the new features of the ACCDB format are
not available when you use the "old" format.

Leave it to Microsoft. All the more reason I'm glad I'm making
the decision to handle my
own synchronization, despite the large effort required.

I don't see that there's really a problem, unless there's something
that breaks, as there are so many things. Here's a sobering
description of A2K7:

http://allenbrowne.com/Access2007.html

Especially bad is the situation with installing multiple versions of
Access on Vista with A2K7. Apparently there's a solution, but it's
not clear if it will always work or not --it's still very early.

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

Relevant Pages

  • Re: Homegrown synchronization
    ... certain tables directly in the backend that they are connected to. ... them from the server to the workstation. ... temp updateDB is set to 'Yes' that record will be deleted from the ... my app will contain a front-end table ...
    (microsoft.public.access.replication)
  • Re: Database design for a network
    ... of a power user writing an app for my own small company. ... PCs connected to a server. ... creating an mde version - I think the implication of some of the postings is ... backend is created per MDE backend module regardless of ...
    (microsoft.public.access.gettingstarted)
  • Homegrown synchronization
    ... Since my app is only used by a relatively small number of people and is ... there will be a backend on a common ... server that all workstations can connect to. ... to the temp updateDB only the records that were added/modified/deleted. ...
    (microsoft.public.access.replication)
  • Re: Synchronization Module
    ... The master copy is on my local server. ... So there is no backend at this point because I haven't been able ... to your question is that it is a database that has the data tables ... If you continue to synch your unsplit ...
    (microsoft.public.access.queries)
  • Re: Link Table Manager
    ... MS Access MVP ... >I have split an app into front end and backend with no problems. ... Server ... > suggestions would on how to link the new table into the existing app. ...
    (microsoft.public.access.tablesdbdesign)