Access upper limit

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have heard that the maximum amount of data that Access can handle is about
a million records. I have a couple questions with regard to this.

1) Is that just for one database? Can I make several separate databases and
tie them together to get around that upper boundary?

2) If I am relatively skilled at using Access which is mostly a GUI for a
database, how will those skills transfer to using say SQL Server?

The reason I am asking is we are considering housing our own analytical data
in the office. Historical as well as future. I did a test on one site and
gathered about 4 years worth of data, which equaled about 7,500 hundred
records. We have 179 sites with data going back for almost 20 years. Is
Access up to this task or should I push to get a more robust database program?
 
I have heard that the maximum amount of data that Access can handle is
about
a million records. I have a couple questions with regard to this.
Click to expand...

Wherever you heard that you got bad information. All the limits for Access
can be found in the help file under Access Specifications. Basically, the
most important consideration is the 2 gigabyte file size.
1) Is that just for one database? Can I make several separate databases
and
tie them together to get around that upper boundary?
Click to expand...

The file size limit is just for one database and, yes, you can use separate
databases, but bear in mind that you cannot enforce referential integrity
across different databases.
2) If I am relatively skilled at using Access which is mostly a GUI for a
database, how will those skills transfer to using say SQL Server?
Click to expand...

Your skills will serve you well, but you will need to add some SQL Server
knowledge to that.
The reason I am asking is we are considering housing our own analytical
data
in the office. Historical as well as future. I did a test on one site and
gathered about 4 years worth of data, which equaled about 7,500 hundred
records. We have 179 sites with data going back for almost 20 years. Is
Access up to this task or should I push to get a more robust database
program?
Click to expand...

Depending on how your database is built, the number of records you mention
will probably not be a problem for Access.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
 
Dear Larry:

There are pros and cons to this discussion. As someone who has "made this
switch" I can offer you some observations.

First, SQL Server is NOT a replacement for Access. SQL Server does not
include the "front end" components that Access does. You could certainly
continue to use Access for 2/3 of the work. Only the tables and queries,
with subsidiary functions like data rules and relationships, would move to
SQL Server. Access works well with SQL Server to provide forms and reports,
which are a lot of the effort you'll be expending. Indeed, this marriage is
sufficiently successful that Access ships with the MSDE database engine,
which is "full scale SQL Server" in terms of function, but not capacity.
(SQL Server reportiong and several other peripheral functions are not
included. Buy Developer Edition of SQL Server by all means!)

So, what do you get? What are the advantages of this change?

SQL Server capacities, such as Stored Procedures and User Defined Functons
integrate with your Access application (front end) very well. I have had
spectacular results with this. In one case, I replaced a module that
manipulated my data in Access, requiring 30 minutes, with a UDF that did the
same thing in 2 seconds. Now that kind of thing requires some quite
advanced analysis and programming, but I credit SQL Server with providing
superb tools that permit that kind of thing to happen. I would say I am
amazed at what SQL Server has accomplished, not what I did. But you'll
probably need to work with it a year or two before you would start enjoying
such benefits, unless you're tapping into something someone else has
developed and published. There's a lot of that, too!

SQL Server MSDE gives you an upgrade path to virtually unlimited database
size and performance. Having multiple processors and even multiple servers
working together to provide you with more and more capacity and performance
is a potential huge advantage.

I could go on and on.

The disadvantages are mostly that there is a lot to learn to be able to take
advantage of all this. How quickly you will begin to be able to take
advantage of this depends on your current level, experience, capability, and
the effort you put into it. It's not for everybody. It's not for the
"typical" Access novice, nor for many intermediates. It is a huge step for
a good, professional database programmer who is ready for more advanced
tools to learn, with the corresponding rewards.

The product is reliable, stable, and highly productive in the right hands.
If you're ambitious and competent for such a challenge, the cost is small to
get started. Buy some books, Developer Edition, and dig in. If the effort
is too much right now, put it on the shelf. You may be drawn back to it
later. $200 may get you started, plus a weeks worth of time to get into it.

Good luck. Let me know how it goes.

Tom Ellison
 
It seems to me that if I have a separate database JUST for the analytical
data that it would be nearly impossible for me to approach the limit then, I
mean I know that eventually I would, but in the short term Access will serve
its purpose. I mean at some point I would like to learn SQL Server but right
now I am a full time student and work full time so I do not have a lot of
free time to throw into learning a new tool. I have a copy of SQL 2005 that I
got at the roll-out from Microsoft (the freebie version) that I will probably
play with this summer when I am not in school. Thanks for your insight - both
of you!
--
Never give up, the answer IS out there, it just takes a while to find it
sometimes!


Lynn Trapp said:
Larry,
Let me add that I agree 100% with Tom Ellison's points. He covers the
question from a different perspective than I do but he says it so well.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
Click to expand...
 
Dear Lynn:

Your post had not yet appeared on my system when I started my reply. Being
8 minutes apart, and given the length of my reply this is no surprise.

