Desktop databases, such as Microsoft Access and Microsoft Visual FoxProŽ, are not suitable alternatives to SQL Server when implementing applications in the high-stress enterprise environment. The desktop databases would not deliver satisfactory performance on a large database with many concurrent users. They are single-user and light multiuser data stores. Their security is also limited.
Neither desktop database supports distributed transactions; hence any Microsoft Transaction Server (MTS) component that reads or updates an Access or FoxPro database must be marked "Does not support transactions."
Earlier versions of the Access driver did not support connection pooling. When using Access with MTS you must use version 3.51 or later of the Access driver.
A desktop database is often an important step in the process of developing a SQL Server database. Desktop databases offer the advantage of being easy to set up and maintain. Upon completion of a desktop prototype the database can be upsized to SQL Server. Access and Visual FoxPro can be upsized to SQL Server using their respective upsizing wizards. The upsizing wizards effortlessly deliver a SQL Server database from your desktop database.
If the CML application adds mobile or offline users, a desktop database could be useful. Using replication, data can be readily shared with the server database. This type of user often is only interested in a subset of the server data, and the desktop database can meet performance expectations.