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.

 

[Mikal via AccessMonster.com]

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?

I think you can put your back end in something like MySQL which is both free
and not limited to any size you are ever likely to exceed. And then link
Access to the MySQL tables. SQL Server would be the MS choice of backend,
but the free express edition is limited to 4GB (I think) and the unlimited
editions cost money which, since you are elsewhere concerned about the number
of Access licenses you have, it doesn't sound like you want to spend.

HTH

Mike

 

[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

 

[Mikal via AccessMonster.com]

Good morning guys,

Thank you all very much for those information.

Believe or not, we are using the SQL server

How much data do you already have? Are you near the 4GB limit on SQL Express?
Are you actually using SQL Express (or maybe MSDE) or are you using an
unlimited edition of SQL Server? If you are using one of the unlimited
ediions of SQL Server then the 2GB limit on Access Jet or the 4 GB limit on
SQL Server Express are not relevant.

Mike

 

[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.

 

[Mikal via AccessMonster.com]

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.

In that case, I think the resources provided by Arvin Meyer and Tom Wickerath
above ought to give you all you need. If I remember right, SQL Server 2000
has a limit of something over a million Terabytes of data so it's essentially
unlimited as far as just about anybody is concerned.

Mike

--
Mike Pippins

"We have met the enemy and he is us." -- Pogo Possum

Message posted via AccessMonster.com

 

[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.

 

[Arvin Meyer [MVP]]

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?

Use Access as a front-end, and stay where you are with SQL-Server until you
find some reason to upgrade the back-end. Eventually, there will be some
reason that you'll find an upgrade desirable, or even necessary.

 

[Please Help]

Thanks again to all of you for these precious information. I will look into
those resources.

 

[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.