Allowing Access to exceed 2 Gigabytes.

[Guest]

Is there a way to allow an Access file to be larger than 2 Gigabytes?

 

[Jeff Boyce]

No.

But if you absolutely positively have to store more than 2 Gbytes, you could
"break apart" your data into separate Access .mdb files, each with a limit
of 2 Gbytes. Then you'd link to the tables in those (multiple) .mdb files
from a "front-end". There are other issues related to this approach.

Is there a reason you are not considering something with larger capacity for
the back-end data store?

 

[Albert D.Kallal]

If you are exceeding the capacity of 2 gigs...then something is likely
wrong!!

You can use multiple back ends, and link to them from a front end database.
This would give you close to un-limited storage.

The size limit of access 2002 is 2 gigs.

So, if you take a average name in a mailing list, add a few extra
fields...your record size might be about 120 characters on average in size.

So, the number of records you can store is:

2 gig / 120 = 32 million records

So, the limit is NOT the number of records..but "how many" records you can
fit into 2 gig.

Of course, if each customer name only takes up 60 characters...then you can
store 64 million records in the file.

So, unless you are storing millions and millions of records..the limit
should not be a problem at all....

 

[Guest]

As Jeff and Albert said, 2GB is the limit of an MDB file; however, you can
create different backend database files and link them.

Is this a real problem or a hypothetical question? If a real problem, make
a good copy of the database. Then run Compact and Repair on it. See if that
brings down the file size. Better yet, get a copy of JetComp 4.0 and use it.

Also if you are storing graphic files such as bitmaps inside the database,
consider using hyperlinks and external files instead. Access does a terrible
job of storing graphic files which sometimes makes each file 2 to 20 times
larger than the original.

You may also have a problem with using temporary files to hold data for
reports or manipulating data. Sometimes this requires a rethink and rebuild
of the database.

If a hypothetical question, how do you know that you may need more than 2GB
file size. That's a lot of records. We're talking millions here. If you do
have that much data, you might need a more industrial strength database such
as SQL Server or Oracle.

 

[Guest]

Thank you Jerry.

The database is over 3.8 Million records, but since I am limited on funds
and time, another program is not an option. I will break it apart and have a
front end link file.

I kept running into an error message that prevented doing any additional
queries, that is when I realized I was exceeding somekind of limit. I had
already compacted and repaired and there is only text in the records.

Thank you for the assistance,
Michael

 

[Guest]

Thank you Jeff.

I will break up the file. What are the drawbacks? Due to financial and
time constraints, I will stick with access.

Michael

 

[Guest]

Dear Albert,

Nothing is wrong, I have over 3.8 Million records. I am going to break it
up into multiple back ends.

Thank you,
Michael

 

[Guest]

Hi Michael,

I just add a few thoughts here. First, check the data types of the fields to
make sure that they are reasonable for the data contained in each record. For
example, if a byte will do, don't use a long integer or double to store the
same data. For text and memo fields, you can recover some space after
compacting by setting the Unicode Compression property to Yes, if it is
currently set to No.

Verify that the database design is reasonable. Perhaps there is lots of
repeated data where one could save some space by putting the unique values
into a separate table, and then using a numeric PK / FK combination to tie
the values together.

One of the major disadvantages of using multiple linked back-end files is
that there is no way to enforce referential integrity between records in one
linked database to records in a second linked database.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

The database is over 3.8 Million records, but since I am limited on funds
and time, another program is not an option. I will break it apart and have a
front end link file.

I kept running into an error message that prevented doing any additional
queries, that is when I realized I was exceeding somekind of limit. I had
already compacted and repaired and there is only text in the records.

Thank you for the assistance,
Michael

 

[Jeff Boyce]

Michael

Tom W's reply mentioned the major drawback, in my opinion. Access can
enforce referential integrity among tables, BUT only if the tables reside
within the same .mdb file. Breaking the data apart into separate .mdbs
means you are entirely responsible for designing, developing and maintaining
your own, home-grown referential integrity validation.

I'll echo some of the other suggestions/questions in this thread. It may be
that your data structure would benefit (i.e., get smaller!) from further
normalization. Consider posting to the tablesdbdesign newsgroup if this is
an option.

 

[Guest]

Thank you Tom,

I had already made the fields a minimal length and I will have to check on
the Compression property. I can split the table into distict districts that
have no overlapping data.

I am the only current user and when I have other users, they will only use
their district, so that should maintain the ability to merge the smaller
databases.

Michael

 

[Guest]

Thank you Jeff,

I replied to Toms response. I have a short term solution and if I keep at
this project, I will see how it works and I may have to change programs for a
longer term solution.

Michael