Overcoming Access size limitation possibilities?

[leegold58]

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.

[Bob Alston]

Re: Overcoming Access size limitation possibilities?

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

[Albert D. Kallal]

Re: Overcoming Access size limitation possibilities?

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...th en 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
pleaseNOOSpamKa llal@msn.com

[Keith Wilby]

Re: Overcoming Access size limitation possibilities?

"leegold58" <goldtech@world post.comwrote in message
news:301222ed-b99f-4aa3-b46e-a64723d6592d@s5 0g2000hsb.googl egroups.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.

https://www.keithwilby.com

[Chris O'C via AccessMonster.com]

Re: Overcoming Access size limitation possibilities?

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