Consolidate three remote databases

[ahg]

Hi everyone,

I have a client who operates from three locations 100 miles apart. At each location they have an identical Access 2k database which receives and inputs local data and is dealt with locally. The main one has about 150,000 records, the other two each have about 35,000 records. They now want to consolidate all db's into one located at the main site so it will hold all 220,000 - and that's growing daily. The other two sites will be required to send any new data to the main site for inputting but are to retain read-only ability of the data for their area.

Using Data Access pages doesn't seem to be feasible for this volume so I am looking for any help and/or ideas which may help me to advise the client. They're a non-profit group so needless to say they don't have much in the way of financial resources - but enough to look seriously at the project as a long term centralized and efficiency project.

I know there is so much more experience out there than I have so would really appreciate any ideas/help/suggestions from anyone.

AHG

 

[Francescina]

In general terms: 220.000 records is not a big quantity for ACCESS, I am working with tables containing more than 1.5 million records.
If the MDB is working with 150.000 records this is a very good sign concerning its design so do not be afraid to accumulate their data.
If you want to have smaller tables for some reason it is a very good solution to use several tables with the same structure and link them with queries.
Nethertheless, if the organisation wants to spend some money (in exchange to performance and stability) then you might invest in MS SQL server or Oracle.