[digioz]

I got this excellent question from "airprince" through a PM message, and I thought it would be good to share it on the forum publically:


Hello,

Microsoft Access by design was not designed to be used for such large size storage of data or to keep a large number of records. By default, there is a 2GB maximum file size on a mdb file, and a 32768 maximum number of objects that can be stored in it. If you are thinking of having 100,000 records in a ms access database, I would recommend against it.

Here are some more interesting statistics for your information:

Attribute - Maximum
-----------------------------------------
Number of characters in a table name: 64
Number of characters in a field name: 64
Number of fields in a table: 255
Number of open tables: 2048 - Actual max is less, since access opens some by default
Table size: 1 gigabyte
Number of characters in a Text field: 255
Number of characters in a Memo field: 65,535 when entering data through user interface; 1 gigabyte when entering data programmatically.
Size of an OLE Object field: 1 gigabyte
Number of indexes in a table: 32
Number of fields in an index: 10
Number of characters in a validation message: 255
Number of characters in a validation rule: 2048
Number of characters in a table or field description: 255
Number of characters in a record (excluding Memo and OLE Object fields): 2000
Number of characters in a field property setting: 255


Pete

Originally Posted by airprince

Greetings......

I just want to ask aboust Visual Basic.

I am developing a Program using Visual Basic. This program needs MS Access for data storage. Everyday more than hundred of data added in the Database. If the data reaches 100,000 or more, does it affect the performance of the program? I mean will it run as is or will run slow especially when finding records on the database?

I dont have much idea about Basual basic. I only read some tutorials here and downloaded some sample code.

in fairness I learned some basic and maybe some hard codes. Just for reading and reading some of your samples.

Thanks

Thanks for posting that, I have seen it some where before but it is good information. Personlay I would not use access if the database is larger than 500 MB, especialy with sql 2005 express available. I thought it can handle more than 100,000 records per table, but I still would not do it.

[digioz]

Yeah, people seem to forget that Microsoft Access was meant to be more of a personal / single user database then anything else.

[King Coder]

People should also take into consideration the number of simultaneous users. Access is considered unstable after about 5.