Solved

how to synchronize Access database via the Internet

Posted on 2009-05-11
18
884 Views
Last Modified: 2013-11-10
Hi,
I would like to synchronize Microsoft Access databases via the Internet (TCP/IP). The Master and Slaves databases look exactly the same. The master database is on a Central Server and the clients are located on different geographically locations.  I am planning one-way (one-directional) synchronization with a session to be automatically scheduled and triggered on a weekly basis. this session will be triggered from the Central Server. All  servers are using Windows Server 2003.

1-      I want to know if this synchronization is possible.
2-      if there any product I can get to facilitate this job (third party software). or this is something programmable through VB or Access.
3-   is there something I can set up through Windows Server?.
Any ideas?
0
Comment
Question by:LougaLo
  • 9
  • 6
  • 2
18 Comments
 
LVL 28

Expert Comment

by:Bill Bach
ID: 24359303
A few basic questions:
A) How big is the Access Database?
B) Are the servers toi hold EXACT copies of the data (i.e. can we simplify things by just copying files)?
C) What access limitations do you have (i.e. must be FTP, file share available, etc.)
0
 
LVL 5

Accepted Solution

by:
noetymology earned 250 total points
ID: 24359381
- What version of Access are you using and what is the file format being used?
- Are you aware that the feature of Replication has been deprecated from Access 2007 and the future releases? Hence a solution to your internet synchronization might not work if you plan to upgrade to Access 2007.
- We cannot use Replication Manager to do a scheduled synchronization. This feature is not supported for Internet synchronizations. However, you can perform Internet synchronizations by using Visual Basic for Applications and JRO or by using Visual Basic for Applications and DAO.
- The JRO Synchronize method uses the following syntax:
Replica.Synchronize(Target [, SyncType] [, SyncMode])
- Detailed samples of the above code can be give to you if interested
0
 

Author Comment

by:LougaLo
ID: 24360802
BillBach: here are some answer to your questions.

A- I do not know exactly the size of the whole database.

B- we are looking for to be able to pull the data automatically from the clients to the central database.

C- right now only FTP is available.

0
 
LVL 28

Assisted Solution

by:Bill Bach
Bill Bach earned 250 total points
ID: 24364644
With a weekly update and only FTP access, replication may just not be worth the effort.  

