Access 2000-Biggest DB you have?

[KAllen31]

I am in the midst of developing a data base that will be updated every month form data from an ASP. The info will be archived at the end of each year to make room for the new year's info.

My concern is: how robust is 2000? I heard that you can have 1Gig in Access 97 (that's what I am currently using), but in 2000 you can have 2 gig.

This has the potential of having about 10,000 records in one table-15 fields, and up to 40,000-12 fields in another and them numerous others as the data base progresses.

I know 2 gigs is a lot, but I want to nip any problems I may have with scalability now as opposed to later. I have SQL 97 (which I am now learning, and have thought about putting the info there and linking it into Access), I am fairly new to SQL so feel a little uncomfortable doing that.

My questions is: For those of you on 2000 what is the biggest DB you are running on it right now (in terms of records and fields)

Thanks

 

[Benjamin Bolduc]

I have a large amount of corperate data and objects in my database. (About .5GB) Up untill I read your post, I wasn't even aware of a 2GB limit. Now I'm a little concerned myself as to the scalability of the program. I'm hoping someone can offer more information about this.

Thanks,
Ben Bolduc

 

[KAllen31]

I have heard the info. about the 2gig limit by an Access developer who had to swich to Access 2000 because his Access 97 DB grew over 1Gig (the limit in 97).

He told me that when doing my project roll-out I needed to at least switch to 2000 to give myself that extra gigabyte of storage.

In exploring this a little further, I did see a couple of references to this user groups, such as the following

"Information on your question can be found in "MS. Access help" where max Mdb
file size for Access 2000 is given as 2Gb and max size of a single table is
1 Gb. The mdb max for Access 97 was 1 Gb. I think that the 2 Gb limit also
applies to a Access 2000 Project file. It is pointed out in the
documentation that the 2 GB limit applies to one Mdb file, but since linked
tables can be used an MS Access database system can be much larger because
many mdb files may form a system."

Info on this is hard to find, can anyone confirm whether this is true?

Thanks!!

 

[Benjamin Bolduc]

Maximum Attributes-

Microsoft Access database (.mdb) file size 2 gigabytes minus the space needed for system objects.

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


I don't know if this is any help, But this is all the info I could find in the Access 2002 help files

 

[Cosmos75]

You can split your database into seperate files , one for data and one for forms/queries/reports. Just have them linked.

I think you can even have one db file for one table or split the tables (data) into seperate db files. Each db file would then have the 2Gig limit.

Am not sure what problems you can run into doing that but I know you can if you want to.

Hope this helps!