Your point about DRI being the disadvantage of splitting data between
databases is EXTREMELY COGENT (right on!). I have a crazy client who won't
use MSDE because they have more than 2 GB of data. But much of it is
historical, and each year's history only needs internal RI (referential
integrity). There's no RI between different years, hence no need to keep it
all in one database. But they have some incompetent technical people from
seeing this kind of thing, so they've been on a 3 year long search for some
"free" database that does everything they want, and does it well.

OK, so I blew off some steam.

But your answer is in strong agreement with my position, and I appreciate
it!

Well, when you "agree 100% with Tom Ellison" I guess I can't complain!

My intention is to also "scratch your back". Between the two of us, we have
covered many important and differing aspects, without any disagreement.
Now, without communicaiton between us, that's hard to do!

I would add that the database in which I experienced the 30 minutes to 2
seconds improvement was under 20 megabytes. You don't have to require great
size to experience some tremendous advantages from SQL Server.

Tom Ellison


Lynn Trapp said:
Larry,
Let me add that I agree 100% with Tom Ellison's points. He covers the
question from a different perspective than I do but he says it so well.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
Click to expand...
 
Larry,
You are very welcome. Best wishes with your project and feel free to post
back if you have any questions.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html



Larry G. said:
It seems to me that if I have a separate database JUST for the analytical
data that it would be nearly impossible for me to approach the limit then,
I
mean I know that eventually I would, but in the short term Access will
serve
its purpose. I mean at some point I would like to learn SQL Server but
right
now I am a full time student and work full time so I do not have a lot of
free time to throw into learning a new tool. I have a copy of SQL 2005
that I
got at the roll-out from Microsoft (the freebie version) that I will
probably
play with this summer when I am not in school. Thanks for your insight -
both
of you!
Click to expand...
 
So Tom if I continue to use Access to store the data would it be prudent then
to say store all historical data in one database then all 2006 data in
another and so forth. Linking can be accomplished whenever I run a query
right? I mean if I want to get all the Benzene results for a particular well
over the lifetime of the data all I have to do is link the tables and then
create a relationship in the query? I have done this before and usually get
the right results.
--
Never give up, the answer IS out there, it just takes a while to find it
sometimes!


Tom Ellison said:
Dear Lynn:

Your post had not yet appeared on my system when I started my reply. Being
8 minutes apart, and given the length of my reply this is no surprise.

Your point about DRI being the disadvantage of splitting data between
databases is EXTREMELY COGENT (right on!). I have a crazy client who won't
use MSDE because they have more than 2 GB of data. But much of it is
historical, and each year's history only needs internal RI (referential
integrity). There's no RI between different years, hence no need to keep it
all in one database. But they have some incompetent technical people from
seeing this kind of thing, so they've been on a 3 year long search for some
"free" database that does everything they want, and does it well.

OK, so I blew off some steam.

But your answer is in strong agreement with my position, and I appreciate
it!

Well, when you "agree 100% with Tom Ellison" I guess I can't complain!

My intention is to also "scratch your back". Between the two of us, we have
covered many important and differing aspects, without any disagreement.
Now, without communicaiton between us, that's hard to do!

I would add that the database in which I experienced the 30 minutes to 2
seconds improvement was under 20 megabytes. You don't have to require great
size to experience some tremendous advantages from SQL Server.

Tom Ellison
Click to expand...
 
Dear Larry:

If you have a query that must access ALL the consecutive annual tables, then
you would need a UNION of all these tables. It is doable, but would be a
bit extensive. The code would change dynamically each year.

Before making up your mind, a study of the space needed to record all this
data would be in order. Consider carefully using minimally sized datatypes.

You said, "7,500 hundred records". That's confusing. is that 7500 records,
or 750000 records?

If this is 2000 records per year per site, and 200 sites (round up, it may
grow) that's 400,000 rows per year. You may not be able to store 10 years.
Budget to have a larger database by then. SQL Express is currently at 4 GB.
If you were to start with this, and budget full SQL Server, say, 5 years
from now, you would not face any significant likelihood of problems.

Tom Ellison
 
"the maximum amount of data that Access can handle is about
a million records"

I wanted an answer to this question so I wrote a loop that appended a long
integer field to an Access 97 table incremeting by 1 with each added record.
I ran the program just before leaving for a weekend. Upon my return I found
about 114,000,000 records and an error message that max MDE size had been
reached.

The limit is solely a function of bytes available in the MDB file broken
down into objects and data and other glop that fills up an MDB..

The power of Access is widely underestimated!
 
LOL - 7500 records :-p I am such a goofball when it comes to typing - not
very good at it! My fingers always type what my brain is thinking. I was
thinking 75 hundred.
 
Dear Larry:

After 30 years of programming, my typing skills are at least "up to date" if
not fairly decent. But I guarantee you, I proof read most of what I do here
for spelling, correctness, clarity, and technical detail. May I suggest
there's not substitute for that.

