Database Replication Issue

[iheartvba]

Hi,
I already have 3 Databases running:
A. they all have the same tables and the same structure
B. There is no 1 Master table they are all separate tables

What I want to do is to merge them into 1 Master Table to be able to generate reports etc. An append query will not suffice because some fields get updated in the orginial databases.

I have looked into database replication and I think it would work. I have considered the following method:

1. Merge all Data into 1 table
2. create 3 replica's

Issue with this method:

1. Each Database will be populated with data from 2 other databases which is not needed.

Is there a way to resolve this, because if I delete the data from the 2 other tables, wouldn't the replication wizard want to delete that data from the Master aswell?

Best Regards

[puppydogbuddy]

In my opinion Replication is definitely not the way to go for what you want to accomplish. Instead, I recommend the DoCmd.TransferDatabase method as discussed and illustrated in the following links:

http://msdn.microsoft.com/en-us/library/bb214131.aspx

http://www.blueclaw-db.com/transferdatabase-docmd.htm

[NeoPa]

You seem to use the terms "Database" and "Table" interchangeably. These two terms most definitely do not have the same meaning.

As a result, getting a clear understanding of the question is difficult without making assumptions, which I prefer to leave to those who don't know better ;)

PS. In case that sounds like I may have been referring to pDog, I can say categorically I was not.

[puppydogbuddy]

You seem to use the terms "Database" and "Table" interchangeably. These two terms most definitely do not have the same meaning.

As a result, getting a clear understanding of the question is difficult without making assumptions, which I prefer to leave to those who don't know better ;)

PS. In case that sounds like I may have been referring to pDog, I can say categorically I was not.

Adrian,
LOL!! At the time I answered the question, it seemed pretty clear that the OP simply wanted to consolidate data from external databases with the same table structure into a master table for reporting purposes.

pDog

[NeoPa]

pDog,

For those experts (such as yourself I assume) that are already familiar with the subject matter I expect there are assumptions that can be made quite sensibly. As I have no experience in this area and simply look to find a sensible solutions to posters problems in such cases, it is very important that I (and other experts in a similar position) have a question that explains what's what.

As explained earlier, I intended no criticism towards yourself. Simply at the OP who seemed to expect people to realise he meant one thing when saying another.

There are other areas of Access where I am on quite familiar ground and I can guess what the OPs mean even when poorly expressed. Other experts may struggle in the same situations. I try to encourage members to post questions that make sense in all cases, on the assumption that they don't know who will be available to offer a solution. It seems only reasonable after all, if they expect others to go to far greater trouble on their behalf.

In case it's not clear, I also understand that members are often unable to express themselves clearly in technical terms, even when they do make an effort. Making the effort never hurt though.

In this case I felt the question led rather to a solution which was query based, as he didn't want all the data in together except for the report. Sometimes the OP's own perception of the problem can be quite ill-informed. I didn't want to assume that though, as I've been led up many a blind-alley before.

Anyway, we can see how this one progresses :)

[iheartvba]

Sorry, my mistake, I encountered a brain not work error in my brain. I have amended the post, as well as adding a query regarding PDog's reply

Hi,
I already have 3 Databases running:
A. they all have the same tables and all tables have the same structure
B. There is no 1 Master Database they are all run independantley

What I want to do is to merge them into 1 Master Database to be able to generate reports etc. An append query will not suffice because some fields get updated in the orginial databases.

I have looked into database replication and I think it would work. I have considered the following method:

1. Merge all Databases into 1 Database
2. create 3 replica's

Issue with this method:

1. Each Database will be populated with data from 2 other databases which is not needed.

Is there a way to resolve this, because if I delete the data from the 2 other databases, wouldn't the replication wizard want to delete that data from the Master aswell?

Best Regards


P.S. as per Pdog I have now also considered doing the DoCmd.TransferDatabase, from what I understand this is how it would be done:

Lets Call the Master Database office M_DB
Lets Call the Sub Database offices S_DB

1. Create VPN between M_DB and S_DB's
2. Delete all tables in M_DB and initiate DoCmd.TransferDatabase from the first S_DB to M_DB

a. the reason for deleting the tables is that some fields may be updated so access would not allow a duplicate primary key for those records

3. Then the DoCmd.TransferDatabase would be initiatied on the other 2 S_DB's
4. if my understanding is right, then isn't this a really long winded way to acheive my objective

[puppydogbuddy]

