Often people in newsgroups ask about some comparison of Access and SQL Server databases. In this article, I want to tell you about some general characteristics of Access 97/2000 and SQL Server 7.0/2000, about their restrictions, and about the comparison of these products.
Microsoft offers a full family of database tools for the desktop, the server, and for open connectivity. For the desktop Microsoft offers Microsoft Access 97/2000. The Microsoft Access 97/2000 is a full-featured multiuser relational database management system that designed for the Microsoft Windows operating systems (such as Windows 9x, Windows NT, Windows 2000). Access 97/2000 is extremely visually oriented and easy to use. It makes extensive use of drag-and-drop and visual design for queries, forms, and reports.
Access 97/2000 comes with an integrated development environment (IDE), including incremental compilation, a fully interactive visual debugger, breakpoints, and single step-through. These capabilities combine to make Microsoft Access an extremely powerful platform for developing client-server database solutions.
There are general Access 97/2000 characteristics:
Though the maximum database size is 1 Gb, but your database can include linked tables in other files, and its total size is limited only by available storage capacity.
Number of characters in a Memo field can be up to 65,535 characters when entering data through the user interface or can be up to 1 Gb when entering data programmatically.
Access 2000 can lock on the row level in comparison with Access 97 page level's lock.
For the server engine in client-server solutions Microsoft provides the Microsoft SQL Server 7.0/2000.
There are no many differences in the internal architecture between SQL Server 7.0 and SQL Server 2000. SQL Server 2000 has the same features as SQL Server 7.0: autogrow features, new storage engine, complete row level locking and so on. So, the general characteristics of these versions are the same.
There are general SQL Server 97/2000 characteristics:
SQL Server 2000 Enterprise Edition on the Windows 2000 DataCenter can support up to 32 CPU and up to 64GB physical memory (RAM) on a computer!
SQL Server Books Online
Maximum Capacity Specifications http://msdn.microsoft.com/library/psdk/sql/8_ar_ts_8dbn.htm
ACC: Choosing Database Tools White Paper Available in Download Center http://support.microsoft.com/support/kb/articles/Q128/3/84.asp
Microsoft SQL Server: Microsoft Access 2000 Data Engine Options http://msdn.microsoft.com/library/backgrnd/html/acmsdeop.htm