Huge Database Advice

[rifraf47@yahoo.com]

My office of 6 folks want to put together a large database (over a
million records) and hit it often with querries. Can someone offer up
some advice on how to go about this? These are options I've thought of.

(1) Set aside a dedicated computer to house the MS-ACCESS database.
When someone wants to use it they go over and do so, perhaps exporting
a querry result to Excel, putting it on the network and going back to
their computer to retrieve it.

(2) Put the MS-ACCESS database on the network's server. People open the
database file and goal. (Is this feasable with a million plus records?)

(3) Use another database. I've read some on MySQL as a possible route.
But I'm not sure what this would entail. In my readings there was a
bunch of talk about configuring a server... So I got the feeling this
isn't exactly a similar solution to MS-ACCESS, is it? (i.e. we're no
longer talking about a file sitting somewhere that users double click
to launch - mySQL we're talking about a front/end back/end type thing
requring extensive programming?)

Also, related... is there any shareware program that helps in the
design and layout of a large database? We've got a bunch of papers with
lines connecting them and notes and...
Thanks for your time and advice,
Paul./

[Mike Turco]

Re: Huge Database Advice



<rifraf47@yahoo .com> wrote in message
news:1107194140 .500249.274970@ c13g2000cwb.goo glegroups.com.. .[color=blue]
> My office of 6 folks want to put together a large database (over a
> million records) and hit it often with querries.[/color]

Access is limited to a 2 gig file size, which for a million records is
cutting it too close. You're going to have to go with MySql, MS SQL or some
such thing. You can still build the "front end" (user interface) in Access.
You'll need to setup software on a server, and you'll need to go with a
client/server type system (as you feared). This is beyond the scope of a "do
it yourself" project.

[Arno R]

Re: Huge Database Advice

[color=blue]
> Access is limited to a 2 gig file size, which for a million records is
> cutting it too close. `[/color]

This will depend (how many fields, what kind of fields)
I have a db here with 460.000 records in one table and the same table cut in various tables (Thus a
total of almost 1.000.000 records).
This database is only 46 Mb ... so there is a long way to go to 2Gb

If you have more large tables then you could (if needed) put them in separate db's and link to them.
So I think the size is *not* the limit here.
IME Access (even version 2.0) is very well capable of handling a million records, though certain
queries will run slow.
It all depends very much on decent indexing.

HTH
Arno R