Size Limit / Splitting Data Base

[DSerr77]

I am looking for some help with a database that I use. The database uses 3 large tables and has a few summary queries that pull information from the tables. The problem is that the tables are huge and have brought me to the 2GB size limit. I can no longer compact and repair to fix it. I was wondering if I split the tables into 3 seperate databases and had a fourth that had the queries, if that would solve my size problem. Also, any info on how to go about that would be helpful. Any and all help with this is greatly appreciated.

 

[Golom]

Given no other information than that ... Yes, it will probably give you some temporary relief. I assume that these tables are growing over time so, making the simplistic assumption that each table is about 0.66 GB, you can probably buy some time by splitting and linking.

Personally, I would want to look at moving away from Access to something more industrial strength before you hit the point where a single table starts to consume 2 GB and you then need to start splitting individual tables.

 

[DSerr77]

Thanks for the info. This is a annual AP database that contains twelve months of data and usually does not break the 2 GB size limit. Where can I learn how to link databases as mentioned?

 

[lespaul]

Sounds like you are importing information from your Accounting database to tables in Access. Instead you'll want to right click on Tables and then select Link Tables. Depending on where the information is actually stored, you may need to create an ODBC connection.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[cmmsi]

Feel free to take a look at:

http://support.microsoft.com/kb/304932/en-us

or even access help on database splitting.

Essentially, you can set up a database for each table, and then a single database with connections to each table in each database, so it looks like 1 database.

A better solution would be something more robust like sql server or oracle or similar, and is you are a sql server shop, you can very quickly migrate the tables using data transformation services (

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/dtssql2k.mspx)

Good luck.