Question Application data transfer through internet

[mcdhappy80]

I need to create a database which will be operated on two different workstations and on two different networks.
One networks has a server where main database will be stored and that network will have its front end database from wich the user will manipulate data stored in back end on server.
On the second network there will also be a front end and the back end database and all the data manipulation will be done there
On the end of each workday the back end from the second network will be copied through internet on the first network (on the server) and I need to update the server back end database with all the record changes done on the second network.
At the beginning of the next workday the user at the second network will download the synchronised copy of the back end from the server, again through internet, and will continue his work for that day.
And the process starts over. Here is the scenarion interpreted graphically:

What I need to know before starting this whole thing is:
1) Is this the best scenarion to do this?
2) I've read something about access replication features, can it be applied in this situation?
3) Is moving the database back and forth a good idea, or it would be maybe better to create web application for the second network, while the database will remain on the server of the first network? Will I have to watch then about record locking because the two users will modify the data in the same time on the servers back end database?
4) What would be more simpler, synchronising two datatabases (with or without access replication features) or dealing with record locking, I have to state that speed in data manipulation and communication with the back end on the first network server is important here because the user on the second network will deal with his clients in real time and we need to cut the time he needs to enter the data and release the pressure he's on, so he needs to retrieve the data on his workstation ASAP. The user on the first network doesn't work with clients in real time so he is not under pressure when working.
5) Someone has mentioned on the forum Windows Terminal Service as a method of connecting the second LAN through internet with the first LAN where server is (and all application will be stored there). Will this method be fast enough for the user on the second LAN? Also I know nothing about this so I need to know what softeare I would need installed on both LANs.
6) So, the main issue here is data transfer through internet, is there are some other means to acomplish this I'm open for all suggestions.

Thanks for the answers.

 

[DCrake]

You will need to be careful about replicating the autonumbers in both databases. This is not a situation I would like to implement as it is fraught with danger. For example a user on server one would update record 100 likewise a user on server 2 updates record 100. You need to decide which is the most uptodate. It may not be timestamp correct. Its the data that determines the accuracy. If user 2 is deemed to be the superior record change and the date is updated. When user 1 revists this record they are going to say "Where are my changes that I made? what's happened to them?" Not a very good situation is it?

David

 

[Banana]

Personally, I'm inclined to say that it would be far better solution to use any of those following:

1) Windows Terminal Server & Remote Desktop/Citrix/Any kind of Desktop sharing application
2) RDBMS backend that supports WAN connections such as SQL Server, MySQL, Oracle, PostgreSQL
3) If you're using .mdb format, you can use Jet replication, though it isn't available in .accdb format and you would probably need indirect replication which is even more complicated.

 

[mcdhappy80]

What about this idea:
When the user on LAN 2 finishes his work and send his Back End to LAN 1, can that Back End 2, when gets on LAN 1, extract only that data that is added( and/ or changed) and import it in the Back End on LAN 1?
Is the thing simpler if User 2 on LAN 2 only ADD new records not change them, so that only new records need to be imported in Back End 1?

 

[Banana]

Emailing-forth-and-back would work as long there is only one person using it at a time. The problem can arise if both user unknowingly add a new record without having synchronized the newly added records, resulting in a duplicate primary key.

There's also the fact that there may be an eventual 3rd or 4th user and you'd be back to square one all again.

 

[DCrake]

Could you not put the mdb in a sharepoint location?

 

[mcdhappy80]

Emailing-forth-and-back would work as long there is only one person using it at a time.

Each user is working with its copy of the back end.
Both users finish at the same time, then the Back End 2 is e-mailed to LAN 1, and then the synchronisation should take place, copyinign (or whatever needs to be done) the newly added records in Back End to to Back End 1 (thus creating one database with both records), which will be sent to LAN 2 next morning so that User 2 can add new records if needed.
So, basically only one user is adding records at the time to his copy of the back end while two databases should merge their records when no data entry is allowed.

The problem can arise if both user unknowingly add a new record without having synchronized the newly added records, resulting in a duplicate primary key.

No doubt they will add new record and if PK for records is Autonumber datatype there will be different records (in two databases) with the same PK values.
So can I overcome this with replication? I'm developing in Access 2007.

There's also the fact that there may be an eventual 3rd or 4th user and you'd be back to square one all again.

No there won't be 3rd and 4th user.

 

[Banana]

No doubt they will add new record and if PK for records is Autonumber datatype there will be different records (in two databases) with the same PK values.
So can I overcome this with replication? I'm developing in Access 2007.

Well, maybe. It really depends whether it's worth the hassle. As I said, if you're using .accdb format, replication simply isn't available. You need to be using .mdb format (which you can create/run in 2007 as well) to have replication.

But if it's just a few tables and easy to script, I would probably just create two columns and use it as a composite key: One Autonumber, other a Byte containing "1" and "2" for the user so you don't have to worry about duplicate PK values.

No there won't be 3rd and 4th user.

That's true today, probably, but will it remain that way tomorrow? The point is that you don't want to end up with a setup that's a one-shot deal and to be redone when you have to add more users or when it changes.

 

[mcdhappy80]

Could you not put the mdb in a sharepoint location?

What is sharepoint location?

 

[CheapSlider]

On the assumption that each location 'owns' its own records:

use a different index range on records from the 2 locations
EG:
Main location from 1000000
Satelite location from 5000000.

Satelite sends records to main.
Main location appends satelite records to its own
Main sends combined records to satelite.


Based on Index range, the form at each location allow only locally created records to be altered. A field could advise of ownership

 

[NigelShaw]

Hi