The best option will likely be to find a time when the Access database is not in use at the primary site and make a quick backup of it.  This can be easily scheduled with SchTasks on the Windows Server OS.  (http://technet.microsoft.com/en-us/library/cc725744.aspx) This copy should be placed into a temporary holding directory.  This is as simple as a batch file that implements the file copy.

From the temporary holding directory, you can then have a scheduled process that connects to each of the target servers and FTP's the files up to the server.  Again, scripts abound from numerous sources on running FTP from a batch file, and you can make this as complicated or as simple as you need to. (http://technet.microsoft.com/en-us/library/cc725744.aspx)  Conversely, you could set up an FTP server at the source server and have the other (remote) stations download the file.

Then, on the target servers, you can either have one more script that copies the data over the top of the original file, assuming that you didn't FTP it right into the target file location.

Of course, some of this falls down if the files are in use 24x7
0
 

Author Comment

by:LougaLo
ID: 24383864
Hi BillBach:

the issue here is I don't have yet the databases. I am on the design stage. but I am anticipating on the next task.
 
I have a couple of questions:

1-It seems like I can use FTP + Windows Server + a Script to perform the transfer either from the Source (central server) or the Targets (client server)? I already have a FTP site and Windows Server 2003 but I don't have any experience with running a scripting on FTP to transfer data.

2-  I am not familiar with Windows Server but do you think Windows Server has component that can allow the Source server to connect directly to other Target servers. Reminder: the servers are not on the same LAN. if this is possible can we have a script to perform the transfer task from Target servers to Source server (I need just one way transfer).






0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 24383942
As long as ftp ports are open between the boxes, ftp will work.
There are numerous posts, both here and on the 'Net, about running ftp from a batch file. Just search on "ftp batch script" and you'll hit several -- some very simple and some quite complicated.
Just pick one and run with it.

Again - this solution works as long as the database on the targets is closed when you are transferring the data.
0
 

Author Comment

by:LougaLo
ID: 24389248
BillBach:

I am a little bit lost but let me summarize the steps you sent. please let me know if I am on the right track.
we have 1 [Source_Server] and 5 [Target_Servers]
1- on [Source_Server]: close the Access database.
2- on [Source_Server]: schedule (using SchTasks) a backup of the Access db and put into a temporary holding directory (THD)
3- on [Source_Server]: from the THD run a scheduled FTP Batch script that connect to the [Target_Servers] and download their files into the THD.
4-on [Source_Server]: have a script that \Append the new data to the original on the database.

I can't test it right now but if this is what you meant please let me know.
Thanks

0
 

Author Comment

by:LougaLo
ID: 24389769
Hi noetymology:

I was reading more about your solutions. I found this article on MSDN site. is this what you are referring to when you said "However, you can perform Internet synchronizations by using Visual Basic for Applications and JRO"?

I know that Access 2007 does not support Replication.

I am using Access 2003.
0
Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

 
LVL 5

Expert Comment

by:noetymology
ID: 24390111
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 24397830
You have confused me now.  Your original post indicated a Master database on the central server and one-way replication.  Yet in items 3 & 4 above you appear to be dragging data from the slave databases back into the Master?  This seems backwards -- the definition of a Master database with one-way replication is the one that is always current, and the Slave databases are merely copies.  This sounds now like you want to do two-way replication, which portends a completely different solution.

Based on the original request, step 3 should be to connect to each Slave server and push the Master copy of the database to each server, which would normally be the end of it.  If you think that the Slave databases may be in use, then you may need to upload it to a THD on each slave and then run a scheduled task that periodically tries to copy the database over from the THD to the Slave data directory (and delete the THD it when successful).

If you are now wanting another configuration for 2-way replication, then all of my answers above are bunk and should be ignored.
0
 

Author Comment

by:LougaLo
ID: 24398415
BillBach:

sorry for the little confusion. I am still planning

-one-way synchronization .
-to having a Master database located at the central server. all databases can be shut down if necessary.
-to connect to each Slave server (one at the time)
-to copy data FROM each Slave database TO the Master database.

then the master database get all the data from the different slave databases. hope this clear and can be done.

0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 24398548
This is not a 1-way replication, but rather is a many-to-one replication, which can be infinitely more challenging, unless the data slices are completely non-related.  (If they were non-related, of course, then you probably wouldn't be merging the data.)  You can ignore my previous comments.

Your comment from 05/14 (ID:24389248) is still a valid solution -- but you can just do these two steps:
  3-on [Source_Server]: from the THD run a scheduled FTP Batch script that connect to the [Target_Servers] and download their files into the THD.
  4-on [Source_Server]: have a script that Append the new data to the original on the database.

Look back at the suggestions from noetymology, or you'll need to write your own import scripts from one access DB to another.  Dealing with record conflicts is a whole different issue, of course, that may or may not be ignored, depending on your data.
0
 

Author Comment

by:LougaLo
ID: 24398608
BillBach:

I already considered noetymology suggestions however I would like to have a second plan. I believe your suggestions make sense. thanks
0
 

Author Comment

by:LougaLo
ID: 24398633
BillBach:
 I found this third party tool what do you think?

http://www.goodsync.com/index.html
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 24398674
Negatory -- that is a file sync tool, not a data sync tool.  That is more along the lines of my original idea -- we could possibly implement this by just copying the database files around.  In your case, you must actually copy the individual records from the 5 databases to the common target, and this is a lot more difficult.

If you're looking for a tool, then an ETL tool (Extract/Transform/Load) is really what you need.  I've got experience with Pervasive's Data Integrator, and you could use their high-speed engine to automatically do this very easily (probably in about an hour of setup time), but the Engine is pretty expensive and probably out of scope, unless you have a LOT more integration work to do.   You may also wish to check out the open source alternatives from Jaspersoft and Pentaho as cheaper alternatives.
0
 

Author Comment

by:LougaLo
ID: 24398796
BillBach:
"that is a file sync tool, not a data sync tool": thanks for this clarification and for the third party tools. these good to know. for now I will be focused on the solutions I got from you and noetymology
Regards
0
 

Author Comment

by:LougaLo
ID: 24411931
@ Admin:

actually the comment ID: 24364644 from BillBach is valid as well. I forgot to select multiple solutions. is a way to select that comment and split the points.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now