What I want to do is to merge them into 1 Master Database to be able to generate reports etc. An append query will not suffice because some fields get updated in the orginial databases.

I have looked into database replication and I think it would work. Issue with this method:

1. Each Database will be populated with data from 2 other databases which is not needed.

Is there a way to resolve this, because if I delete the data from the 2 other databases, wouldn't the replication wizard want to delete that data from the Master aswell?

4. if my understanding is right, then isn't this a really long winded way to acheive my objective

In my opinion, replication represents the long-winded way to achieve your objective. Firstly, replication is suited for home office with large distributed databases, each of which need to work from a consistent (consolidated) pool of data. You stated that the master table would be used strictly to consolidate data for reporting purposes, and that only the master table needs to have all of the information. The distributed computers are to be used only to capture data at the site and pass it on to the Master table at the home site.

With replication, true synchronization of all replicas and the design master is more difficult to achieve in practice then in theory…..identifying and resolving conflicts can require considerably more of your time than anticipated, even where a code approach is used.

The transferdatabase method is analogous to an automated file import/export, coded to run at the click of a button. And contrary to your conception, I don't believe the "master" table has to be deleted. The way I conceive the process, is that the master table would have its own autonumber primary key plus an additional field that would be used to capture and store the sitePrimaryKey. The retention of the sitePrimaryKey in the master table would enable you to distinguish between new records and updates of existing records.

[iheartvba]

Thanks,
Actually there would need to be some deletion otherwise there will be unnessecary duplication. I will go with the transfer database method as it doesn't really matter if there is deletion or not.

Much Appreciated

[NeoPa]

If the databases are accessible over a network, what about merging the data together on the fly using a query instead. This would assume only read/only access is required of course (for reporting).

I would consider an ACTUAL master database here, at your site, which has no actual data, but simply links to all the required tables of the other databases.

Does this sound like it may fit the bill?

[iheartvba]

That would be absolutley brilliant!

I thought it wasnt possible to merge muiltiple tables with the same table structure into 1 table without using an append query. If what you are saying is that I can merge multiple tables using only a select query that would be excellent.

How would I go about doing that?

[iheartvba]

Wooohooo

Thank you Thank you Thank You!!!

I think the person who invented access should be given a Nobel Prize. It never ceases to amaze me. Wow! A Union Query allows you to merge 2 tables.

Thank You NeoPa

[NeoPa]

A UNION query would be my first recommendation, but there may be issues with that, particularly if your data comes to many thousands of records.

MS Access struggles to produce the same level of optimisations for tables joined in a UNION. Frankly, unless the volumes are severe I would stick with that, but if that becomes too much of an issue then you can build a temporary table (or some tables) in the local master database and then run your report(s) from that.

You would need to determine the balance of what constitutes the better solution for you. Bear in mind if the latter option becomes necessary, a message to the operator explaining the delay while the building is going on, followed by more responsive reports, is often considered an acceptable way to work.

Good luck with your project anyway :)

[NeoPa]

How would I go about doing that?

I assumed that you are now happy with this and no longer need a direct response. If this is not the case please let me know.

[iheartvba]

Hi NeoPa,
Sorry about the delayed response. Firstly yes I am happy with the response, I have not been able to configure everything yes so I'm not sure if it is slow or not.

I just don't know how VPN's work, I know how to connect to another computer via VPN but then what? How do I connect the back end of the remote office to the main database?

[NeoPa]

I think you may have to tell me how VPNs got into the picture.

Virtual Private Networks are a whole subject unto themselves. Depending on how complex that gets we may need to set that up as a new thread in the Networking forum.

I certainly need a better understanding of what you're talking about before I can proceed though. Are VPNs mentioned anywhere already in this thread at all?

[iheartvba]

See Post #6 it has mention of VPN.

I will repeat the situation

There are 3 offices

1 x Main Office With A front End

2 x Branch Offices with Separate Back End's

The Main Office needs to do the following:
1. run reports from data derived from the branch offices
2. Receive payments from clients and then append the branch office databases to mark the invoices as paid at the branch office.

Now I don't really understand how this will work. I don't want to download the Databases everyday using Remote Desktop. I would like to create some sort of a inter office network where the main office Front End can connect to the Branch Office Back Ends and Vise Versa.

Thanks

[NeoPa]

The fundamental first question then is :
Currently, what links do you have available to the offices from your main office?

[iheartvba]

The fundamental first question then is :
Currently, what links do you have available to the offices from your main office?