I use a program that has a remote faculty. What this program does when I go remote is collect the next 200 records for the remote user so if any records are added to the main db, the records are added 200 records ahead. Then when the remote user syncs, the remote records are placed and then filter in numerical order and the main db records altered to match the last number of the remote entries + 1 and so forth.

Nidge

 

[mcdhappy80]

Will replication encounter problems if the users on two locations only add new records, not edit the existing ones?
I think that users won't be adding records with the same data in it.
What do You think?

Thank You

 

[NigelShaw]

Hi

to be honest, I wouldn't have thought so as the new record would lock until completed and as it is placed by a SQL string, it's almost instant. I think the remote users would have to try add a record at exactly the same time which is possible I suppose.

Nidge

 

[dfenton]

Will replication encounter problems if the users on two locations only add new records, not edit the existing ones?
I think that users won't be adding records with the same data in it.

With replicated data tables, the Autonumbers become random (existing ones are not altered, but the new ones are random), which insures the smallest possibility of PK collisions between two replica sets.

APPEND-only applications are absolutely the safest with Jet replication, as there are no potential conflicts between replicas. Random autonumbers will insure no PK collisions (assuming you're using an Autonumber for your PK). If you're generating your own sequence as PK, then you will need to convert that to a compound key, with a second column identifying the source replica.

But this is the easy part of Jet replication, i.e., designing your schema.

The hard part is the synch, and if the replicas are on different networks, you're going to need indirect (or Internet) replication. This is pretty complicated to implement, so you might ask yourself whether you want to use replication in this situation.

Given that you're in an append-only situation, you might consider *not* using replication, and instead just ship temp MDBs with the newly added records. You could use email, or if you've got a VPN, each machine could drop the temp MDB in a folder on the other machine. If I had this kind of application and hosting the app on Windows Terminal Server was not a viable option, I'd likely considered this non-replicated approach before I started mucking about with indirect replication. This is despite the fact that I have over a decade of experience with Jet replication.

 

[mcdhappy80]

Thanks David for such thorough explanation.

With replicated data tables, the Autonumbers become random (existing ones are not altered, but the new ones are random), which insures the smallest possibility of PK collisions between two replica sets.

APPEND-only applications are absolutely the safest with Jet replication, as there are no potential conflicts between replicas. Random autonumbers will insure no PK collisions (assuming you're using an Autonumber for your PK). If you're generating your own sequence as PK, then you will need to convert that to a compound key, with a second column identifying the source replica.

You mean composite PK consisting of two fields where one part of PK is, lets say, autonumber field, and other could be number which identifies from which user the data comes. For example:
0101 - first record from first workstation
0102 - first record from second workstation

But this is the easy part of Jet replication, i.e., designing your schema.

The hard part is the synch, and if the replicas are on different networks, you're going to need indirect (or Internet) replication. This is pretty complicated to implement, so you might ask yourself whether you want to use replication in this situation.

Given that you're in an append-only situation, you might consider *not* using replication, and instead just ship temp MDBs with the newly added records. You could use email, or if you've got a VPN, each machine could drop the temp MDB in a folder on the other machine. If I had this kind of application and hosting the app on Windows Terminal Server was not a viable option, I'd likely considered this non-replicated approach before I started mucking about with indirect replication. This is despite the fact that I have over a decade of experience with Jet replication.

I thought about that non-replicating mechanism (with PKs) You suggested, but I wanted to automate synchronization process as much as possible to the end users, so I thought, if Access already has that mechanism (replication) implemented why not.
My idea was to create that "synchronization" between two database with one mouse click, if possible, through VBA code.
Are there VBA code examples to create this data exporting/importing scenario?

Thank You.

 

[dfenton]

You mean composite PK consisting of two fields where one part of PK is, lets say, autonumber field, and other could be number which identifies from which user the data comes.

Yes, that's correct.

For example:
0101 - first record from first workstation
0102 - first record from second workstation

If you mean:

ID      Source
1         1
1         2

Then, yes. But I'd actually use it in the other order, with the machine column 1st and the ID column second.

But it really matters none at all, as this is just a way of a generating unique compound PK across multiple databases.

I thought about that non-replicating mechanism (with PKs) You suggested, but I wanted to automate synchronization process as much as possible to the end users, so I thought, if Access already has that mechanism (replication) implemented why not.
My idea was to create that "synchronization" between two database with one mouse click, if possible, through VBA code.

It's doable with Jet replication, of course, but it requires indirect or Internet replication, unless you don't mind corrupting or losing all your data every now and again. And the programming requirements are at a substantially higher level of sophistication for Jet replication than it is for the method of shipping non-replicated MDBs back and forth.

So, after all the programming, while the end-user experience might be an easy one-click experience, you still have all the issues of setting up each workstation and installing all the needed components and keeping them all in working order, which is itself quite complicated. For a taste of what's involved with this, see the Jet Replication Wiki article on setting up indirect replication without Replication Manager:

http://dfenton.com/DFA/Replication/...irect_replication_without_Replication_Manager

Are there VBA code examples to create this data exporting/importing scenario?

I'd think it would be very simple code. You'd keep a copy of your empty transport database as a template, make a copy of it, append the new records to its table(s), zip it up (or not -- it might not be large enough to matter), copy it across the WAN to the other machine and be done with it.

In the other directioy, you'd also periodically check to see if thje local computer has received a transport database from the other machine, and if it's there, import its records into your local database.

That sounds to me like quite a few programming steps, but each of them is very simple. Sure, you could complicate things by given each transport database a different name, and archiving the files after you've imported them, but the basics are pretty straightforward, seems to me.

The only problem I can think of that could be a problem would be making sure the VPN was up and running before trying to copy the file across to the other side, but that's an issue with indirect replication over the Internet, as well, so it's not really something specific to the approach I'm suggesting.