Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2006
    Posts
    65

    Unanswered: mutliple instances of the same database

    hi,

    i have recently started a new job and the central databse is written in access. There are multiple instances of the same database. I need to query records; instead of repeating queries in each databse is there a way to merge all the records into one and then query?
    any help/advice greatly appreciated

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Yes, it can be done. Performance will be absolutely horrific, but you could do it that way. The other idea is to create a database that synchronizes everything, another horrific undertaking. Yet another idea would be create a meta-data database that would pull from all of them in a batch process during off hours. If you don't have any control over these databases or how they're used, this might be your best option.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2006
    Posts
    65
    thanks for your advice Teddy,

    i have complete control over the databases and have made copies to my hard drive.

    i am a newbie to access so can you please advise how to carry out the merge?

    is it possible to export each database to a spreadsheet, create a new database and then import from all the spreadsheets?

    many thanks

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If you are the de-facto admin, I would first question why you have various instances of the same database to begin with. Could you provide a bit of detail about the business logic and requirements for your system? We're talking birdseye vew...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Apr 2006
    Posts
    65
    i had a feeling you would ask me that,

    Let me first explain the situation, we have one database that data is currently being inputted to. However due to increase in data size and the system being poorly designed, it would cease to work. My predecessor created a new instance of the dbase by taking a snapshot each time this happened. The old instances are not being inputted to.
    I have basically inherited a mess and my job is to collate stats untill i oversee the implementation of a new system designed in SQL server which is due next few weeks.

    I require stats for the whole year and this means i have to query the current and old copies of the dbase. I am also a newbie to access so i dont know how to merge the data, is it possible to export to a spreadsheet for each instance and then create a new snapshot and then import from each spreadsheet?
    Last edited by waylander; 04-05-06 at 16:48.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I see. So your real question is how to marshal the ETL process to pull information from your undesirable databases into your live database. Is that about right?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Apr 2006
    Posts
    65
    Not necessarily to the live,
    the live will only remain for a few weeks and i dont wish to risk hampering the inputting process by fiddling with that ( there is a massive back log).

    i have taken copies of the instances including the live on to my hard drive for query purposes only

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It would behoove you to put all of your archival data in the same database. I would avoid querying multiple static databases like the plague. Put it all in one spot, it doesn't have to be your "live" database so to speak, but put all the archival stuff together.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •