Stab in the dark!

[paulcraigdainty]

I have four seperate databases each of identical structure. Each database has an option which exports the data to a Excel spreadsheet. I want each database to dump data to the same spreadsheet. I have used the code below and this works however when i execute on a different database the data in the spreadsheet is overwritten. I want the action to update the spreadsheet rather than overwrite, is this possible?

Private Sub cmdReport_Click()
DoCmd.OutputTo acOutputTable, "tblFault", acFormatXLS, "C:\Windows\Desktop\Fault.xls"
Application.FollowHyperlink "C:\Windows\Desktop\Fault.xls"
End Sub

 

[The_Doc_Man]

Not with the commands you gave it.

What you described is a non-trivial exercise. There are a couple of ways to do it but each has its problems.

Basically, you face a problem in destructive interference (i.e. left hand does not know what right hand is doing...)

The only way to NOT do this is to realize that one and ONLY ONE of your data repositories can be "authoritative" - I.e. the "true" data source. Sounds to me like you have at least four and maybe five sources. Nothing we tell you will fix the problem until you decide which source is authoritative.

To short-cut the situation, once you have established the authoritative source, the others must not update that source. Instead, that source must examine the alternates and do a form of database reconciliation / replication.

Look in the Access HELP files for the topic "Replica" and follow several threads. This topic has been addressed before under that keyword.