2 gig file limit reach

[kgj]

i have using MS access 2003 for 10 years without a hitch until i reached the 2 gig file limit. I created ms program using wizards so i cant use vb programming codes. Can somebody help me to increase the file size or decrease the file size? I have split the database. Thanks in advance.


kgj

[GinaWhipp]

There is no increasing the file size, 2 gig is it. Have you tried running Compact & Repair?

[CJ_London]

you can't increase file size beyond 2Gb, basic procedure to reduce file size is to compact and repair, although if you have reached the 2Gb limit that won't work in 2003 (will in later versions)

If you have reached the limits, create a new db, then import the tables from your obese db. Archive the obese db and rename your new db to replace it. Can't remember where the import feature is in the 2003 file options, but it will be there somewhere, perhaps under something like external data.

If you are holding close to 2Gb data after compacting, then you have two options. The first is to split your backend into 2 or more databases. Try to keep related tables in the same db so you can maintain relationships.

Alternative is to upsize the backend to sql server - express is free. however not sure 2003 access will talk to current versions of sql server - someone else can perhaps advise on that or you can research online. If you do upsize it will only be effective if your tables are normalised, queries efficient etc. So you may need to make some changes

[kgj]

thanks a lot.
hey guy do you have experience in a 1 is to 1 relation of tables. I was thinking of putting that relationship to increase the file size somehow a backup up table

[CJ_London]

there is rarely a need for 1-1 relationships (data would normally all be in one table) and I wouldn't do it across backends. However you could perhaps archive old data to another backend , then reference it when required.

OLE objects will bloat your db so if you are storing them in your database you could store those in a file and store instead the filename/path in your db.

It would also help if you clarified whether you have taken the advice already provided and if so, what the outcome was