 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Replication / June 2007Homegrown synchronization
I'm concerned about the lack of support by Microsoft for database replication and indirect synchronization. They don't even supply replication manager any more. In our tough IT environment, they don't allow unapproved third-party software (like TSI Synchronizer). Further IT has not approved the Jet Synchronizer for use and it currently cannot be installed. Even if they do eventually allow it, they may at some point in the future take it away. Also since the Replication Manager is now no longer provided with Office Professional 2003, I seriously doubt they will allow its installation even if they can get a copy. Although Jet direct synchronization is a possibility, a number of users are on WANs so corruption is possible (many would say inevitable). The institution I work with is at the forefront of network security. Since my app is only used by a relatively small number of people and is not mission critical, they could care less about our requirements. Who knows what they will do in the future. So, I'm thinking about creating my own indirect synchronization code. Although it will be major work, at least I will own it and nobody will be able to take it away. My thoughts are to do it differently than Jet and to not have any features regarding synchronization of table design or synchronization scheduling. So: 1) Scrap Jet Replication and Synchronization. This is not a problem since we've only been testing it for use. It was never implemented in the existing production environment. Start with an UNREPLICATED back-end file. Each workstation (including laptops) will have its own "local" backend. In addition, there will be a backend on a common server that all workstations can connect to. 2) All back-end tables currently have a time-stamp field which is modified if data in the record is changed. 3) Create a "model" database that contains all of the tables from the back end but is EMPTY of data. This "model" database will be copied to each user's workstation and be part of my application's files. When a user updates data on their local backend, code in my app will create a temporary database from this "model": a temporary updateDB (temp updateDB). The temp updateDB will contain all the tables from the backend, but will be initially empty of data. The code then will write to the temp updateDB only the records that were added/modified/deleted. The tables in the temp updateDB, however, will include an extra field used by "import" code to decide what to do with the records in a table in the temp updateDB. I think I can keep this as a Yes/No field. If 'Yes' then when data from the temp updateDB is imported into a backend, the record is deleted. If 'No' then the record is an update to an existing record or addition of a new record. How does my application know what data to write to the temp updateDB? Good question. My thinking right now is that I can just use the TimeStamp field in each table in the backend. For additions and updates, copy only records where the TimeStamp has today's date in it (or some such algorithm). Any deletions would probably need to immediately be written to the temp updateDB (since that record is removed from the backend tables). This is an area that needs more thought in order to keep it simple, but I think is definately doable. 4) My app will look to add records to the temp updateDB when the app closes (deletions would presumably already have been added). Once the temp updateDB is filled with the updated/modified/deleted data and the Yes/No flag set for each record, then the app will copy the temp updateDB from the local workstation to a common server location that all workstations can see. So, the idea is that the temp updateDB created by the app on local workstations should in the vast majority of cases be relatively small and easily/quickly copied to the common server folder. It will be named with the user's name and a date (down to the second). BTW: The reason for using a model DB is that it should be quicker to simply create an instance of a model database, rather than creating an entire temporary DB from code (besides I've got a lot of other coding to do!). I already do it this way for my normal temporary database that my app uses. In two years, this techniques has always worked as intended and never failed. 5) When my app starts up, it will look for temp updateDBs in a folder on the server. If it finds any that have not already been processed (based on prior logs in a frontend table), it will copy them from the server to the workstation. Then code in the app will transfer "update/add" data from each table in the temp updateDB(s), copied over from the server, to the equivalent local backend table. If the flag for the record in the table in the temp updateDB is set to 'Yes' that record will be deleted from the local backend. As noted, my app will contain a front-end table that will log the filename of each temp updateDB that was imported as well as the import date. So, I think this can work for updating workstations. It's an indirect synchronization scheme because the temp updateDB and the local back-end file are only opened locally for updating of the local back-end file. Scheduling is not needed. When the app opens it will look for temp updateDB(s) to IMPORT and when it closes it will look to EXPORT any table changes made by that user into a temp updateDB that will be copied to the server. Again synchronizing table design changes is not critical. There are other ways to handle this. If we change the table design, then we will do it to the most current copy we can find and run code to take all temp updateDB files on the server and import them. Most of these updates will likely fail due to key violations. My app launcher already looks for updates to my app located on the server and will download them to the workstation. It is a simple matter to also force downloading of fresh copies of the backend from the server. Further, in this scenario, even if the supposedly updated backend on the server does not truly have all current data, a new copy of my app will have an empty frontend table that logged previous imports of temp updateDBs. So it will try again to import temp updateDBs once it is on the workstation. Most if not all will fail due to key violations, but it should ensure that the new backend file is updated. HOWEVER, I still need to have a current copy of the backend on the server. My app launcher allows users to download a copy of the backend from the server (in case it ever gets corrupted,or if we've changed the table designs, or if they haven't logged on in 30 to 60 days). But I'm unclear on how to keep the copy of the backend on the server up-to-date. I guess I will need to have one or more of the workstations handle this. My app already has a user setup form that defines the group a user belongs to as well as what permissions they have to see data. Only administrators can add/delete users and set permissions. I could add a checkbox to that user setup form that sets whether their copy of my app can update the server backend. Only users that are within the LAN of the SERVER would be allowed to do this; no users connecting to the server via a WAN would be allowed to do this. When these users close my app, the code would not only update their local copy but also the server copy. An alternative would be to create a program that would schedule these updates. I already have a small program for scheduled backup and compaction of the server backend so I could add updating of the server backend tables to this program. It has a timer (IT won't allow us to use Windows Scheduler). This might actually be the best solution. Users on the LAN where the server is could just launch this small program, make sure the timer is set and then leave their machines on over night. Also "housekeeping" is an issue. At what point do we start getting rid of these temp updateDB files that are accumulating not only in the server folder but also in the workstation folders. Since the frontend will have a table that logs every temp updateDB filename and a date of import, any entries where the date of import is over 60 days old, for example, could be deleted from not only the table but also the folder. These are just my initial thoughts and I would appreciate comments, suggestions, etc. Thanks. > I'm concerned about the lack of support by Microsoft for database > replication and indirect synchronization. MS has never been good supporting Jet replication. I can only think that the reason they aren't trumpeting the existence of such an incredibly well-implemented capability in one of their products is because they think it will hurt sales of SQL Server. I think this is the only reason they've never created a Jet server component (like Filemaker has), because it would probably blow away SQL Server for applications with up to 255 users. I believe that the reason Jet replication is so good is because it was the dry run for implementing the final SQL Server merge replication. The development groups that worked on Jet 4 and replication and on SQL Server overlapped a great deal, if I understand correctly, and a lot of what went into Jet 4 replication also went into SQL Server (and heterogeneous replication between Jet and SQL Server). > They don't even supply > replication manager any more. This is a vast stupidity, yes. > In our tough IT environment, they don't > allow unapproved third-party software (like TSI Synchronizer). [quoted text clipped - 4 lines] > Professional 2003, I seriously doubt they will allow its > installation even if they can get a copy. Show them the Jet Replication security update. Even if you were doing direct replication, you'd need that, obviously, and that includes the synchronizers. So, it *is* supplied direct from Microsoft. > Although Jet direct synchronization is a possibility, a number > of users are on WANs so corruption is possible (many would say > inevitable). The loss of replicability is the main thing. That happens any time you compact a replica with any of its "possible corruption" flags set. > The institution I work with is at the forefront of network > security. Since my app is only used by a relatively small number [quoted text clipped - 5 lines] > differently than Jet and to not have any features regarding > synchronization of table design or synchronization scheduling. Sounds like a good plan at this point. > So: > [quoted text clipped - 4 lines] > will have its own "local" backend. In addition, there will be a > backend on a common server that all workstations can connect to. I still don't understand why LAN-connected machines shouldn't all be sharing a common back end stored on that LAN's server. > 2) All back-end tables currently have a time-stamp field which is > modified if data in the record is changed. [quoted text clipped - 8 lines] > empty of data. The code then will write to the temp updateDB only > the records that were added/modified/deleted. You won't be able to user Autonumber PKs unless you make them part of a compound key that identifies the source database. > The tables in the temp updateDB, however, will include an extra > field [quoted text clipped - 13 lines] > an area that needs more thought in order to keep it simple, but I > think is definately doable. I've done this already in a master/slave relationship. Here's what you do: 1. use the PKs to determine new additions. That is, do an outer join and find the records that don't exist on the other end. 2. use DAO to walk the recordset fields to find updated records. This is a matter of writing on-the-fly SQL that is something like this: UPDATE table1 INNER JOIN table1 ON table1.PK = table2.PK SET table1.field = table2.field2 WHERE table1.field <> table2.field2 Now, it's more complicated than that, as you have to account for Nulls. And you can't use the same WHERE clause for all data types. What I usually do is have a CASE SELECT on the field type that writes an appropriate WHERE clause. Then, to run it, you walk the fields collection of a recordset with the updatable fields, and then write the SQL for each field. This SQL is *very* efficient and runs very quickly, and it only updates the fields that have been changed. 3. deletes are harder, because you can't delete a record that isn't there. There are two approaches for this: A. use a DELETED flag and a DELETED field. The flag means "a user deleted this" and the DELETED field (a date/time field) says when it was deleted from the other replica. Now, the problem here is that you still need to get it around to all the other replicas, so your topology is going to matter a lot. You won't be able to synch with just any other database -- you'll only be able to have pairs of databases. I've never done this except in a master/slave relationship between two databases, so I didn't have to worry about that, and don't know what the ideal solution would be. B. do it like Jet replication and maintain a table of deletions. Then all you'd be doing is checking for additions to that table (just like in #1) and then processing the new records accordingly. This seems to me to be much easier than A) in a multi-database scenario like yours. But all of these are dependent on generating unique PKs in each database that are the permanent PKs. > 4) My app will look to add records to the temp updateDB when the > app closes (deletions would presumably already have been added). > Once the temp updateDB is filled with the updated/modified/deleted > data and the Yes/No flag set for each record, then the app will > copy the temp updateDB from the local workstation to a common > server location that all workstations can see. Your temp database is just your method of sending the data, so I guess that works. I've always had direct connections when I did this or used text files (the first time I implemented this was between an Access MDB and a MySQL database on a web server, the second was between an Access MDB and another MDB downloaded from a web site; notice a pattern? :). > So, the idea is that the temp updateDB created by the app on local > workstations should in the vast majority of cases be relatively [quoted text clipped - 4 lines] > rather than creating an entire temporary DB from code (besides > I've got a lot of other coding to do!). Tony Toews doesn't believe that to be true. He has code on his website to create temp databases on the fly and he says it's just as fast, because a Jet MDB created in code doensn't include anything but the bare-bones properties needed to store the data. I just don't want to spend the time writing and testing the code to create the temp file! So I use the method you're proposing, but it's not because of how long it takes to create the temp database from scratch. > I already do it this way for my normal temporary database that my > app > uses. In two years, this techniques has always worked as intended > and never failed. It's exactly what I use for temp files. > 5) When my app starts up, it will look for temp updateDBs in a > folder on the server. If it finds any that have not already been [quoted text clipped - 7 lines] > that will log the filename of each temp updateDB that was imported > as well as the import date. I would suggest you'll want to archive both the temp databases and the state of the real database before applying the changes from the temp databases. This is so you can easily roll back to a previous state if something goes wrong. You'll want to do all your synching wrapped in transactions, too. > So, I think this can work for updating workstations. It's an > indirect synchronization scheme because the temp updateDB and the > local back-end file are only opened locally for updating of the > local back-end file. It's a direct analog of how Jet indirect synch works with dropbox files. > Scheduling is not needed. What about between the servers? > When the app opens it will look for temp > updateDB(s) to IMPORT and when it closes it will look to EXPORT [quoted text clipped - 15 lines] > violations, but it should ensure that the new backend file is > updated. Sounds like there are pitfalls there, but the basic outline sounds about right. > HOWEVER, I still need to have a current copy of the backend on > the server. My app launcher allows users to download a copy of [quoted text clipped - 15 lines] > also the server copy. An alternative would be to create a program > that would schedule these updates. Yes, you'll have to schedule somehow. I know that it's possible to launch an Access MDB that runs code automatically, and this works when there's not a user logged on (I had a client whose daily compacts were done this way). > I already have a small > program for scheduled backup and compaction of the server backend > so I could add updating of the server backend tables to this > program. It has a timer (IT won't allow us to use Windows > Scheduler). a.sholes. You could write a VBScript to do the same thing, you know, using DAO. Would they let you schedule that? > This might > actually be the best solution. Users on the LAN where the server > is could just launch this small program, make sure the timer is > set and then leave their machines on over night. It could work, but I'd be worried about it -- it's too easy for it to get shut down and not run. > Also "housekeeping" is an issue. At what point do we start > getting rid of these temp updateDB files that are accumulating not [quoted text clipped - 3 lines] > import is over 60 days old, for example, could be deleted from not > only the table but also the folder. What about zipping them up? That gives you a lot more overhead? Also, you might want to consider not updating every time, and just updating when a certain number of changes have accumulated. > These are just my initial thoughts and I would appreciate > comments, suggestions, etc. It's a *very* hard task, and you're running into some of the same problems with IT, because you really need scheduling to remove as much latency as possible. But I think it's doable. But I don't envy you the task. If I were in your shoes, I'd be putting my energy into lobbying management into getting IT to approve use of indirect synchronization.  SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > > In our tough IT environment, they don't > > allow unapproved third-party software (like TSI Synchronizer). [quoted text clipped - 9 lines] > includes the synchronizers. So, it *is* supplied direct from > Microsoft. Are you saying that the synchronizer needed for indirect synchronization is already on the workstations??! Direct synchronization does work as I've already tested it and nothing was added in the way of executables, dlls, etc. > Sounds like a good plan at this point. Glad that someone with your experience agrees. I'd hate to put in the time and then find I'd hit a dead end or had neglected to consider a showstopper. > I've done this already in a master/slave relationship. Here's what > you do: > > 1. use the PKs to determine new additions. That is, do an outer join > and find the records that don't exist on the other end. I have very few tables that use autonumber PKs, but these could be a problem. I have one table where none of the human-enterable fields are unique, therefore there has to be an autonumber PK. But generally I use compound keys. I do not use Access to enforce referential integrity and do it all in code (Okay, I got tired before deployment and have about 5 out of 110 tables that do; but these are all small and it would be relatively easy to do the referential integrity in code). > 2. use DAO to walk the recordset fields to find updated records. > This is a matter of writing on-the-fly SQL that is something like [quoted text clipped - 14 lines] > SQL is *very* efficient and runs very quickly, and it only updates > the fields that have been changed. I had forgotten that I've already coded this for about ten tables or so. When I first started playing around with replication I was concerned about mass deletions bloating the replicated backend. This is because I have chosen to allow users to make backup copies of certain tables directly in the backend that they are connected to. That way if they screw up that table when editing/deleting, they can quickly restore the original (i.e. they don't have to restore the entire backend, just the table). But when they make a new backup, previously I was simply deleting all records in the backup table. Since some of my tables have 100000+ records, this really bloats replicated backends. Therefore, I had already implemented the code you suggest. Of course, now I have to do it for the other 100 tables :). The GUIDs caused me problems when implementing this code and I couldn't just use Select *. I had to explicitly write each field which was ugly with one table that has 70+ fields (yes, these are virtually all unique fields not contained in any other table). But now without the GUIDs this should be a whole lot easier. Still it's a lot of work. > 3. deletes are harder, because you can't delete a record that isn't > there. There are two approaches for this: [quoted text clipped - 14 lines] > accordingly. This seems to me to be much easier than A) in a > multi-database scenario like yours. Yes, the deletes are a problem and I'll study your two suggestions later. > But all of these are dependent on generating unique PKs in each > database that are the permanent PKs. > Your temp database is just your method of sending the data, so I > guess that works. Yes. I thought about a text file, but the coding will be substantially tougher (at least for me), than if I just use an Access database. There will be very, very few cases where that temp database will be greater than 2MB; most of the time I'm sure it will be less than 1MB after compacting (this is based on my experience with my current temp database which has about 65 tables. > > 5) When my app starts up, it will look for temp updateDBs in a > > folder on the server. If it finds any that have not already been [quoted text clipped - 14 lines] > > You'll want to do all your synching wrapped in transactions, too. Good idea on the transaction wrappers. I already have those in my app, so I've done it before. With regards to archiving, I think I'll do this only for the server copy. I'm going to beef up the small backup and compact application (intially provided by Candace Tripp and modified by me) to include handling the updating of the server backend file. For me that is the key backend file. If any of the workstation backend files blow up, it's not a real problem because the launcher is already setup to allow users to download a fresh copy of the backend file from the server if they need to. > What about between the servers? Yeah, this is where it gets trickier. > > When the app opens it will look for temp > > updateDB(s) to IMPORT and when it closes it will look to EXPORT [quoted text clipped - 51 lines] > > a.sholes. You got that right :) > You could write a VBScript to do the same thing, you know, using > DAO. Would they let you schedule that? [quoted text clipped - 6 lines] > It could work, but I'd be worried about it -- it's too easy for it > to get shut down and not run. Yeah, you're right. Or users could forget to run it. HOWEVER, it just occurred to me that I've already got programming in my app to send e-mails. What if I just copy and paste this email code into the backup/compact/update server backend app. When the update of the server backend file is complete, it will send an e-mail to the appropriate individual(s). E-mail received, everything went okay. No e-mail, then there were most likely problems. Of course the backup/compact/update app can be operated manually, so that's what the administrator would have to do if the timed update failed, for whatever reason. Fortunately, keeping the data up-to-date is not super critical most of the time. But there are certain times of the year where it is. > > Also "housekeeping" is an issue. At what point do we start > > getting rid of these temp updateDB files that are accumulating not [quoted text clipped - 3 lines] > > import is over 60 days old, for example, could be deleted from not > > only the table but also the folder. If I'm going to use the compact/backup/update app for the server backend, I should probably log which temp updateDB files were already imported. I don't think it is wise to store that in the compact/backup/update front end. Probably should store that information in the backend file so that it will be universally available to anyone who uses the compact/backup/update app (each administrator will need their own copy). Besides I might find it useful in the future to have a history of updates files applied to the server backend. Since I plan to include the user's logon names in the temp updateDB files, I can learn who is doing what and if they're doing what they are supposed to with regards to maintaining data. > What about zipping them up? That gives you a lot more overhead? > > Also, you might want to consider not updating every time, and just > updating when a certain number of changes have accumulated. Yeah, I could probably get away with that > > These are just my initial thoughts and I would appreciate > > comments, suggestions, etc. [quoted text clipped - 4 lines] > > But I don't envy you the task. Everything I've mentioned about IT is only getting worse. Most of the users are disillusioned and literally have to practice TM and chant to keep from going crazy with the slowness of the network connections. The response of anything dealing with the server in general is slow. For example, at my location, it takes 7 minutes to copy a 50 MB file from my hard drive to the server using Windows Explorer. At home with cable modem, I can download 50 MB in just over a minute. This by the way is why I provide users with a local copy of the backend. My app is mostly about complicated analyses on tens to hundreds of thousands of rows from multiple tables with multiple criteria provided by users. Some take several minutes to run even with the backend file on the local PC. A comment I get from many users is that they like the speed of the app (it's only because the backend is on the local PC). They are used to applications (MAXIMO, etc) where they have to wait and wait and wait to accomplish their work. I wanted my app to be used [since it is really cool :)], so I decided I could forgo best practice and not make everyone deal with an ultraslow backend on a LAN server (the fact that we're not constantly updating data makes this feasible, IMHO). Of course, if they want to, users can connect to the backend file on the server. My launcher makes this very easy. Just click a radio button and the server location is input into a textbox and that's where my app will go to find the backend file. But no one does that, because they prefer the speed. However, that means that the responsibility is then on me to do what I can to make sure they have current data. At this point it is really about me maintaining control. I would hate to implement Jet indirect synchronization and then have them yank the synchronizer in a year or two causing my application to fail when it comes to updating replicas. No, this will be better. Also, I don't care for the bloat and size of replicated backends. My backend went from 130 MB to close to 300MB after it was replicated and after multiple compactions. One thing I didn't consider yet is conflicts. I don't expect this to be a huge problem because data entry personnel are only allowed to update data for their site. There are four sites and while everyone can see data from all four sites, they cannot update somebody else's site data even though they may have the rights to update their own site data. This will assist in minimizing data conflicts. Further, not everyone can update data. Only about 1 out of three users has been given permissions to update data; the rest can only view and run analyses. But conflicts still can and probably will happen, and we won't really know about them until probably well after the fact. Right now it looks like the last temp updateDB file to update a backend is the winner if there is a conflict. If a new record's PK already exists, then the fields will simply be updated instead of a new record added. If a row has already been deleted, well then I guess in that case, the first deletion won. I don't think it is going to be a show stopper. I'm finding that my users don't really want to update the data, they just want to use the data for their analyses and view data they need for their work. Most want somebody else to do it. > -- > David W. Fenton http://www.dfenton.com/ > usenet at dfenton dot com http://www.dfenton.com/DFA/ > > 2. use DAO to walk the recordset fields to find updated records. > > This is a matter of writing on-the-fly SQL that is something like [quoted text clipped - 4 lines] > > SET table1.field = table2.field2 > > WHERE table1.field <> table2.field2 For importing from the temp updateDB, I know which databases to use to get table1 (currently connected backend file) and table2 (temp updateDB). But for exporting, this is not clear. The code will have created the temp updateDB but it will be empty. I think I'm going to need to make a copy of the backend file when the app opens. It will essentially be a snapshot of the backend prior to any updates/edits/deletes. Copying files on the local PC can be fast (75 MB on my machine takes about 10 seconds). Then I need to do the join and update the temp updateDB. I've never written code like that before but I guess if I just change the Update statement above to: UPDATE table3..... table1 = local backend file table table2 = copy of local backend file table created when app was first opened during the session table2 = temp updateDB table There's probably more clever ways to do this, but this should work, although speed is important. Well, I've been playing around with the SQL for synchronizing EXISTING records in two tables. Eventually, I'm sure I can get it to work in a TableDef loop, but I can see now that this is going to be really, really slow (looping through over 100 tables). And I'm just talking about writing the updated and deleted records between my app and a temp updateDB on the SAME workstation (no network connection). The largest table (130,000 rows) takes 40 seconds alone. Add in 99 more tables (okay they're smaller), but this could probably take at least 10 minutes. And it doesn't really matter whether there have been any changes to the tables or not. OTOH, Jet direct synchronization takes only a few seconds when both databases are on the same machine. A speedier although time-consuming-to-implement method is: when my code makes an add/edit/delete to a table, at that point write the record to the temp updateDB database. There would essentially be no effect on speed in this case. The hard part would be modifying all of the forms where updates occur to also include writing to an external database. I'm coming to the conclusion that there just aren't any good alternatives given the existing IT environment that I have to work in. Maybe what I should do is stick with replication and direct synchronization and implement as many processes as possible to protect the backend replica on the server (the main backend). Alert the user when a database has lost it's replication (so they can create another replica for local use). Creating daily backups of the server replica will probably be required. If the server replica has a problem, then it can be deleted, removed from the replica set and replaced by a replica from the most recent "backup" replica (note: MakeReplica will be used at all times, no file copying). Hypothetical: Hub backend replica in a star configuration is on the server at SiteA (server replica). User1 is at SiteB connected to SiteA by a WAN. User1 tries to direct synchronize his remote replica with the server replica but an error message comes back saying that synchronization cannot be accomplished because the server replica is corrupted or no longer replicated (I hope there is such a message available). Can User1 at SiteB safely initiate the replication of a new server replica at SiteA from an existing "backup" replica also located at SiteA. Or is this dangerous because of the WAN connection? > > > 2. use DAO to walk the recordset fields to find updated records. > > > This is a matter of writing on-the-fly SQL that is something like [quoted text clipped - 27 lines] > There's probably more clever ways to do this, but this should work, > although speed is important. > Well, I've been playing around with the SQL for synchronizing > EXISTING records in two tables. Eventually, I'm sure I can get it [quoted text clipped - 8 lines] > not. OTOH, Jet direct synchronization takes only a few seconds > when both databases are on the same machine. Are you filtering your records based on the date stamps for updates? You could do it one of two ways: 1. record the last synch date and filter on that. 2. process only the records where the date stamp doesn't match. Processing the actual updates should be fast, as it's running a SQL statement for each field. I've done this on apps with 40 or so tables, and it was quite fast, though the number of records was not as large as yours. Are you sure you've code it right? > A speedier although time-consuming-to-implement method is: when my > code makes an add/edit/delete to a table, at that point write the > record to the temp updateDB database. There would essentially be > no effect on speed in this case. The hard part would be modifying > all of the forms where updates occur to also include writing to an > external database. Why duplicate data? What would you do when there are multiple updates to a single record? > I'm coming to the conclusion that there just aren't any good > alternatives given the existing IT environment that I have to work > in. That's basically what I said on the front end. Did you say earlier why Windows Terminal Server was out of the question? That would give complete IT centralized control and wouldn't require anything but Access and a little disk space for the front ends for each user. > Maybe what I should do is stick with replication and direct > synchronization and implement as many processes as possible to > protect the backend replica on the server (the main backend). > Alert the user when a database has lost it's replication (so they > can create another replica for local use). You're missing a key point. It's much more likely to be the server replica that loses replicability than the user replica, since the user will be initiating the synch across the wire to the server, and it's never the local replica that gets corrupted. > Creating daily backups of the server replica > will probably be required. Well, also, doing your remote synchs with a buffer replica that is distinct from the actual main replica of record on the server would help, too. That's basically the smallest possible replica farm (two replicas). > If the server replica has a problem, then > it can be deleted, removed from the replica set and replaced by a > replica from the most recent "backup" replica (note: MakeReplica > will be used at all times, no file copying). A replica farm would be preferable. > Hypothetical: Hub backend replica in a star configuration is on > the server at SiteA (server replica). User1 is at SiteB connected [quoted text clipped - 3 lines] > server replica is corrupted or no longer replicated (I hope there > is such a message available). I still don't understand why you've rejected the multiple star topology, with hubs on each of the servers on each of the LANs, with all users synching directly with the local server's hub replica (perfectly safe on a local LAN), and then using indirect synch to keep the server replicas synchronized with each other. This would be a two-level star, with a central server at the hub of a small star of servers, synching indirectly with the central server, and then a secondary star with each server as the center, with all synchs from the spokes to the center being direct. > Can User1 at SiteB safely initiate the replication of a new server > replica at SiteA from an existing "backup" replica also located at > SiteA. Or is this dangerous because of the WAN connection? It's very dangerous, because the local CPU will be doing the processing to create the new replica. It's more dangerous than any other direct synch operation you've contemplated. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ Your suggestion about applying the filter put the spunk back in me :) I'm making good progress. I have the code setup to create and fill the temp updateDB and it seems to be working well. Took me quite some time to make sure that all of my tables were set up correctly (no autonumber PKs, all have TIME_STAMP field) and to make sure that all my code always set the TIME_STAMP when a change is made to the data tables. The 'checking for delete' process is what is taking the most execution time. With the TIME_STAMP filter for checking for additions and updates, this part of the code takes as little as 2 seconds to execute for 107 tables. But the delete process can't use this technique because the record is missing. I'm creating a "snapshot" backend when the app opens (i.e. just filecopy of the local backend). Then when the app closes, the delete process looks for non-matching records in the snapshot backend versus the backend that the app is linked to. This takes about 50 seconds at a minimum. However, when I then deleted 130,000 rows from a table it only took an extra 7 seconds. What's taking so long is that I'm using generic code. The tables are looped through and then the index is looped through to get the PK. This dynamic creation of the SQL is what appears to be taking the lion's share of the time. However, it beats the alternative of coding for 107 tables and specifying each field name explicitly. I don't even know if that would save any execution time and I'm not inclined to find out. It would seem that the only way to trim this execution time down would be to write the record(s) to the temp updateDB table when the record(s) are actually deleted. This might actually be a possibility, since with virtually all table deletes, I use an action query like strSQLDELETE = "DELETE *......". Of course, I'm sure that there are probably a few exceptions, but still I'd be able to find the vast majority very quickly and then simply copy and paste the code and add a database qualifier to the external table. I'm going to look at this since then I wouldn't have to check for deleted records upon creating the temp updateDB which would save at least 50 seconds of time. > > Well, I've been playing around with the SQL for synchronizing > > EXISTING records in two tables. Eventually, I'm sure I can get it [quoted text clipped - 101 lines] > David W. Fenton http://www.dfenton.com/ > usenet at dfenton dot com http://www.dfenton.com/DFA/ The code for importing of an update file is working and, as you noted earlier, it is very fast (as expected). There are still some housekeeping issues I need to consider and I've started on them, but I'll save the details for another post. **Let me add a bit more about how I'm currently handling the deletion of records issue.** 1) When the app opens on the remote PC it makes a "snapshot" copy of the local backend file that the user is connected to. 2) When the app is closed, the code that EXPORTS changes to the temp updateDB (the DB where updated records are stored and which will be copied to the server) looks for records in the local backend file that the user is connected to that are not contained in the "snapshot" copy. If it finds any records these are written to the appropriate table in the temp updateDB. There is a flag in each table in the temp updateDB that indicates to code that IMPORTS data from the temp updateDB whether that data is to be added/edited (flag = false) or deleted (flag = true). For deletions this flag is set to true. 3) There are several issues with this method: a) A copy of the backend file has to be created. It is true that the copy is made from a local PC file to another local PC file, so the copying process is fairly fast. 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. *************How do I apply updates to the server backend***************************** This is the main last piece that I need to deal with. If I understand Jet indirect synchronization correctly, the synchronizer on the server is running in the background looking for updates in the server dropbox. When it finds one, it will process it. Well in my homegrown synchronization scheme, I'll use the same method, namely send the update file created by a remote user upon closing my app to the server ImportDropBox. But the real question is how do those updates in the ImportDropBox for the server get applied. There is no program running in the background on the server like the Jet Synchronizer. Window's scheduler is not available to us to launch an Access program that could maybe handle this (although I'm not completely sure how). In one form or another, this has to somehow be user initiated. My current plan is: 1) Only users that are connected to the server via a LAN can initiate the updating of the server backend (no WAN users). If I keep the code for this in my app, this is easy to do since I have a user setup screen which administrators use to set permissions to view data. This would just be one more permission. 2) Should I use a hidden form in my app with a timer that monitors if new update files have appeared in the server ImportDropBox. Then the user could be alerted to apply the update or this could be done automatically. I could get away with this because in general we don't update data very often. The code would of course check if any of the update files in the dropbox had already been imported (each import is logged to a table). A better alternative might be to launch another program at the same time myapp is launched. This second program would be the one monitoring the server ImportDropBox and would automatically apply updates. But right now, I'm not sure if a timer on a form within in application that does not have the focus, works within Access. I need to research this. But either method requires that someone is logged in. Even if I use a separate app to accomplish the server synchronization, it is always going to require that someone is logged into their computer at a minimum. But let's keep things in perspective and look at how we're doing it now (BTW: the app is still in the deployment stage so we are still working out coding and logistic bugs and adding customization; but I need to figure this "synchronization" issue out and deploy it soon). Updates are sent to a central person (typically by spreadsheet or e-mail) and that person has to manually update the server backend. Then after the server backend is updated an e-mail is sent out and remote users have to download the updated backend from the server. So from that perspective, this is a large improvement. Further, since there are multiple users using the app that are also connected to the LAN where the server resides, each one would be given permissions to update the server backend so there would be some redundancy. There will still be latency, but it will be much shorter than it is now. Of course, Jet indirect would be better yet (but IT won't yet allow it). OTOH, one of my other concerns is that MS will abandon Jet replication as they currently seem to be on that path (I don't know how well it is supported in A 2007). Some thoughts, suggestion, before I embark on this coding effort. Thanks. > Your suggestion about applying the filter put the spunk back in me :) > [quoted text clipped - 141 lines] > > David W. Fenton http://www.dfenton.com/ > > usenet at dfenton dot com http://www.dfenton.com/DFA/ [] > 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. > *************How do I apply updates to the server > backend***************************** [] > But the real question is how do those updates in the ImportDropBox > for the server get applied. There is no program running in the > background on the server like the Jet Synchronizer. Window's > scheduler is not available to us to launch an Access program that > could maybe handle this (although I'm not completely sure how). As I told you, you're just moving the problems around by hand-coding this, as you still need capabilities that your brain-dead IT people are prohibiting you from using. I really think you need to go to management on this and explain that IT is being too restrictive and it's costing extra money and effort in your work. > In one form or another, > this has to somehow be user initiated. [quoted text clipped - 14 lines] > check if any of the update files in the dropbox had already been > imported (each import is logged to a table). I think the way I'd do this in your situation is: 1. have LAN users run the update process on the server when they upload their own updates to the server (assuming they are using the same methods as the remote users, which may be wrong -- the LAN users may be updating the server back end directly). 2. on a workstation that stays on all the time, run an Access app that has one form with a timer that simply runs the update code on the server at a specified interval. Or, that looks in the dropbox every N minutes and processes any files there. Running it in a Locked Workstation logon on a workstation removes the IT restrictions. On WinXP, it could be just a "switch user" kind of thing, and as long as the machine is left on, it will just run. > A better alternative might be to launch another program at the > same time myapp is launched. This second program would be the one > monitoring the server ImportDropBox and would automatically apply > updates. But right now, I'm not sure if a timer on a form within > in application that does not have the focus, works within Access. > I need to research this. You wouldn't want but one instance of this update program, though. > But either method requires that someone is logged in. Even if I > use a separate app to accomplish the server synchronization, it is > always going to require that someone is logged into their computer > at a minimum. Yes. This is where the IT restrictions end up being just exactly as restrictive for your hand-built synch as they are for Jet replication. What i described above is *exactly* equivalent to running your synchronizer on a workstation. > But let's keep things in perspective and look at how we're doing > it now (BTW: the app is still in the deployment stage so we are [quoted text clipped - 5 lines] > is updated an e-mail is sent out and remote users have to download > the updated backend from the server. 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. > So from that perspective, this is a large improvement. Further, > since there are multiple users using the app that are also [quoted text clipped - 5 lines] > that MS will abandon Jet replication as they currently seem to be > on that path (I don't know how well it is supported in A 2007). 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. I don't think that's much of an issue for replication, as you can surely use an ACCDB for your front end and link to a replicated back end. Thus you get most of the new features of the ACCDB (which are almost all UI-related and not data-related, with the exception of the multi-value fields, which I don't believe I'd use, in any case). SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > [] > [quoted text clipped - 7 lines] > 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. 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. 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. 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. 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. 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. 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). The downside, of course, is the code changes that will be required to my app. 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. > 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? > 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. Hope you had a nice holiday! > -- > David W. Fenton http://www.dfenton.com/ > usenet at dfenton dot com http://www.dfenton.com/DFA/ >> [] >> [quoted text clipped - 18 lines] > 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 [quoted text clipped - 4 lines] > 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 [quoted text clipped - 3 lines] > 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 [quoted text clipped - 3 lines] > 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 [quoted text clipped - 4 lines] > 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 [quoted text clipped - 6 lines] > 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. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ . > Here's a suggestion: > [quoted text clipped - 6 lines] > the ADO UserRoster for this, or the LDBUSR.DLL (look that up on > Microsoft's website, or on the Access Web, mvps.org/access). You totally lost me on this one. By a separate front end, you don't mean my main application, do you? Are you suggesting that the sync program ("front end"?) I wrote should link to the shared backend file on the server? > 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. Store the sync app on the server.... hmmm...... So the idea is that if it is on the server, I could set the option to have it open exclusively. Is your concern that if two users have the synch app open on their machines and both synch apps try to process an update that they find, that that will cause a problem. Frankly, I hadn't considered that! > I foresee an issue with this -- you'll never know for certain when > you can delete the files in the export dropbox. Yes, clean up of these temp updateDBs will be an issue. How does Jet indirect synchronization handle this? It's sending message files, isn't it? Couldn't I just copy the technique used there? > 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. Yes this is my current plan. When the sync app processes an import to the shared backend, it then creates a temp updateDB based on the shared backend for EXPORT to the remote users. > 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). Actually updates by LAN users is an issue that occurred to me after I posted previously, and I've been wondering how to handle that. Scheduling has its advantages, but it could also mean a lot of updating of remote user apps that are unnecessary. On the other hand, if there is virtually no data in the temp updateDB sent to the remote users, then that import will only take a few seconds, which would not be noticeable since my app takes about 15 to 20 seconds to load anyway. But the file copy from the server to the remote machine will probably take longer. Originally I had thought that the model for the temp updateDB would be relatively small. It's actually 2.5 MB when it is completely empty of data. So, it's at least 2.5 times larger than I thought. Copying it to remote machines over a WAN might take a little time (but is probably not worth worrying about). > Yes. There's be no separate process required for deletions. > [quoted text clipped - 16 lines] > filters out the deletes. This would mean you don't have to edit > everything. Yes, I thought of this too because I already have RWOP queries. At first, I thought I could just modify the RWOP queries, but this won't work because the users can't get at the tables directly with the security. The only way they can get at the DeleteFlag is through the RWOP queries. So, I'd have to rename the existing RWOP queries with a prefix and then create new queries with the old names of the RWOP queries and adding "WHERE DeleteFlag = False). This will add another layer of queries (pushing it up to about 300 total in the main app). If I implement the delete flag system, I will do this though, as there is no way I can go through all of my code to find all of the select statements, etc and modify them. Speed Ferret could help with this as well?? > > 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 [quoted text clipped - 5 lines] > 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. I'm going to buy this anyway. I've heard good things about it. I looked at Speed Ferret. The cost is actually just under $200. Further, it won't work unless Access 2002 is also installed. My machine currently only has A2003 on it. I looked on their website and there is mention of a forthcoming upgrade that will work with A2003. However, the date of that notice is 2004???! Since nothing has happened yet, it makes one wonder if they are still interested in improving the product. An alternative is Find and Replace from Rick Fisher Consulting. Cost is $37 and I've been playing around with the demo copy. It could do the trick although I wish it had the capability to Find and Replace on one string subject to another string or something similar. I've ordered F&R. I'm gonna bite the bullet and implement setting the IsDeletedFlag method for "deleting" records in tables. It'll probably require a full day. I only wish I didn't have to add another 110 queries, but that's the way it is. In the end, I'll probably be happy that I spent the time to do this. It'll certainly make my "homegrown synchronization" a lot simpler. > . > > [quoted text clipped - 106 lines] > > > I'm going to buy this anyway. I've heard good things about it. Well, the switchover to a different table record delete logic is not going as well as I had hoped (from a time perspective). It took longer to set up the tables and queries than I thought because I also took the opportunity to clean up a lot of objects that were no longer being used. Okay, that's fine. However, it is the code that is the major problem. Almost all of my add/edit/deletes are done in code. Most of the add/edits using DAO recordsets and deletes using Action queries. What I'm just now realizing is that this is a more complex process than I realized. Why? Because you have to take into account that when a record gets deleted, a user may try to recreate it. They don't know that the record really wasn't deleted but instead that the IsDeletedFlag was set to true. So they try to recreate the record and get a duplicate primary key error unless my code accounts for that (which it currently does not). Maybe I should have coded the additions/edits differently but it's too late now. It's also too late to abandon implementing the new delete logic. I'm posting this because if anyone is following along on this thread, this is something you need to consider for your situation. > However, it is the code that is the major problem. Almost all of > my add/edit/deletes are done in code. Most of the add/edits using > DAO recordsets and deletes using Action queries. Doesn't F&$ search code? Speed Ferret does. > What I'm just now realizing is that this is a more complex process > than I realized. Why? Because you have to take into account that [quoted text clipped - 3 lines] > record and get a duplicate primary key error unless my code > accounts for that (which it currently does not). Bummer. Why are you re-using PKs? I'd never do that, myself. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ Well, the switchover to a different table record delete logic is not going as well as I had hoped (from a time perspective). It took longer to set up the tables and queries than I thought because I also took the opportunity to clean up a lot of objects that were no longer being used. Okay, that's fine. However, it is the code that is the major problem. Almost all of my add/edit/deletes are done in code. Most of the add/edits using DAO recordsets and deletes using Action queries. What I'm just now realizing is that this is a more complex process than I realized. Why? Because you have to take into account that when a record gets deleted, a user may try to recreate it. They don't know that the record really wasn't deleted but instead that the IsDeletedFlag was set to true. So they try to recreate the record and get a duplicate primary key error unless my code accounts for that (which it currently does not). Maybe I should have coded the additions/edits differently but it's too late now. It's also too late to abandon implementing the new delete logic. I'm posting this because if anyone is following along on this thread, this is something you need to consider for your situation. The continuing saga: After trying to fix the code to use an IsdeletedFlag instead of actually deleting records, I've abandoned the effort. I'm reverting to last night's backups. The changes required are much, much larger than I ever imagined. I'm now concerned that I will have to go back and retest all that functionality that took weeks to test in the past and currently works. So, I'll just have to contine with my homegrown sync plan dealing with deleted records in a different way. > Well, the switchover to a different table record delete logic is not > going as well as I had hoped (from a time perspective). It took longer [quoted text clipped - 20 lines] > I'm posting this because if anyone is following along on this thread, > this is something you need to consider for your situation. > So, I'll just have to contine with my homegrown sync plan dealing > with deleted records in a different way. I would say that the best way would be to copy Jet replication's method. Create a tombstones table that has a field that says which table a record was deleted from, the date of deletion and the PK. Then you'll join each table to the PK field of the tombstones table, filter on the table name (and, perhaps, the deletion date) and then you'll know which records to delete. However, you're going to run into *exactly* the same problem as with the delete flag because you're re-using PKs. I can't think of any method for deleting records where that will not be a problem. I think you have to re-engineer your app to never re-use PKs. A random autonumber is a very good candidate for that. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > I looked at Speed Ferret. The cost is actually just under $200. Huh. It's been $99 for years and years. It would have still been worth it the first time I bought it and used it -- it would have paid for itself in saved time on the first use. > Further, it won't work unless Access 2002 is also installed. My > machine currently only has A2003 on it. I looked on their website > and there is mention of a forthcoming upgrade that will work with > A2003. However, the date of that notice is 2004???! Since nothing > has happened yet, it makes one wonder if they are still interested > in improving the product. That's a concern. I'd write to them. Do you have A2K? I do all my programming in A2K for distribution on later versions of Access, so I'd have no issues with it myself. > An alternative is Find and Replace from Rick Fisher Consulting. > Cost is $37 and I've been playing around with the demo copy. I've heard good things about it, but it's not as versatile and feature-rich as Speed Ferret. > It could do > the trick although I wish it had the capability to Find and [quoted text clipped - 9 lines] > this. It'll certainly make my "homegrown synchronization" a lot > simpler. I think so, too. All your alternatives would have taken a lot longer to implement and would be subject to a much larger degree of error in the implementation. This is a simple change that is just time-consuming. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ >> Here's a suggestion: >> [quoted text clipped - 12 lines] > the sync program ("front end"?) I wrote should link to the shared > backend file on the server? Know. I'm suggesting that instead of using the back end directly, your synch program could use an intermediary MDB file with linked tables and the only purpose of this intermediary MDB file would be to provide you with an LDB file that would allow you to restrict to one user (something you can't do when using the real back end). >> 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 [quoted text clipped - 4 lines] > that if it is on the server, I could set the option to have it > open exclusively. No. That's not an MDB option, but a program option. And I wouldn't open it exclusively, because then you lose the possibility of having two users running it as backup. Instead, I'd allow it to be opened by more than one user, but only one user at a time allowed to actually run the synchs. This would mean you'd have to have your synch timer run code to check how many users have it open. If User1 opens it, he'll be running the synchs. If User2 then opens it, her copy will then wait until User1 logs out before it will ever run any synchs. > Is your concern that if two users have the synch app open > on their machines and both synch apps try to process an update > that they find, that that will cause a problem. Frankly, I hadn't > considered that! Yes, of course it will cause problems. From my point of view you want: 1. only one user at a time running the synchs. 2. guarantee that the synchs will run. To get #2, you probably need more than one user running the synch program at a time, so that if somebody shuts down her PC, there will still be a copy of the synch program running. So, to get #2, you have to have #1 written in a way that it checks constantly if it's the only user and only starts running the synchs when it is. >> I foresee an issue with this -- you'll never know for certain >> when you can delete the files in the export dropbox. > > Yes, clean up of these temp updateDBs will be an issue. How does > Jet indirect synchronization handle this? It's sending message > files, isn't it? Couldn't I just copy the technique used there? It's sending a different set of message files for every replica pair. My understanding was that you'd be creating a single file for each "generation" and multiple remote databases would process it. If you do it like Jet, then you could safely have the remote database delete the message file as soon as it successfully closes the transaction in which the synch operations were performed. >> Seems to me the import update process should create the export >> database. That way, each import creates a new export. This would [quoted text clipped - 3 lines] > import to the shared backend, it then creates a temp updateDB > based on the shared backend for EXPORT to the remote users. If you do it this way, rather than creating a message database for every remote database, you'll have to keep track of which remote databases have processed the message database. When all of them have done so, you can archive it. >> 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 [quoted text clipped - 15 lines] > remote machines over a WAN might take a little time (but is > probably not worth worrying about). That's where zipping comes in. If you zip it up, it's like to be 1/4 the size or less. That can very easily be automated. >> Yes. There's be no separate process required for deletions. >> [quoted text clipped - 31 lines] > select statements, etc and modify them. Speed Ferret could help > with this as well?? Absolutely. It's a search and replace program. I'm afraid you'd still have to run the search/replace for each table, though. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > That's where zipping comes in. If you zip it up, it's like to be 1/4 > the size or less. That can very easily be automated. Never occurred to me to do this. I checked and if I zip this 2.5 to 3 MB file in Windows Explorer it goes down to about 120 KB! That's excellent, and I will implement it if I can (IT may be the issue). Currently, IT has code that executes to remove any zip files attached to e-mails received by users. Since everyone is still using Windows 2000, I don't believe that there is a Zip program like with Windows XP. But I think everybody does have WinZip. Although I've never used it in that IT environment I do know that people trick the e-mail stripper by simply replacing .zip with something else. Then when a user receives that file they rename it (back to .zip) and unzip it. That means that they must have some type of utility program installed (probably WinZip). I'll be searching for code to do this. I heard rumors that IT will be rolling out XP professional in the near future. I would imagine that they will use the native XP zip program and abandon the 3rd party program currently in use. So, I'll need to include code that looks for both programs: if it can't find one, it looks for the other. Thanks! > I'll be searching for code to do this. Check out http://www.frez.co.uk/freecode.htm, which refers to the ZLib DLL that you can download for free and distribute with your app for free. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > >> Here's a suggestion: > >> [quoted text clipped - 18 lines] > to provide you with an LDB file that would allow you to restrict to > one user (something you can't do when using the real back end). I'm not clear on why I wouldn't have the synch program link to the shared backend. I currently use the Jet UserRoster in my main app. So I could use it there. I believe I am correct in saying that a .ldb file is not created for the backend until someone actually uses data from the backend. So an app just sitting there linked to the backend doesn't cause a .ldb file to be created for the backend. So if I link the synch program directly to the backend and am careful in my code to make sure that all recordsets are closed and set to Nothing, then why wouldn't this work. Before the code applies any updates, it would first check to see via the Jet UserRoster if anyone else is "logged in" to the backend. If so, update will not be applied. When the other user finishes, then the update is applied. After an update is applied, a record is written to a table in the backend with the details of that update file. When the synch program timer fires it checks what files are in the dropbox and which files have been imported. If already imported, the import code is not run. This seems like it should work. >> >> Here's a suggestion: >> >> [quoted text clipped - 22 lines] > I'm not clear on why I wouldn't have the synch program link to the > shared backend. Because that provides you a way to monitor and limit the number of users running the synch program. If you link directly to the back end, you can't distinguish your regular users from the user of the synch app. > I currently use the Jet UserRoster in my main app. So > I could use it there. I believe I am correct in saying that a .ldb > file is not created for the backend until someone actually uses > data from the backend. Not entirely. A back end file will not have an LDB just because someone opens a front end with tables linked to it. But all it takes is the initialization of a DAO database variable to create the LDB file. Opening a form bound to a table will also create the LDB. The point is that if you open an MDB, the LDB is created immediately. Opening a front end creates an LDB for the front end, but not for the back end. So, you could either use the intermediary front end or run the synch MDB in shared mode (I'm not certain if this would be risky or not). > So an app just sitting there linked to the backend > doesn't cause a .ldb file to be created for the backend. We're not talking about the back end's LDB, but any LDB that could be used to keep track of who is logged on. It could be the LDB for your synch app or the LDB for the intermediary front end I suggested (which is not really a front end -- it's just a shell MDB with table links in it, used for no purpose except to track who has it open). > So if I link the synch program directly to the backend and am > careful in my code to make sure that all recordsets are closed and [quoted text clipped - 3 lines] > update will not be applied. > When the other user finishes, then the update is applied. But how can you then apply synchs when the regular users are editing? Or is that what you want to avoid? I thought we were talking about how to prevent multiple users from running the synch program simultaneously. > After an update is applied, a record is written to a table in the > backend with the details of that update file. When the synch [quoted text clipped - 3 lines] > > This seems like it should work. I don't think we're any longer talking about the same thing at all. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ No, it looks like we're not talking about the same thing. Sorry to be so dense. But I'm still not following you. Let me state a few things first to see if we are on the same page. 1) Updates will be applied to the shared production backend directly, or should it be a copy? 2) If the production backend, then the concern is that the code in the synch app may be updating a record in a production backend table that a user connected to the production backend is trying to update at the same time. 3) Synch app is on each each user's local PC? 4) Intermediary app is where? On each user's local PC as well or in the folder on the server where the shared production backend (or copy) is. If we're trying to restrict the intermediary app to one user, then wouldn't it have to be on the server. There's no way to control this if a copy of the intermediary app exists on each user's intermediary machine. 5) My plan was to launch the synch app when my main app launches; but only for users who are given permission to do so (this would be set in my main app when a user is setup to use the main app). The reason for this is I want to restrict who can launch a synch app to only those individuals who are connected to the LAN where the shared production backend is. Sure I could not distribute the Synch app to the WAN users, but my plan seems safer since users could still get a copy off the server. This way I don't need to add additional instructions for new users (the more instructions, the more confusion, the more calls......). I guess I just don't see how having the intermediary app tells me anything about users who are logged in directly to the production backend. I thought that the way to determine this was to see if there is a .ldb file for the backend file and/or use the Jet User Roster. Sorry, but I'm just not following you so I'll stop my post here rather than ramble on with a false set of assumptions or understanding. > >> >> Here's a suggestion: > >> >> [quoted text clipped - 79 lines] > David W. Fenton http://www.dfenton.com/ > usenet at dfenton dot com http://www.dfenton.com/DFA/ > No, it looks like we're not talking about the same thing. > [quoted text clipped - 3 lines] > 1) Updates will be applied to the shared production backend > directly, or should it be a copy? You have to apply them at some point, so, yes, you have to apply them to the production back end. Moving it to a copy just adds latency to the time it takes for updates to move in both directions, from the LAN users to the remote users. > 2) If the production backend, then the concern is that the code in > the synch app may be updating a record in a production backend > table that a user connected to the production backend is trying to > update at the same time. Yes, but that should be blocked, and all you need is code that handles that kind of error. > 3) Synch app is on each each user's local PC? Er, I don't think so, unless you're now defining the synch app differently than I understood you to be defining it a few days ago. I thought the synch app was designed to do the scheduled synchs, looking in the inbox on the server and processing the files dropped there by the remote users (and, I presume, doing the same thing in the outbound direction). Now, each remote user has to have functionality to process it's updates (both incoming and outgoing), so I guess that's the same application, yes. But one copy of that app, it seems to me, has to be differently designed than the others, and that's the one that is managing the production database. > 4) Intermediary app is where? That whole issue only applies to the one copy of the synch app that is handling the production database. The whole point is that your synch app is working like the Jet replication synchronizer -- it "manages" a database, and that database is the production database, so it has to work somewhat differently than the synch apps managing the other databses, which are not multi-user. > On each user's local PC as well or in > the folder on the server where the shared production backend (or > copy) is. If we're trying to restrict the intermediary app to one > user, then wouldn't it have to be on the server. There's no way > to control this if a copy of the intermediary app exists on each > user's intermediary machine. First off, a remote user's synch app will be picking up an update MDB from the dropbox on the server, and writing its own update MDB to the inbox on the server. It will be operating only on the local laptop's or workstation's database -- it won't be opening the production database across the WAN or you'll be right back where you started. Now, perhaps you're just going to be copying the whole production database off to the remote user? That would obviate the need for any outgoing message databases, and sounds like a good thing to me, because then you could compare the local database to the one from the server. All you'd do would be zip up the production replica and copy the zip file. Now, that's subject to the remote possibility of corruption, as you could be zipping up a file that's in an incomplete state (say some data pages have been written from a transaction but not all at the moment that your zip library compresses the file sectors), but in all the years I've copied open MDB files, I've never seen a single corruption. Of course, you'd be doing it systematically, so perhaps it *would* be better to have an intermediary database that is populated on a schedule (once every 10 minutes?) with data from the production database. When it's created, then you'd zip it up, and whatever the last zip copy is is what the remote users would download. That seems like a better idea to me. > 5) My plan was to launch the synch app when my main app launches; > but only for users who are given permission to do so (this would [quoted text clipped - 6 lines] > add additional instructions for new users (the more instructions, > the more confusion, the more calls......). OK, well, I'm confused again. Why would you want multiple LAN users synching? Don't you want your LAN users using the productiong database? > I guess I just don't see how having the intermediary app tells me > anything about users who are logged in directly to the production > backend. It doesn't, and I never suggested it did. I thought you were designing a scenario where you had a single instance of a synch app that was running to manage the synchs for your production database, and, like the synchronizer, you want only one process doing that synchronizing. One way to limit that to one user is the intermediary MDB that I suggested. > I thought that the way to determine this was to see if there > is a .ldb file for the backend file and/or use the Jet User > Roster. If you're trying to limit the synching to one user at a time, that won't accomplish anything, because you won't be able to distinguish editing users from the synch user. > Sorry, but I'm just not following you so I'll stop my post here > rather than ramble on with a false set of assumptions or > understanding. I find your description above confusing and contradictory. I'm obviously missing something major. It seems to me that you're replicating indirect replication. You'll have a synch app that replaces the synchronizer on each machine. You'll want only one copy at a time running. Your dropboxes will use zipped-up MDB files to push updates back and forth. It seems to me that your outgoing message MDB from the production server will need to be a full copy of the production database, but the ones coming in from the remote users need only be the records that have been added/updated/deleted in comparison to the full copy of the prudction database. But the key point is: you don't want multiple users running a synch at once on the production database, because that will increase the chances of update collisions. You'll want one synch app running on your LAN, taking care of scanning the incoming dropbox for update databases and then applying those updates to the production database. There are two goals I see there: 1. you want only one copy of the synch app, BUT 2. you don't want to lose the synching when some user happens to shut down her workstation (and close the single running synch app). That is accomplished by one of the two methods I described, either sharing the synch app and using its LDB file to figure out who got to it first, or using the intermediary MDB I suggested for the sole purpose of tracking who is running the synch program. The first user in the LDB gets the privilege of running the synchs. Users who start up the synch program when there's already someone in the LDB won't get to synch. But if you have, say, a 10-minute timer-based synch interval, you'd want to have it check the LDB file each 10-minute period to see if all the users that were logged on when the synch app was launched have now logged out. That |
Enable EMail Alerts
Start New Thread
Reply to this Message