what's the limit of Access?

[Bob]

Hi,

i developped a webapplication using ASP/ADO with Access XP as database.
i foresee that the main table will reach 10000 records (recordlength=100
bytes).

1) what's the reasonable limit (amount of rcords) before the application
will become too slow and that MS sqlserver would be more appropriated?
2) there is a historical table into the database which gets every night the
obsoleted records of the day before. That table will inflate every day and
will become very huge, but the webapplication never uses that table. Will
the size of the global database (caused by that table) has an influence on
the performance of the application?

Thanks
Bob

 

[Rick Brandt]

Hi,

i developped a webapplication using ASP/ADO with Access XP as database.
i foresee that the main table will reach 10000 records (recordlength=100
bytes).

1) what's the reasonable limit (amount of rcords) before the application
will become too slow and that MS sqlserver would be more appropriated?
2) there is a historical table into the database which gets every night the
obsoleted records of the day before. That table will inflate every day and
will become very huge, but the webapplication never uses that table. Will
the size of the global database (caused by that table) has an influence on
the performance of the application?

A) You're describing a "tiny" amount of records for just about any database
(including Access).

B) Access (Jet actually) limitations compared to SQL Server have WAY more
to do with number of concurrent connections and security. Size will be the
least of your concerns.

 

[M Skabialka]

I looked up specifications in Help:
Microsoft Access database general specifications
Attribute Maximum
Microsoft Access database (.mdb) file size 2 gigabytes. However,
because your database can include linked tables in other files, its total
size is limited only by available storage capacity.
Number of objects in a database 32,768
Modules (including forms and reports with the HasModule property set
to True) 1,000
Number of characters in an object name 64
Number of characters in a password 14
Number of characters in a user name or group name 20
Number of concurrent users 255

 

[Randy]

Hi,

i developped a webapplication using ASP/ADO with Access XP as database.
i foresee that the main table will reach 10000 records (recordlength=100
bytes).

1) what's the reasonable limit (amount of rcords) before the application
will become too slow and that MS sqlserver would be more appropriated?
2) there is a historical table into the database which gets every night
the
obsoleted records of the day before. That table will inflate every day and
will become very huge, but the webapplication never uses that table. Will
the size of the global database (caused by that table) has an influence on
the performance of the application?

Thanks
Bob

Bob,

Microsoft Access limits are 1 or 2 million records (depending on the version
of Access you are using). You should have no-problems if you built the
tables correctly, that is: not use of redundant, duplicated data. Use of
primary keys, indexes, etc.

Microsoft Access performs really fast (even faster than SQL Server). The
performance based on size is not an issue. It is like RAM memory; it doesn't
matter how much you got, the speed to locate a byte in a lower memory
address is the same as the speed to locate a byte in the highest memory
address. To locate any byte or address in a vast memory area, the computer
just "goes" there. It doesn't matter if it is at the beginning or the at the
end. The issue is the performance based on your design (keys, indexes, data
redundancy, etc.).

If there will be tons of tons of transactions with this database, you should
only worry about running maintenance on it once in a while. That is backing
it up and compacting it, which will re-build indexes, reset continuity of
Autonumbers, resize the database, etc. It will be on top condition.

-Randy

 

[Fred]

Thanks all

Bob,

Microsoft Access limits are 1 or 2 million records (depending on the version
of Access you are using). You should have no-problems if you built the
tables correctly, that is: not use of redundant, duplicated data. Use of
primary keys, indexes, etc.

Microsoft Access performs really fast (even faster than SQL Server). The
performance based on size is not an issue. It is like RAM memory; it doesn't
matter how much you got, the speed to locate a byte in a lower memory
address is the same as the speed to locate a byte in the highest memory
address. To locate any byte or address in a vast memory area, the computer
just "goes" there. It doesn't matter if it is at the beginning or the at the
end. The issue is the performance based on your design (keys, indexes, data
redundancy, etc.).

If there will be tons of tons of transactions with this database, you should
only worry about running maintenance on it once in a while. That is backing
it up and compacting it, which will re-build indexes, reset continuity of
Autonumbers, resize the database, etc. It will be on top condition.

-Randy

 

[John Vinson]

Microsoft Access limits are 1 or 2 million records (depending on the version
of Access you are using).

Actually the limit is on database *size* - 1 GByte for Access97 and
before, 2 GByte for 2000 and later. There is no explicit limit on the
number of records; I'm aware of some Access applications with
20,000,000 rows in the largest table.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Dirk Goldgar]

Hi,

i developped a webapplication using ASP/ADO with Access XP as
database.
i foresee that the main table will reach 10000 records
(recordlength=100 bytes).

1) what's the reasonable limit (amount of rcords) before the
application will become too slow and that MS sqlserver would be more
appropriated?

As others have said, with a 2GB filesize limit, what you foresee is not
a problem.

2) there is a historical table into the database which
gets every night the obsoleted records of the day before. That table
will inflate every day and will become very huge, but the
webapplication never uses that table. Will the size of the global
database (caused by that table) has an influence on the performance
of the application?

No, but you may want to put that historical table into a separate .mdb
file. Then it will never be an issue in the application database.

 

[Randy]

Actually the limit is on database *size* - 1 GByte for Access97 and
before, 2 GByte for 2000 and later. There is no explicit limit on the
number of records; I'm aware of some Access applications with
20,000,000 rows in the largest table.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

Ooops. Yes, I meant "GB" no "records." Thanks for clarifying.

-Randy