Capacity in Access

[Please Help]

Hello all,

I am planning on having an Access database that stores multiple years of
data. The size of each year is around 600 MB. For example, if I store 10
years worth of data, the database would end up to 6 GB.

Do you guys think Access can handle that capacity? Assuming that I have a
database with a size of 6 GB, do you think it is best to use it as Back-end
or both Front-end and back-end?

Any inputs or suggestions are greatly appreciated.

Thanks.

 

[Rick Brandt]

Hello all,

I am planning on having an Access database that stores multiple years
of data. The size of each year is around 600 MB. For example, if I
store 10 years worth of data, the database would end up to 6 GB.

Do you guys think Access can handle that capacity? Assuming that I
have a database with a size of 6 GB, do you think it is best to use
it as Back-end or both Front-end and back-end?

Any inputs or suggestions are greatly appreciated.

Thanks.

Access files cannot be bigger than 2GB. I would suggest Access for the front
end and a server database for the back end with those kinds of numbers. You
could use a file per year or some such scheme, but that is a recipe for trouble
in my opionion.

 

[Please Help]

Rick,

Thanks for the information.

My intent of having multiple years of data in the same database is to be
able to retrieve any year of data in Excel by using the year as identifier.
Unfortunately, we don't have enough Access licenses for everyone to use.

How would you handle in that kind of situation?

I am using Access 2003. The database has 5 tables, and two of the tables
have over 2 millions of records each.

Thanks.

 

[Arvin Meyer [MVP]]

2 GB is the maximum in Access, and I wouldn't let one get bigger than 1GB if
there are a few users. The only way to get around the front-end Access
license limit is to use either a web front-end, or buy a developer's edition
which will allow you to make a run-time to access the data. If you have more
than 3 or 4 users without licenses, that may be the cheapest solution.

 

[gllincoln]

I get the feeling that cost is a big issue here?

Access isn't going to handle that amount of data in a reasonable manner.

You might consider MS SQL Express Edition -
http://www.microsoft.com/sql/editions/express/default.mspx

It supports up to a 4 gigabyte database. That would offer you 6 years worth
of data, probably 7 if you worked on normalizing what you have and deduping.

Although it is considered to be sort of heresy here in the Microsoft corner
of the world, and it is also sometimes not so easy to get it to play nice
with Microsoft products like Access and Excel - MySQL community version
might be an alternative for you to explore. It can handle the amount of
data. It's not as fast as MS SQL Server and it doesn't have nearly as rich
of a feature set, but it does work.

Hope this helps...

Gordon

 

[Arvin Meyer [MVP]]

I think that SQL-Express may be the better idea. Not only faster, but easier
to backup and maintain. Archiving data would probably bring the size
requirement down to a more manageable level as well. Four or 5 years from
now conditions may change, allowing one to purchase SQL-Server, or switch to
MySQL at that time. Who knows, the MySQL product could be improved by then.

 

[Please Help]

Good morning guys,

Thank you all very much for those information.

Believe or not, we are using the SQL server. So I don't think I will need
MySQL. The problem is I don't know how to use the SQL server with Access.
Can you guys point me to sources or guide me through how to use SQL server
with Access?

Thanks.

 

[David W. Fenton]

I think that SQL-Express may be the better idea.

Er, didn't he say he had 6GBs of data? And didn't someone else say
SQL Express was limited to 4GBs?

 

[Tom Wickerath]

Consider investing in a copy of the book titled "Microsoft Access Developer's
Guide to SQL Server", written by Mary Chipman and Andy Baron (SAMS
Publishing):

http://www.amazon.com/dp/0672319446

Also, try this free resource:

SQL Server White Papers: Migration from Oracle Sybase, or Microsoft Access
to Microsoft SQL Server
http://www.microsoft.com/downloads/...dff9-49cd-8ea7-581aa7a303a4&displaylang=en&tm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Arvin Meyer [MVP]]

Absolutely. He also said that he had a 10 expectancy to get 6 GB of data,
and I thought archiving and.or using SQL-Express would give him at least 5
years, during which time, MySQL may improve, or his economic condition may
allow him to move forward with a full version of SQL-Server.

My reasoning was (and is) that SQL-Server currently is so much better than
MySQL, it is worth the possible extra work 5 years from now.

Wasn't that clear from my answer? If not, I apologize.

 

[Arvin Meyer [MVP]]

There is also another book by Mary Chipman and John Viescas which is out of
date and probably out of print, but still well worth it if you can find it.
It looks like you can get it used at Amazon:

http://www.amazon.com/Access-SQL-Se...=sr_1_8?ie=UTF8&s=books&qid=1203304295&sr=8-8

 

[Please Help]

Hi Mikal,

We are using the full version of SQL Server 2000. Currently, the database
is only 600 MB and has only one year worth of data. We have planned on
accumulating more data to the database.

What would you suggest?

Thanks.

 

[Please Help]

Thanks guys for providing those resources.

I am new to the whole thing about attaching Access to the SQL server. Do
you guys think these resources are easy to understand to get started?

Thanks again.

 

[John W. Vinson]

We are using the full version of SQL Server 2000. Currently, the database
is only 600 MB and has only one year worth of data. We have planned on
accumulating more data to the database.

What would you suggest?

By all means, use Access as a frontend to your SQL/Server database. The
various resources listed should help you through the process, but - if you
design your forms correctly - it really is very little different from a .mdb
backend for your tables.

 

[David W. Fenton]

He also said that he had a 10 expectancy to get 6 GB of data,
and I thought archiving and.or using SQL-Express would give him at
least 5 years, during which time, MySQL may improve, or his
economic condition may allow him to move forward with a full
version of SQL-Server.

My reasoning was (and is) that SQL-Server currently is so much
better than MySQL, it is worth the possible extra work 5 years
from now.

Wasn't that clear from my answer? If not, I apologize.

I missed the part of the 10-year plan.

MySQL with InnoDB tables is much, much better than with MYISAM
tables. The only major thing you lose is full-text indexing, which
is very seldom an issue in non-web-based apps (i.e., the kind you'd
be developing with an Access front end).

Another free alternative that is better than MySQL is PostgreSQL.

 

[David W. Fenton]

The
various resources listed should help you through the process, but
- if you design your forms correctly - it really is very little
different from a .mdb backend for your tables.

Huh. You see, I have been designing all my my Jet-based apps with
upsizing in mind since, oh, 1998, so really, there's very little
that needs to be changed.

The point is: designing for efficiency in SQL Server is also very
efficient with a Jet back end.

 

[Arvin Meyer [MVP]]

I missed the part of the 10-year plan.

MySQL with InnoDB tables is much, much better than with MYISAM
tables. The only major thing you lose is full-text indexing, which
is very seldom an issue in non-web-based apps (i.e., the kind you'd
be developing with an Access front end).

Another free alternative that is better than MySQL is PostgreSQL.

 

[Arvin Meyer [MVP]]

I missed the part of the 10-year plan.

Turns out that he already is using full SQL-Server, so he needs to stay
where he's at, until the SQL-Server version he has requires upgrading.