Specifications and Limits of Access (2000,2003)

#1 July 2nd, 2008, 11:45 AM

I have always wondered about the specification page in the Access Help
files. It reads

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

Now I thought to test the Modules section of this given it is what my
current project is most concerned with. My current project has been in
active development for the past 8 years, not a monolithic 8 years
trying to achieve one goal but an 8 years spent adding small features
that users request over time, new reports, new ways(quicker, simpler,
etc.) of doing things and simply new things to do. We do everything
with this software from inventory management to po's invoicing orders
management individual job management, returns, quality control, etc.
It really is a monster.

IT has about 700 forms and reports with a good deal of re use between
reports, every form does data input and display and each does a lot,
believe me we manage everything we do with this (its really kind of
scary when you think of it Access being the heart and soul of any
medium sized company)

Because at one point we had close to 1000 forms and reports (we cut
out anything that wasn't used in the past year as a sign that well we
didn't need it) this limit seemed to be important.

I set up a form in access 2003(our company uses 2000 for its main
business, for a smaller branch we've gone to 2003 as a test before
migrating) and had it copy one of my previous forms up until i got to
1023 (1024 seems like a real number that could be a limit on a
computer system) and then i set it to add more. and more and more
until i got to 2000 forms with code modules (granted they were the
same exact code inside them, so i don't know if access does any
optimization with code modules that are the same, maybe someone here
knows?) and access kept on trucking.

I just thought I'd throw this out there to generate some conversation
and thoughts on how hard the limits MS publishes are and should we
really be worried about pushing access past these points. As we keep
growing as a company and we add more requirements to our system (we're
working on becoming ISO 9000 and implementing all the forms and
procedures within our system along with expanding our current QA
system to be inline with as the jobs are being preformed on the
factory floor) it would be nice to know how hard we can push Access
before we have to rewrite everything into a compiled exe (VB6, .Net
whatever floats our boat at the time)

#2 July 2nd, 2008, 01:05 PM

<howard.canaway@gmail.comwrote in message
news:02e684ca-5f95-444e-9033-36f47e85eb5d@p25g2000hsf.googlegroups.com...

Quote:

>
IT has about 700 forms and reports
>
Because at one point we had close to 1000 forms and reports

I don't think I'd be alone in thinking that an app with so many objects
would need a major overhaul, if not a redesign from scratch.

Keith.

#3 July 2nd, 2008, 02:55 PM

This is a family-oriented newsgroup and you really shouldn't describe
such things here. There are young eyes, windows to easily influenced
minds, reading it.

The Access Gods have been restless and unhappy lately and I think that I
understand why. Beware!

howard.canaway@gmail.com wrote in news:02e684ca-5f95-444e-9033-
36f47e85eb5d@p25g2000hsf.googlegroups.com:

Quote:

I have always wondered about the specification page in the Access Help
files. It reads
>
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
>
Now I thought to test the Modules section of this given it is what my
current project is most concerned with. My current project has been in
active development for the past 8 years, not a monolithic 8 years
trying to achieve one goal but an 8 years spent adding small features
that users request over time, new reports, new ways(quicker, simpler,
etc.) of doing things and simply new things to do. We do everything
with this software from inventory management to po's invoicing orders
management individual job management, returns, quality control, etc.
It really is a monster.
>
IT has about 700 forms and reports with a good deal of re use between
reports, every form does data input and display and each does a lot,
believe me we manage everything we do with this (its really kind of
scary when you think of it Access being the heart and soul of any
medium sized company)
>
Because at one point we had close to 1000 forms and reports (we cut
out anything that wasn't used in the past year as a sign that well we
didn't need it) this limit seemed to be important.
>
I set up a form in access 2003(our company uses 2000 for its main
business, for a smaller branch we've gone to 2003 as a test before
migrating) and had it copy one of my previous forms up until i got to
1023 (1024 seems like a real number that could be a limit on a
computer system) and then i set it to add more. and more and more
until i got to 2000 forms with code modules (granted they were the
same exact code inside them, so i don't know if access does any
optimization with code modules that are the same, maybe someone here
knows?) and access kept on trucking.
>
I just thought I'd throw this out there to generate some conversation
and thoughts on how hard the limits MS publishes are and should we
really be worried about pushing access past these points. As we keep
growing as a company and we add more requirements to our system (we're
working on becoming ISO 9000 and implementing all the forms and
procedures within our system along with expanding our current QA
system to be inline with as the jobs are being preformed on the
factory floor) it would be nice to know how hard we can push Access
before we have to rewrite everything into a compiled exe (VB6, .Net
whatever floats our boat at the time)
>
>
>

#4 July 2nd, 2008, 08:25 PM

<howard.canaway@gmail.comwrote in message
news:02e684ca-5f95-444e-9033-36f47e85eb5d@p25g2000hsf.googlegroups.com...

Quote:

I have always wondered about the specification page in the Access Help
files. It reads
>
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
>
Now I thought to test the Modules section of this given it is what my
current project is most concerned with. My current project has been in
active development for the past 8 years, not a monolithic 8 years
trying to achieve one goal but an 8 years spent adding small features
that users request over time, new reports, new ways(quicker, simpler,
etc.) of doing things and simply new things to do. We do everything
with this software from inventory management to po's invoicing orders
management individual job management, returns, quality control, etc.
It really is a monster.
>
IT has about 700 forms and reports with a good deal of re use between
reports, every form does data input and display and each does a lot,
believe me we manage everything we do with this (its really kind of
scary when you think of it Access being the heart and soul of any
medium sized company)
>
Because at one point we had close to 1000 forms and reports (we cut
out anything that wasn't used in the past year as a sign that well we
didn't need it) this limit seemed to be important.
>
I set up a form in access 2003(our company uses 2000 for its main
business, for a smaller branch we've gone to 2003 as a test before
migrating) and had it copy one of my previous forms up until i got to
1023 (1024 seems like a real number that could be a limit on a
computer system) and then i set it to add more. and more and more
until i got to 2000 forms with code modules (granted they were the
same exact code inside them, so i don't know if access does any
optimization with code modules that are the same, maybe someone here
knows?) and access kept on trucking.
>
I just thought I'd throw this out there to generate some conversation
and thoughts on how hard the limits MS publishes are and should we
really be worried about pushing access past these points. As we keep
growing as a company and we add more requirements to our system (we're
working on becoming ISO 9000 and implementing all the forms and
procedures within our system along with expanding our current QA
system to be inline with as the jobs are being preformed on the
factory floor) it would be nice to know how hard we can push Access
before we have to rewrite everything into a compiled exe (VB6, .Net
whatever floats our boat at the time)
>
>
>

You may want to break-up this application into modules.
1) Move your Utility functions and Forms into a library.
2) Split the other functions into separate MDBs all referencing the same
library and back-end.
3) Create a new top level menu to open the modules.
4) If the back-end if reaching 2GB limit, consider a move to SQL server.
Of course this will take some time to code and test, but will allow you to
reuse most of the existing objects. It will also allow you to fix any
structural problems and document all the features. You can also add all the
new features for ISO 9000 ect.

#5 July 3rd, 2008, 07:45 AM

<howard.canaway@gmail.comwrote in message
news:5fc73ecb-0587-4a2f-86bb-c521a42231a9@y21g2000hsf.googlegroups.com...

Quote:

yes we probably should be doing this in something more robust in
access, i was trying to see what experience others have had with
having an application grow like this has within access and how hard
they have pushed the limits of access and what happens when/if it
breaks down under the strain.

I have no experience of pushing Access to the limits you describe but you do
seem to have all of your eggs in one basket. My experience is that, once an
application in Access becomes business-critical it is migrated to Oracle.
It sounds to me that, at the very least, you should be splitting this
application up into manageable business unit oriented groups of
applications. There's nothing to stop these applications then interacting
with each-other where necessary.

Regards,
Keith.
www.keithwilby.com

Specifications and Limits of Access (2000,2003)

Popular Articles

What is Bytes?