Database potentially larger than access 2gb size limit

[mmtoure]

I am a novice Access user and would appreciate some tips on the following. I am trying to build a database that could be more than 2GB (I am at 1.9GB, and starting to get loads of errors). I wonder what would be a good strategy to build a large database consisting of multiple access files but being able to run queries (or pivot tables) that retrieve specific data from all of them (keeping in mind the back end data are not in a single table, but the table though have different names are formatted the same way...i.e. same headers, etc.)

[alansidman]

Short of moving to another platform, ie. SQL server, the first thing, I would attempt is to put my tables in different files (other Access db) and then link those tables to the file holding your queries. I have done this on a very limited basis, but it will work. I have done it with stand alone databases and not with a split FE/BE but that should not make a difference so long as they are linked properly.


Edit: Just a thought. When you are in the design and building stage, it is often important to run compact and repairs on your file. This will help to keep the size down because as you create and delete objects, Access does not give you back the formerly used space. A compact and repair fixes this.

[mmtoure]

Hi Alan. Thanks for your response. Just for the purpose of fully getting this, could you please explain what you meant by "properly linked"? Will this be the same as creating relationships between the tables in multiple dbase?

[alansidman]

You got it! All I meant by properly linked was that you use the built in functionality to link the tables to your primary file. Perhaps it was overly descriptive. Don't overlook the Compact and Repair, and you may want to do this daily until your db is in production.

[Kyle123]

I'd normalise it - yours doesn't sound normalised - then stick it in sql server

[mmtoure]

We don't have an sql set up (How easy is it to set up?). As for now, I will have to do without.

EDITS: Also I tried normalizing it using the table analyzer wizard, but I keep on getting errors that the file is corrupted.

[Kyle123]

It's pretty easy - http://www.microsoft.com/en-gb/downl....aspx?id=29062, you'll want to install the management studio as well. There's then a wizard in Access to upsize your database into sql server

[mmtoure]

Thanks. So, if I am on a network, will other users be able to access the sql that is downloaded on one source computer or do we have to have sql downloaded on computers that will be making queries to the stored data?

[Kyle123]

Yes, they will be able to access it without installing anything

[mmtoure]

Thank you Kyle and Alan for your help.