Ways to deploy an Access project

In a multiuser environment, each user often needs his or her own copy of the Access project file that is connected to the same Microsoft SQL Server database. You cannot modify objects, such as forms and reports, in the same Access project file among two or more users.

When you open an Access project, it's always opened in exclusive mode. If another user has opened an Access project first, and you try to open the same Access project while that user still has it open, Access alerts you that the file is opened exclusively and gives you the option of opening a read-only copy. In this case, objects in the Access project can't be modified.

In practice, when you are running a shared application you often provide a copy of the Access project file to each user of the application. In this case, each user has his or her own copy of forms, reports, data access pages, macros, and modules.

Whether or not a user has a personal copy of an Access project, one SQL Server database and the tables, queries, and database diagrams that reside in that database are in fact being shared. Therefore, it's important in a multiuser environment to also provide adequate security on these shared database objects so their integrity is not compromised.

If your users are using Microsoft Windows Terminal Server, each user can have a copy of the Access project file in his or her individual user data space defined in the user's profile on the Terminal Server. You can copy the Access project into each user's default application data folder and when a user logs in through Terminal Server, each user gets a personal copy of the Access project file.

You can create data access pages or output one or more database objects to server-generated HTML or static HTML. You can then display these Web pages in Microsoft Internet Explorer, and access data though these Web pages.

You don't always have online access to your company databases. However, you often want to take selective data offline (that is, download the data to a computer, such as a laptop or home computer), review and update the data away from your workplace, and then conveniently update and reconcile data changes when you have online access again. Using a combination of Microsoft products, a developer and database administrator can create an application that enables offline data access for users.

Many application projects with a single database can make use of several copies of the database: a development database, a test database, and a deployed production database. During the lifetime of a deployed database application, changes and improvements are first made in the development database, then propagated to the test database, and then propagated to the production database.

You might want to create an Access project and, after it is ready for testing or production, disconnect it from the SQL Server database. For example, you can create an Access project application and wait until after you have distributed copies of it to connect it to a Microsoft SQL Server database. You can distribute your Access project file to users, and then give them instructions on how to first open the file and then use the Connection command on the File menu to connect to the server where your database is located.

You can also use the Copy SQL Database or Transfer SQL database commands, macro actions, or methods to make the databases available to users.

Alternatively you could build the SQL Server database with a script file. The Northwind Traders sample Access project (NorthwindCS.adp) uses Visual Basic for Applications (VBA) code in the Startup module (which is invoked by using a function call from the OnOpen event of the Startup form) to build the SQL Server 2000 database by using Transact-SQL code contained in a script file (NorthwindCS.SQL), and then attaches the database to SQL Server. The VBA code in the Startup module uses a combination of Access, ADO, and SQL-DMO objects to perform these operations.

If you have Microsoft Office Access 2003 Developer Extensions, you can use the Package and Deployment Wizard to create an installation program that will install both an Access project application and a SQL Server 2000 Desktop Engine database. For information about deploying Access client/server applications with this wizard, see the Access 2003 Developer Extensions Help files.

If you use two computers, such as an office computer and a portable computer, you can make replicas of your SQL Server database and keep those replicas synchronized. Many users at different locations can work on personal database copies of a Master database at the same time and then synchronize the copies over the network, either through a dial-up connection or the Internet. For more information about SQL Server replication, see the SQL Server documentation.