Limitations of Access

[Tegwin]

I am using Access 2000 as a backend database for a web application I am running, can anyone tell me what are the limitations of Access and also, What is the maximium size that an Access database can get to etc

I am starting to experience a slowness of the site and was thinking I need to update to SQL server and I think Access 2000 is to blame

[jofa]

http://www.sitepointforums.com/showp...8&postcount=19

[Cam]

http://www.sitepointforums.com/showp...8&postcount=11

Very good in terms of the limitations of Access, but doesn't help much in comparing with other RDMS.

[DaveMaxwell]

Those links are fine. Some other things to consider: there are a number of factors that can influence the power of Access 2000:

1. How many people are accessing your website at any one time? The specs always say 20, but that is people accessing your database at the exact time (ie connect to your page at the exact same moment which causes your database to be accessed for the data), so that number can be realistically as high as 100 depending on how much data is needed to build your pages (lower if your site is VERY data driven)
2. Which driver are you using to connect to the database? OLEDB or ODBC?
3. When was the last time you compacted the database?
4. How well do you have your asp coded? Have you optimized the code to it's fullest extent? Are you opening connections/recordsets at the last second and closing them when not needed? Are connections pooled?
5. Do you have all the appropriate indexes in place?

#1-3 are specifically related to access. #4 & 5 are basic design concepts. If the #4 & 5 can't be answered "yes" to, then you will not see a huge difference when switching to SQL server because the same problems that exist for access will still have the same effect (though to a lower extent) with SQL Server.

I'm not saying don't go to SQL Server, but the expense is not insignificant and it's not as easy as just copying the data over. If you can put if off for a little while, look heavily at those last two before making the jump up.

Just my .02