How many records is ok in Access? (Access vs. SQL).

  • Thread starter Thread starter Niklas Östergren
  • Start date Start date
N

Niklas Östergren

Hi!

How many records i possible to store in access, without getting some
problem?

We are developing a machine with a new control system. In this control
system we have apr. 16 000 parameters wich we would like to store for each
machine (control system).

Ex. if we sell 100 machines we need to store apr. 160 000 records of the
parameters. But must likely we will sell more than 100 machines since we
have had our old control system for more than 15 yers and sold apr. 350
machines during this perid.

I know, at least a little bit, of Access so if this is a good environment
then I could do this, with some help from you guy´s but anyway!

But if not we have to go for a fullscale SQL-database (I guess)? And then
another guy (a consult) have to do this!

What´s your opinion?

TIA!
// Niklas
 
It would depend upon whether or not the parameters were numeric or text.
16,000 * 350 is 5,600,000 records. If the parameters were number that would
be a maximum of 8 bytes or about 45 MB of data, well within Access's
capacity. If they were text, however, they could easily exceed Access's
capacity. Access has a capacity of 2 GB, but I would not get anywhere close
to that without having a highly skilled professional design your database.

I have worked on huge databases, but I prefer to use SQL-Server if they get
above .5 GB, just for comfort's sake in knowing that the database engine has
built in capacity to handle large data stores. Yes, you will need to have a
professional design a SQL-Server database, and you also will need a (at
least a part-time) database administrator. The expense is much higher. You
might also consider starting it in Access and later, if it warrants it,
upsize it to SQL-Server.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Hi!

How many records i possible to store in access, without getting some
problem?

We are developing a machine with a new control system. In this control
system we have apr. 16 000 parameters wich we would like to store for each
machine (control system).

Ex. if we sell 100 machines we need to store apr. 160 000 records of the
parameters. But must likely we will sell more than 100 machines since we
have had our old control system for more than 15 yers and sold apr. 350
machines during this perid.
Click to expand...

A single database is limited to 2 GByte... a LOT of machines. The
largest working Access database I know of has some 20,000,000 records
in the largest table. 160000 records - or 1.6 million records - is
well within Access' capabilities.
 
Hi John!

Thank´s a lot!
There are different types of parameters. Some are text, some are decimal
digits (integer would be fine) and some are binary data 7 bit´s.

We probably start with a access db and, if neccesary, move up to and
SQL-server.

Thank´s a lot for your answer!

// Niklas
 
Thank´s a lot Arvin for your detaild answer. I´ll take a closer look at the
parameters we need to save and see if it´s possible to calculate needed
memory.

I would think that we maby start with an access db and if neccesary move up
to a SQL-Server!

Thanks for taking time!

// Niklas
 
Hi Again!

You wrote:
"...but I would not get anywhere close to that without having a highly
skilled professional design your database."

So what´s the most important to think about then?
Normalizing, Selecting right datatypes, selecting right datasize? What more
to think about?

TIA!
// Niklas
 
We probably start with a access db and, if neccesary, move up to and
SQL-server.
Click to expand...

If you're seriously planning to move to SQL, you might want to
consider implementing the database as an Access Project (.adp file)
using MSDE as the data engine; it comes free with Office (it's not
installed automatically but it's in the SQL folder on the Office CD).
MSDE is a complete version of SQL/Server, with a 2-GByte restriction
on database size and performance "throttled" at 5 concurrent threads.
In practice this does *not* mean that you're limited to five users.

If you're planning to move to SQL, careful Form design is important.
You'll want to consider using VBA code to set the Form's recordsource
to the minimum number of records, set combo rowsources on the fly, and
numerous other techniques to make the server database do most of the
work. There's an overview of these techniques at Tony's website
http://www.granite.ab.ca/access/sqlserverupsizing.htm.
 
With a file based system, such as Access, there are more things to consider:

Number of concurrent editing users. Quality and Speed of your network.
Indexing the correct fields for optimal data return are among the additional
considerations.

Other things to consider are security and disaster recovery, backup
strategies, and scope of usage (hours of use and area, i.e. WAN, LAN, or
Terminal Services)
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Thanks!

Some of this isn´t going to be any problem since we have a network with good
quality and make daly backup of all data and ther´s only going to one user
on this db.

But the rest...!

Thank´s a lot I´ll keep this in mind!

// Niklas
 
Thank´s a lot John!

What does this mean? That a swap to a real SQL-Server doesn´t need any
changes, or?

// Niklas
 
What does this mean? That a swap to a real SQL-Server doesn´t need any
changes, or?
Click to expand...

If you do it in MSDE, it already IS in SQL Server. You'ld just point
the frontend to a new SQL instance; close to no work at all.
 
Doesn't the size of the DB also depend on which version of Access yo
are running. I have seen DBs crash and burn at 70MB in 95 and 97
 
dandgard said:
Doesn't the size of the DB also depend on which version of Access you
are running. I have seen DBs crash and burn at 70MB in 95 and 97.
Click to expand...

Not really. I have a client with a 350 Mb BE in A97. With 15 A97
and 15 A2000 users.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Back
Top