It's a problem with ALL database engines. You cannot enforce Referential Integrity between tables in two different databases. Period. It has NOTHING to do with properly distributing a db remotely. It has to do with the architecture of a database.
Of course, if you know of a case where "others have done it successfully" I am always open to learning. Links please, if they exist.
Hi George, the problem is, everyone thinks that databases started with SQL or Access. Forty years ago we had to write our own from scratch, with the idea of distribution in mind. My business partner Ted took about a month to write the code. It worked great. I can't provide a link, as this was well before the Internet and it was proprietary code which I no longer have anyways.
Besides just handling integrity, Ted handled synchronization on the record and field level with a pretty sophisticated transaction logging routine(s). We first had to do this as four of us were writing code and creating tables with what little time we could "borrow" from client's mini computers (we couldn't afford our own back then at a cost of over $50,000 for the cheapest model--not to mention the space and air-conditioning to run it). Once a week we all brought our 1/2" tapes to the vendor's sales office, which let us use their computer after hours on Friday nights, and synched up all the programs and data. It was rare there was a conflict, as Ted had each transaction log down to the millisecond of a given day, even though the time might be off a few minutes between all the computers we had used. Then we rolled out all the changes back to our client's computers the next week as we visited them (the long-forgotten sneaker-net). Now, if Ted could do that in a month, I wonder why Microsoft has had so much trouble over thirty years? Which all goes to show, one motivated to find-a-new-career-truck-driver can often do a better job that a "Seattle campus" filled with certified geniuses.
Sorry that I keep adding little edits, but (there's the big but), the main driver program for my "forms" program took 45 minutes to compile. Two years ago, when I had it running again, it compiled in less than a second. So, in my mind, there's no reason with the power available today, that MS can't do things we did forty years ago on a Friday evening (with beer and pizza).
Anyways, I think we come up out of one rabbit hole and gone down another from the original post. I've lost track of where that happened.
