Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2016
    Provided Answers: 1

    Unanswered: Errors with split databases

    Hi everyone,

    Not sure if you'll be able to help, it might be too specific, but basically I have a simulation with a lot of calculations, with plenty of dimensions (including a time dimension of 100 years on a monthly basis), fields and records. The main engine of this simulation is a form connected to a table of (scenario) assumptions, and a query which generates all the independent data required for the simulation. The simulation runs through 10,000 people, calculating data (over 1200 months) before summarising the results in different tables, both by individuals (summing over the 100 years), and by the years (summing the people).

    To improve the performance, I first set up the form to generate the query (and so run the simulation) for batches of 100 people at a time - the query runs for 100 people, goes through a while loop calculating the data, the results are stored, and the query is run for the next 100 people, and so on. However, this was still too slow, so I split the database in two. I copied the database and changed part of the form code in both databases - in the first I defined how I'd split the people (half and half) and created a single record table with the start and end person ID for each database, and added some code to open the second database, run it in the background, and open the form with the simulation. In the second database, first I changed all the relevant tables to linked tables (with the first database), I removed code zeroing out the reporting tables at the start of the run (so the results wouldn't be generated in the first database only to be emptied by the second), defined the start and end people for the simulation from the table created in the first database, and then wrote in the code that upon completing the simulation, the second database should close. I also set up the form with the scenario assumptions to load automatically on opening the second database, and the form to run the code on opening the form (as opposed to on clicking a command button like in the first database.

    So here's the problem - when I run the second database on its own, it works fine - it runs through the simulation, produces results which are written to the linked tables, and can be see in the first database when it's opened. However, whenever I try to run both simulations at the same time (starting the process from the first database), I get a whole load of error messages, about getting duplicate data in the index, primary key or relationship, as well as the form being unable to find the referenced scenario assumptions form (which is supposed to open on loading the database, and does when I do so manually). Notwithstanding that, when I click ignore End, the simulation in the first database seems to continue running, and I still end up with some of the reports populated with data, just not all of them..

    Since the form (in the second database) runs fine when run from the second database, I can only surmise that the problem lies in either the code in the first database which loads the (second database's) form, or in the linked tables (though I think that unlikely as the form does run, and does produce results. Below is the code I'm using to load the second database:

    Dim appAccess As Access.Application

    Const strConPathToSamples = "C:\...Model_Part_2.accdb"

    strDB = strConPathToSamples & "Model_Part_2.accdb"
    Set appAccess = CreateObject("Access.Application")
    appAccess.OpenCurrentDatabase strConPathToSamples
    appAccess.Visible = False

    appAccess.DoCmd.OpenForm "FRM_Calc", acNormal

    Can anyone offer any suggestions on this? I appreciate it's probably too hard to do without being there with me looking at it, but I've run out of ideas.. Thanks for any help.

  2. #2
    Join Date
    Apr 2014
    Provided Answers: 33
    Ive never had a problem with split databases.
    you should NEVER have to create a database object in code. Everything can be done with linked tables.
    Maybe this is your problem.

  3. #3
    Join Date
    Nov 2016
    Provided Answers: 1
    Thanks ranman256. I never figured out the problem, but I ended up returning to calculating the 10,000 people in one database, because I couldn't get the split database solution to run consistently. Sometimes it worked, other times I got error messages. I even tried populating separate tables (for the problematic ones saying they had duplicate data) and combining them again later in a query or form (I tried both). I could get all the data into 2 different tables without any error messages, but combining them (even in a new form) gave me errors.. Eventually I gave up and just decided to do it in one database, even if it takes about 1h30 to run on average.. Thanks anyway.

  4. #4
    Join Date
    Jun 2017

    Errors with split databases

    I wish Id seen one of these machines when I was growing up. I just never did. Im sure I wouldve been amazed, but all I can see this as is "how many quarters is 10 minutes worth of Bluth worth to ya?"

  5. #5
    Join Date
    Jun 2007
    Maitland NSW,Australia
    The simulation runs through 10,000 people, calculating data (over 1200 months) before summarising the results in different tables, both by individuals (summing over the 100 years), and by the years (summing the people)

    You calculate the data. Is the data the same for different tables if so why not use queries to do the summing?

    Alternatively your coding may need redesigning.

Tags for this Thread

Posting Permissions

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