So, OK, where is this going? Can you respond to my suggestions?

Tom Ellison
 
LOL - 7500 records :-p I am such a goofball when it comes to typing - not
very good at it! My fingers always type what my brain is thinking. I was
thinking 75 hundred.
Click to expand...

You're good with Access for at least 1000 years' worth of data.

I'm aware of production Access databases with 20,000,000 records in
the largest table.

John W. Vinson[MVP]
 
Hey John:

Earlier we learned this is 7500 per site, with on the order of 200 sites. I
had said 10 years earlier, so after factoring in the 200 or so sites, were
in the same order of magnitude.

I just want to try to make sure Rolls is not misled about the difference
between my estimate and yours.

And a good day to you! : )

Tom Ellison
 
Earlier we learned this is 7500 per site, with on the order of 200 sites. I
had said 10 years earlier, so after factoring in the 200 or so sites, were
in the same order of magnitude.

I just want to try to make sure Rolls is not misled about the difference
between my estimate and yours.
Click to expand...

oops! Thanks Tom, I did miss that.

John W. Vinson[MVP]
 
1) Is that just for one database? Can I make several separate databases and
tie them together to get around that upper boundary?
Click to expand...

Let me add this. The largest Access database that I've ever seen was built
in Access 2.0 (1 GB data limit) which consisted of 79 tables, each in its
own database with 200 to 800 MB of data in each one. It was a total of over
30 GB and it ran fine for years (it may be still running, for all I know).

Now let me also add that this was a magnificent kluge. Sure it worked, but
why bother? There was no reason not to use SQL-Server. The money that the
company saved in licenses they spent in maintenance. With SQL-Server, it
would have run faster and been easier to maintain. It was neat that Access
could run this file, but "neat" does not run a company, good sense does.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
Dear Tom:

I read your reply and I have a question. I bought the Microsoft SQL Server
2000 Personal Edition CD. I installed it on my computer. Now I cannjot figure
out where to even begin using the program and how to get it to work with
Access.

Do you have any suggestions on any books or other resources I can use to get
started learning what to do?

Thanks
Mark



Tom Ellison said:
Dear Larry:

There are pros and cons to this discussion. As someone who has "made this
switch" I can offer you some observations.

First, SQL Server is NOT a replacement for Access. SQL Server does not
include the "front end" components that Access does. You could certainly
continue to use Access for 2/3 of the work. Only the tables and queries,
with subsidiary functions like data rules and relationships, would move to
SQL Server. Access works well with SQL Server to provide forms and reports,
which are a lot of the effort you'll be expending. Indeed, this marriage is
sufficiently successful that Access ships with the MSDE database engine,
which is "full scale SQL Server" in terms of function, but not capacity.
(SQL Server reportiong and several other peripheral functions are not
included. Buy Developer Edition of SQL Server by all means!)

So, what do you get? What are the advantages of this change?

SQL Server capacities, such as Stored Procedures and User Defined Functons
integrate with your Access application (front end) very well. I have had
spectacular results with this. In one case, I replaced a module that
manipulated my data in Access, requiring 30 minutes, with a UDF that did the
same thing in 2 seconds. Now that kind of thing requires some quite
advanced analysis and programming, but I credit SQL Server with providing
superb tools that permit that kind of thing to happen. I would say I am
amazed at what SQL Server has accomplished, not what I did. But you'll
probably need to work with it a year or two before you would start enjoying
such benefits, unless you're tapping into something someone else has
developed and published. There's a lot of that, too!

SQL Server MSDE gives you an upgrade path to virtually unlimited database
size and performance. Having multiple processors and even multiple servers
working together to provide you with more and more capacity and performance
is a potential huge advantage.

I could go on and on.

The disadvantages are mostly that there is a lot to learn to be able to take
advantage of all this. How quickly you will begin to be able to take
advantage of this depends on your current level, experience, capability, and
the effort you put into it. It's not for everybody. It's not for the
"typical" Access novice, nor for many intermediates. It is a huge step for
a good, professional database programmer who is ready for more advanced
tools to learn, with the corresponding rewards.

The product is reliable, stable, and highly productive in the right hands.
If you're ambitious and competent for such a challenge, the cost is small to
get started. Buy some books, Developer Edition, and dig in. If the effort
is too much right now, put it on the shelf. You may be drawn back to it
later. $200 may get you started, plus a weeks worth of time to get into it.

Good luck. Let me know how it goes.

Tom Ellison
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Record-Locking remains open after closing 0
AutoNumber Field Limits - NOT "How do I reset?" 11
MS Access Job Hunter 2
News Biometric Data leak reveals over 27m records 1
Hitting the limits with large table in Excel containing 1 millionrecords. What is the best option fo 2
Coverting Upper Case to Lower Case 1
HELP A NEWBIE - EXCEL TO ACCESS 5
Warning message Access 2007 8

Back
Top