Increasing Locations of Dbase Use VPN for regular backup to Master DB on Headoffice DBServer

Experts, I have a current company DB (Access XP 2K {Front / Back} on Win XP / 2000 machines) The data is stored on an SBS2000 server. I am now wanting to expand the company network to include multiple geographical locations using 1.5/256 ADSL connection (VPN) to transfer current days data input to the Master DB. My database is based on records that span multple tables as much as total 250+ fields, is there a way to extract only the changes made to the branch DB since the last backup. I would like to automate the backup to happen throughout early morning for all Branches then automate the update of the master each morning. Your thoughts
citywideAsked:
Who is Participating?
 
Excalibur_SoftwareCommented:
I hope we have been of help, if you need more help before closing down this question please let us know
Thank you
Al
0
 
shanesuebsahakarnCommented:
1) Replication:
http://support.microsoft.com/default.aspx?scid=kb;en-us;282977&Product=acc

2) Terminal Services
Since you have an ADSL link, TS is a good option, to allow your users to work directly on the database. We have multiple users working on TS, and it works extremely well. This would involve changing your configuration to eliminate branch copies of the database and only have a single master DB.
0
 
citywideAuthor Commented:
At present the Application side of the DB has all the code attached to use remote access does this need to go across to the server side. Is the terminal services access similar to using a VPN connection then loging into a workstation using something like TightVNC for remote access.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Excalibur_SoftwareCommented:
Another option that is safer would be to convert your back end to either SQL server or MSDE.  How many users are going to be using your database?
How time sensitive is your data?
Both SQL server or MSDE have the ably to roll back transactions so you can recover your database faster and easier if one of your user lose connection while writing data to your database
Access 2000 can be destroyed by lose of connection while writing to the dB.

I would still use Terminal Service to connect to your database.

Thank you for your time
Al
0
 
Excalibur_SoftwareCommented:
Here is a link you might want to read before you make up your mind
http://msdn.microsoft.com/chats/office/Office_100302.asp
0
 
citywideAuthor Commented:
I'm Happy for the data to be stored locally and packed up and sent through the VPN Daily to the server so that the data i have on the master is no more then 24hrs old. I want this process to occur automatically after hours from the Branch. I will need to implement some type of branch numbering so or assign a unique ID on the server (ApplicationID). I am the only one that needs access to all data, each branch does need to see the other branches data. I will certainly use Terminal services / remote access for tech support etc... Does this sound like I am on the right track, If so what methods could i look into to extract daily data and send automatically through VPN then automate the upload to the master DB.

Thanks for the quick reply's GREAT STUFF...
0
 
Excalibur_SoftwareCommented:
I think you are setting yourself up for some major headacks with data syn. issues
Example Branch 1 changes record 13
The same time branch 3 changes the same record
that night you update the data what will happen?
0
 
citywideAuthor Commented:
I am looking at the master to only view the branches data and extract the info i need, each branch works on its own work if I need to transfer an application between branches I will need to setup an export facility for that. I wont be changing data on the Master but I will need administor the DB be able to keep it clean etc....
0
 
shanesuebsahakarnCommented:
citywide,

Terminal Services is like using VNC. The performance across a DSL line is extremely good - we have a 2MB/512MB connection and we often have 6-7 simultaneous users without any problems. Of course, your server would need to be powerful enough to handle this.
0
 
shanesuebsahakarnCommented:
If you still prefer the synching method, have a look at the Replication FAQ that I posted a link to.
0
 
Excalibur_SoftwareCommented:
I think what shanesuebsahakarn and I am both are saying that we have tried the database Replication method and have found out that it is not the best way to go. Even Microsoft says that it is possible but does not  recommended the Replication method. But if you still want to do the Replication method the first link I posted has some good information you should know. One other issue you may want to think about is when using Replication as you have described if you get a error on the first transfer you lose all data from all branch’s not just the data from the one with the error. The other method allows you to setup a script that can backup your database every few second so if you get an error you lose at most one transaction.
Here is a link to all the information telling you how to Replicate a Access database
http://office.microsoft.com/en-us/assistance/CH062526841033.aspx?TopLevelCat=CH790018001033
0
 
shanesuebsahakarnCommented:
Al,

If a question has not had any comments for 21 days, it is classified as abandoned and will be handled by the cleanup process - you don't need to post comments in each of these questions. If you do, it means the cleanup volunteers will have to leave it for a further 21 days from the date of your comment before they can close it.
0
 
Excalibur_SoftwareCommented:
Thank you Shane I just ask that question of the cleanup
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.