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.TransferD atabase 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.TransferD atabase, 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.TransferD atabase 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.TransferD atabase 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…..identi fying and resolving conflicts can require considerably more of your time than anticipated, even where a code approach is used.

The transferdatabas e 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]

Merging Tables Using SELECT Query

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?