Exporting Relationships

[Burglar]

Hey all. I am using Access 2000 and have a database that imports 200+ flat files every night into it. The database has 30 tables and 40+ relationships and is fully normalized. There are a total of 2.5 million rows across all tables and the size has just grown to 2GB after the nightly import and when packed, 1GB. My problem is access 2000 has a file size limit of 2GB so if the DB bloats to that point during import, all subsequent transactions fail.

You may be asking "why use access with such a large amount of data?" The answer is, the organization does not want to spend the money for SqlServer app and licenses. My solution is to break the db into smaller BE dbs based on category of table(s), and link all of the tables into the FE db for users. Here is my problem:

How do I replicate the relationships in the FE db without having to redifine them manually? Any help on this would be greatly appreciated.


Burglar

 

[willir]

Ouch! You are really pushing Access to it's limits.

You can
- create tables, indexes and relationships via code
- import relationships and table definitions but not data

You can also consider moving to MySQL

Richard

 

[Burglar]

Thanks for the suggestion but I got a reply from another forum that solved the problem...btw, the app is up to 4.5 million rows now, talk about pushing access to its limits