Migration Questions

[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

 

[alvechurchdata]

My apologies if this is a duplicate. I've just posted one response and it's vanished.

If you compact after importing each of the 200 files then you might keep below the 2Gb limit for a few more months whilst you think of a better solution. It will make the import very slow but you've got all night.

I believe that MSDE is also limited to 2Gb but you might find it easier to control bloat. Using MSDE also gives you a very clear upgrade to a full version of SQL.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Burglar]

Thanks for the suggestion but unfortunately, that is not an option since it takes up to 45 minutes to compact the db after importing all of the files and 20 minutes if the db was just compacted...this would take days. I appreciate the response however.

As for the desktop engine, I will look into that, thank you.

Burglar

 

[mp9]

Search Access VBA help for CreateRelation - this will show you how to create relationships in code. Okay, so you have to manually write the code to create all your relationships but once you've done this you can simply run the VBA at the end of each import run.

One thing the VBA Help doesn't emphasise enough is that the code shown uses DAO, so you'll need to set a reference to your DAO library.

Hope this helps.

http://www22.brinkster.com/accessory/

 

[Burglar]

Sweeeeeet!!! This is exactly what I was looking for! Thank you very much mp9

Burglar