By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,107 Members | 1,392 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,107 IT Pros & Developers. It's quick & easy.

Overcoming Access size limitation possibilities?

P: n/a
Hi,

Is there a "way around" the size limitation for Access? I'm thinking
of something like spanning a huge table accross multiple databases...

Something simple and that would preserve the integrity of the DB yet
let me overcome the size limitation.

Is there any best practice or technique?

Thanks,

Lee G.
Jun 27 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
leegold58 wrote:
Hi,

Is there a "way around" the size limitation for Access? I'm thinking
of something like spanning a huge table accross multiple databases...

Something simple and that would preserve the integrity of the DB yet
let me overcome the size limitation.

Is there any best practice or technique?

Thanks,

Lee G.
Exactly what size limitation do you want to overcome?
How big do you need?

Bob
Jun 27 '08 #2

P: n/a
I think if you reached the limits of ms-access, then you should consider
moving the data part to sql server, and continue to use ms-access as the
front end....

I don't recommend a workaround at all.

You don't mention how large, or how many records you have now. So, how many
records do you have now, and how large is your database now? (after a
compact and repair of course!).

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 = 2,147,483,648 Bytes
1 Character = 1 Byte
120 Characters = 120 Bytes

2 gig / 120 = 32 million records

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

In real terms, I don't think anyone
would store close to the actual limits of the file size for ms-access.

So, it really all relative, and you not mentioned how many reords you have
now.
However, a table with 100,000 records in ms-access is rather small...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
Jun 27 '08 #3

P: n/a
"leegold58" <go******@worldpost.comwrote in message
news:30**********************************@s50g2000 hsb.googlegroups.com...
Hi,

Is there a "way around" the size limitation for Access? I'm thinking
of something like spanning a huge table accross multiple databases...

Something simple and that would preserve the integrity of the DB yet
let me overcome the size limitation.

Is there any best practice or technique?
Well IMO a requirement for a "huge table" set alarms ringing wrt the basic
design. Is the data properly normalised and stored in a relational
configuration? If the answer is yes and the tables are still huge then I'd
be thinking about a more capable host eg Oracle.

Regards,
Keith.
www.keithwilby.com

Jun 27 '08 #4

P: n/a
What you want is called partitioning as long as the partitions reside in the
same database. Jet and ACE don't support partitioned tables.

You can't enforce referential integrity on tables that are stored in
different databases. That goes for any database system, not just Access. If
your data exceeds 2 GB, upgrade to SQL Server or Oracle. They have free
versions that store up to 4 GB of data. If you need to store more than that,
purchase an enterprise level database system.

Chris
Microsoft MVP
leegold58 wrote:
>Hi,

Is there a "way around" the size limitation for Access? I'm thinking
of something like spanning a huge table accross multiple databases...

Something simple and that would preserve the integrity of the DB yet
let me overcome the size limitation.

Is there any best practice or technique?

Thanks,

Lee G.
--
Message posted via http://www.accessmonster.com

Jun 27 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.