1. Internet

A. Download entire database from Remote Desktop

B. have entire Database Emailed to me

2. USB Hard Drive

A. I go to the offices once a week, and I then copy the databases and bring them over.

But I'm confused why is the fundamental question the links between the offices?

[NeoPa]

It's fundamental because if you, like many do nowadays, had a directly addressable link to the other files though your network, then we would not be looking at getting copies transferred, but at accessing them directly instead.

Now I know that you don't currently have a direct link, we are looking at :

1. Getting a direct link (VPN) set up.
2. Getting copies transferred.

I would not consider getting a VPN set up without professional assistance. An IT department may be able to arrange it for you, but someone needs to have good knowledge of what they're doing. Security is extremely important in this day and age, and an inadequately set up VPN could easily compromise that.

Otherwise, you need copies available which always need to be stored in the same (relative) locations.

[NeoPa]

I've just reread post #16 and it seems you may want to update the remote databases too. If so, this is a far more complicated problem. Is this the case?

[iheartvba]

I've just reread post #16 and it seems you may want to update the remote databases too. If so, this is a far more complicated problem. Is this the case?

Yes thats right, I also need to update the remote databases but even if I can just get the remote office data to the Main office that would be sufficient, it's not essential for me to update the remote databases, I can do that manually.

I still don't understand, how would I go about doing this. Shouldn't I just be able to connect to a VPN, there by creating a Wide Area Interoffice network and connect the main office front end to both the remote office back ends. Please excuse me if I am repeating the same question but I am still genuinley utterly confused.

[NeoPa]

If you have a working VPN available, then yes. This is perfectly true.

Unfortunately, there is a world of problems (Networking not Access problems) in setting up a VPN safely. I would never personally recommend anyone just try it out for themselves. Maybe there are other networking experts who may feel comfortable with that, but I don't. It's too easy to leave a link into your actual private network accessible by third parties.

What position are you in exactly relative to VPNs in your organisation?

[NeoPa]

Yes that's right, I also need to update the remote databases but even if I can just get the remote office data to the Main office that would be sufficient, it's not essential for me to update the remote databases, I can do that manually.

With a VPN, both way transfers are possible (feasible even).

Without it, external transfers (of whatever variety) will be necessary. The reporting is relatively straightforward. Updating the remote DBs is a little more complex. Possible, but more fiddly.

[iheartvba]

With a VPN, both way transfers are possible (feasible even).

Without it, external transfers (of whatever variety) will be necessary. The reporting is relatively straightforward. Updating the remote DBs is a little more complex. Possible, but more fiddly.

What I currently do is this:

I generate the required information from the data available in the remote office, that information (query) is then exported to a text file. The text file is manually e-mailed to head office. Then someone at head office downloads the report from their e-mail and imports it into their database. This works fine but it isn't foolproof, if I am just contemplating whether to invest time in making it foolproof, or whether there is a more sensible way of doing what I was trying to do. So if you have any suggetions please provide them


Your Help is Much Appreciated

[NeoPa]

What position are you in exactly relative to VPNs in your organisation?

That depends fundamentally on your answer to the question above (from post #22).

[iheartvba]

Sorry I just realised I didn't answer that question.

I use Windows XP Professional VPN. We are a small accounting firm and do not have servers in any office, we have peer-to-peer networks in all offices. So all I have done so far is setup the Windows VPN connection in all of our offices, then connected those VPN's, a VPN network connected icon came up near where the clock is on the right hand side of the windows toolbar, showing there is a connection, but I am stuck after that. I thought the computers on the remote office LAN would just show up in my network places.

Just as a note the DNS server Address for the head office and remote office is different so could be that is the problem.

[NeoPa]

The DNS thing certainly won't help.

This is a networking issue then.

It's good news you have a VPN, but you need to be able to browse to the other offices (at least access them across the network) otherwise it is no help.

[iheartvba]

I will have to dig up some time to play around with this, because i would need to change the DNS server for the main office. Would you be able to refer me to a good forum where I can get more info about accessing remote office computers using windows VPN?

Thanks alot for your help

[NeoPa]

Have you tried either of our Windows or Networking forums?

[iheartvba]

Just to update, yes I have tried the networking forum, I have continued this question on:

http://bytes.com/answers/networking/...opeer-networks

[NeoPa]

In that case, I will lock this thread to further posts. Otherwise there is the danger that responses appear in both threads and one will not know of the other.

Good luck with your problem.