Question splitting a database up.

[reddwarf]

Question - splitting a database up.

I have a case management system built in access. it services 8 teams, each team has 10 people. Its working ok and has about 10K records a month I am managaing the back end with weekly Compacts and even with 20K records its about 90meg.

The main query ( taken from the main table ) has about 100 fields. Its pretty big.

what i thinking of doing is spltting it up per team - so each team has its own databse - adding about 1200 per month.

i think this will make it quicker and if one teams goes down ( corrupted ) it doesnt affect the rest. As for reporting, i will make a front ebnd which looks in all the tables using the UNION sql command so it acts like one big table. This will mainly be used out of hours of to cause any problems.

some questions :

1 : if all the tables in eachs team database are called "Tblmain" - can i still do a UNION ALL query or do they have to have different names ?

2 : is there a quicker way to do this raher thana union all ?

I hope this finds it way to an expert !

d

 

[JamesMcS]

You should still be OK, as long as name the linked tables differently (this doesn't affect the table name in the linked-to DB).

To be honest though I would imagine that a properly constructed database with that small a dataset should only take a few seconds to do anything. Are there particular aspects of the DB that are slow?

 

[reddwarf]

Well, the server it is on its slow.

also - the system runs a load of activitys. The Q up these activities i use the a union all querey across 5 different tables, each table has about 10 fields but 10K records. In one office it is Ok and only takes about 5 seconds, in the other office were the server is rubbish it can take aboutr 60 seconds to retrieve the workflows.

the main reason for splitting them up is when it corrupts. Frpm what i can fathom the rubbish server has several drop outs" a day. I tink is causinf my back end to corrupt more frequenetly. On the good server, it has never happened. that sound about right ?

 

[JamesMcS]

That's quite a big difference! Time for the boss to splash out on a new server... I'm so funny...

Well it might help - did you notice that it was much faster when not so full of records then?

If you definitely need to split it out, bearing in mind this will cause more work for you down the line, you should be OK with just renaming the linked tables after you've linked them.