Is it OK to have multiple Access instances running in parallel?

Question

I have multiple sets of code I need to run in Access 2007 and I have been trying to see if it would be acceptable to run them at the same time. Each set of data I need to have my Access VBA code run through takes about 5-8 hours to run. Would running them two at a time or all at once cause any problems?

They are each self contained and are pulling no outside information so there wouldn't be a problem with them trying to access the same outside information at the same time.

Edit:

Each Access program would be running code in VBA that essentially compares the stored latitude and longitude locations of devices (MIUs) to a set of grid points and groups all MIUs in said grid in a new table. The grid is shifted after this process and repeated until the maximum latitude and longitudes for the entire search grid have been reached.

Answer 1

If you mean 2 different instances of Access then this will depend more on what you are doing and the capacity of the computer (RAM and Processor Speed, Number of Processors).

I find if I am importing larges amounts of data and move the focus away from access, when I try to switch back the application may stop responding.

You can always try with a test run...

When I have a large job to run I do it on our Citrix server.

It is a good idea to have a progress meter running so you can check the progress.

Answer 2

This is really a "who knows?" type of question. If the 5 - 8 hour process is consuming either most of the CPU cycles or most of the RAM then running two processes on the box won't improve speed; it would actually degrade it due to task switching reasons.

If those processes are truly independent, you might consider having multiple machines work on different copies of the database. That way you could scale out.

Of course, I'm not sure what the process does so it's hard to say whether this is viable or not.

Answer 3

As many know, I'm a big promoter of Access. In this case, it seems to me that Access is probably not the right tool.

But I'm assuming you've implemented your process in Access in the most logical possible way. For instance, if you could replace walking a recordset and updating a field/fields in each row with a SQL Update, that would likely be vastly more efficient.

One thing to consider is that many people often walk a set of records making updates when they could probably slice the problem vertically and issue a finite number of SQL statements (one for each column). An example of this would be when you are trying to synchronize the data between two indentical tables that have both been updated. It's much more efficient to write on-the-fly SQL Updates that go column-by-column than it is to walk row-by-row and updating field-by-field.

The performance improvement from such a re-design can be more than one order of magnitude.