Avoiding duplicate data in different databases.

[LurfysMa]

I am working on a utility program to help test programs figure out
which test to run next.

Each test program will have a number of test categories and within
each category, anywhere from 100 to 100,000 "trials". Within each
category, the trials are graded in some way. Sometimes, the test
perogtram will start with trial #1 and run through them in order.
Other times, it will only run trials 500-1,000. Still other times, it
will select a random trial.

After each trial, it will send the results to the utility program,
which will keep track of them, do some calculations, and decide if
that trial needs to be rerun. If it does, it will tell the test
program to rerun it. If not, it will check to see if any of the
previous-run trials in that category need to be rerun. If yes, it will
tell the test program which one. If not, it will tell it to choose a
new trial that has not yet been run.

This is where the problem arises. How can the test program figure out
which new trial to select?

It needs to run a query that asks the database to return a trial
number that (a) has not been run yet and (b) meets the selection
criteria (within 500-1000, random, etc.).

The utility program has the list of trials that have been run in each
cagtegory. The test program has no need to keep track of that
information except to tun the "next trial" query.

I would prefer that the test program not have access to the utility
program database.

Here are the solutions I thought of:

1. Just have the test program maintain a table on the trial that have
been run. Thos will be redundant data as it is already being tracked
by the utility program. This is the simplest (I think), but has
duplicate data. I am not sure how bad this would be. Some of the
categories can have hundreds of thousands of trials.

2. Devise a way for the utility program to pass back a dataset
containing the list of previously-run trials. This could be a farily
large dataset.

3. Allow the test progrtam to query the test database. Is it possible
to query tales in different databases?

Any other ideas?

Thanks

 

[HS Hartkamp]

From what I understand, the trials are uniquely identifyable. If so, I'd
have the utility program keep record of all tests, using one table that
contains TestID, Passed (boolean), and perhaps dtPassed (date) and dtRerun.

I would not classify that as duplicate data (concern with solution #1). This
seems to me by far the simplest method.
You can then either:
- Link that table to all your testprograms, and tell the tesstprogram to
first check if the test has not already passed, or
- check from the utility program before you send the commands for further
testing.

Size is -in my opinion- not a problem. Bad maintenance and large databases
can be. If an access database grows too large, you'll loose it. (if you have
risks like these, keep regular backups.)
Databases can be somewhere between 1 and 2 Gb - I think-, depending on the
version of Access. If after compacting the database is too large to your
taste, then split the data over several databases, and link the tables to
your program(-database).

This way, I regularly work with databases of 1,6 and 1,9 million records.
The lartgest table I have had was 5,4 million records (with not too many
columns). With proper indexing the speed is still acceptable.

Linking tables is extremely easy, and the linked table can be used in
queries and recordsets just like any other table.

Good luck,

Bas Hartkamp.

 

[dbahooker]

Don't listen to this idiot.

Keep all your data in a single database.. move it to SQL Server and
migrate your forms and reports from silly ole MDB into the
revolutionary ADP format.

it will simplify your life tremendously

-Aaron
ADP Nationalist

 

[dbahooker]

and you won't have the same 2gb limit.

MDB is for babies.

-Aaron

 

[HS Hartkamp]

Don't listen to this idiot.

[ Henri Frederic Amiel once said: "To do easily what is difficult for others
is the mark of talent. To do what is impossible for talent is the mark of
genius." ]

It depends on what you want and what your possibilities are. I am under the
impression that this is a access-newsgroup, and hence it would seem logical
to seek solutions in this area. Only if these recources run out, I would
consider to change to -no doubt more powerful- tools such as SQL-server.

Bas Hartkamp.

 

[aaron.kempf]

there isn't a single reason in the world the EVER use ANY Access
without SQL Server.

ADP is completely superior to MDB in every possible manner.

-Aaron
ADP Nationalist

Don't listen to this idiot.

[ Henri Frederic Amiel once said: "To do easily what is difficult for others
is the mark of talent. To do what is impossible for talent is the mark of
genius." ]

It depends on what you want and what your possibilities are. I am under the
impression that this is a access-newsgroup, and hence it would seem logical
to seek solutions in this area. Only if these recources run out, I would
consider to change to -no doubt more powerful- tools such as SQL-server.

Bas Hartkamp.

 

[aaron.kempf]

there isn't a single reason in the world the EVER use ANY Access
without SQL Server.

ADP is completely superior to MDB in every possible manner.

-Aaron
ADP Nationalist

Don't listen to this idiot.

[ Henri Frederic Amiel once said: "To do easily what is difficult for others
is the mark of talent. To do what is impossible for talent is the mark of
genius." ]

It depends on what you want and what your possibilities are. I am under the
impression that this is a access-newsgroup, and hence it would seem logical
to seek solutions in this area. Only if these recources run out, I would
consider to change to -no doubt more powerful- tools such as SQL-server.

Bas Hartkamp.