CHAPTER 8

Delivering Your Application

If you plan to distribute your application to other users, you may want to use the tools that Microsoft® Access provides to make your application easy to install and maintain. This chapter discusses techniques you can use to prepare your application for delivery.

Chapter Contents

Putting the Final Touches on an Application   

Setting Startup Options   

Separating Your Application’s Tables from Its Other Objects   

Documenting Your Database   

Database Maintenance   

Putting the Final Touches on an Application

When you have finished developing and testing the database and objects that make up your application, it’s time to put the final touches on your application before you deliver it to users. Think about where your users are located, as well as their levels of database expertise. Also, think about the environment in which your application will run, and the potential for future updates to the database and objects you have developed. Considering such issues will help you create a polished final product that meets the specific needs of your users.

You may want to do any of the following before you distribute your application:

• Set options that determine how your application starts up.
  
  
• If your application will be used on a network, you may want to split it into a shared back-end database that resides on the network and a front-end database on each user’s computer that has links to tables in the back-end database.
  
  
• Document the objects and structure of your database.
  
  
• Consider how database maintenance operations will be performed.
  
  
• Secure the design of database objects and control access to sensitive data.

  See Also   For more information about securing your application, see Chapter 18, “Securing Access Databases,” in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.

• Use replication to synchronize data and design changes across a local area network, using dial-up or e-mail connections, or across the Internet.

  See Also   For more information about how to create and use replicas of your application, see Chapter 16, “Multiuser Database Solutions,” in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.

In addition to the tools and information provided by Microsoft Access to assist you with application development, Microsoft Office 2000 Developer provides tools to help you develop Microsoft® Windows®- and Office-compatible applications. Additionally, Office 2000 Developer includes:

• A royalty-free distribution license and tools (including the Microsoft Graph run-time application) that allow you to distribute copies of your custom Access applications to all your users.
  
  
• Replication Manager, which schedules updates among replicas, determines which objects in the database are replicated, and manages multiple replica sets at one time. By using Replication Manager, you can see a visual display of all the replicas in a set.
  
  
• Microsoft Visual SourceSafe™ 6.0 (including the Visual SourceSafe Add-in for Access and the Visual SourceSafe Visual Basic Editor Add-in), which supports multideveloper application development.
  
  
• ActiveX® controls that you can use to add versatility to your applications and duplicate Microsoft Windows 95 functionality. This includes controls for Internet applications and controls to connect Microsoft Outlook® messaging with data and other Office applications.
  
  
• Updated Windows API Viewer with enhanced performance. The Windows API Viewer contains declarations and constants used with the Windows 95 application programming interface (API). You can copy and paste these declarations and constants into your Visual Basic modules.
  
  
• The Data Environment, a user interface for easily connecting solutions to external databases.
  
  
• The Make Add-in feature, which you can use to create an add-in directly from the Visual Basic Editor for a specific Visual Basic Editor host.
  
  
• The Package and Deploy Wizard, which you can use to deliver Office programmable solutions for any Visual Basic® for Applications (VBA) 6.0 host to disk, network, and the Web.
  
  
• The Code Librarian, a tool for browsing existing source files for code snippets. You can search text, view descriptions and comment headers, and insert the text into your current project.
  
  
• The HTML Help Workshop Software Development Kit (SDK), with which you can create Help topics similar to those in Microsoft Office 2000. The SDK includes the HTML Help Workshop and the run-time version to distribute to your users.
  
  
• The Agent SDK, which provides programmatic control over Office Assistants you create.

Setting Startup Options

You can set options in the Startup dialog box that determine how your application looks and behaves when it starts. For example, you can:

• Specify which form to display when your application opens.
  
  
• Indicate which menus and toolbars can be used in your application.
  
  
• Set the text that appears in the title bar while your application is running.
  
  
• Keep the Database window from displaying.
  
  
• Prevent the use of special keystrokes used to display the Database window (F11 or ALT+F1), show the Immediate window (CTRL+G), or pause execution (CTRL+F11).

To display the Startup dialog box, click Startup on the Tools menu.

See Also   For more information about using the Startup dialog box, see “Setting Startup Options” in Chapter 1, “Creating an Application.”

Separating Your Application’s Tables from Its Other Objects

If you’re distributing your application to a number of users, or if your data is located on a server, consider using a back-end database to hold the tables that contain your data and a front-end database to hold the application’s other objects (queries, forms, reports, macros, and modules). You can then base all objects in your application on linked tables from the back-end database.

When you separate the application’s data from its objects, you can easily distribute new versions of your front-end database that include upgraded queries, forms, reports, macros, and modules without disturbing the application’s data. Similarly, if you need to exchange one set of data for another or back up your data, you can perform these operations on your back-end database without affecting the objects in your front-end database. And if your data is located on a server, you can reduce the network load and improve performance by having users run the front-end database on their workstations rather than from the server.

Users open and use the front-end database. Because the objects in the front-end database are based on linked tables, changes that users make to the data using these objects change the data in the back-end database. The following illustration demonstrates this configuration.

If you know from the beginning that you intend to split your application into two database files, you can develop the application with this in mind. Or you can keep tables and objects together in the same file and split them only when you’re finished with development and are ready to distribute the application.

The easiest way to split an application after creating its objects is to use the Database Splitter Wizard.

To split an application after creating its objects

1. On the Tools menu, point to Database Utilities, and then click Database Splitter.
   
   
2. Follow the instructions in the dialog boxes that appear.

The Database Splitter Wizard creates a new, empty back-end database. It moves all tables in the current database to the new back-end database, preserving table relationships and properties. The current database is now the front-end database. The wizard then links each table in the back-end database to the front-end database.

See Also   For more information about using linked tables, type linked tables in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.

The links used by linked tables in the front-end database are based on the location of the back-end database. If users move the back-end database to a different location, the links will fail. If the current links are broken, you can automate the process of linking tables for your users by prompting them for the path to the back-end database when they start your application. Then you can use the RefreshLink method to refresh your application’s links. To see an example of how this works, open the Developer Solutions sample application in the Samples subfolder of your Office folder. In the upper part of the Developer Solutions form, double-click Use Multiple Databases. In the lower part, double-click Link Tables At Startup. You can download the Developer Solutions sample application and associated Help files from the Microsoft Developer Network (MSDN) Online Web site.

Documenting Your Database

Before you distribute your database to users, you may want to document the objects in your database; the underlying structure, or schema, of your application; and any Data Access Objects (DAO) objects, methods, and properties. This is sometimes called mapping the database. When you have a complete map of your database, it’s easier to modify the database and to write applications that manipulate the data.

If you just need information about application objects that appear in the user interface, you can use the Documenter (Tools menu, Analyze submenu) to produce a report on those objects. However, to obtain information about the DAO objects, properties, and methods that can’t be manipulated by way of the user interface, you must use VBA.

See Also   For more information about the Documenter, type Documenter in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.

Because DAO objects are organized in a hierarchy of nested containers, you can map the database by walking through the hierarchy with a series of nested For...Each loops. Each element in the collection can be accessed with a statement block with the following syntax:

For Each object In collection

statements' Map the properties of the object.

Next object

See Also   For information about individual properties that are exposed when you map a database, type the name of the property in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.

The following code maps the current database. For brevity, the Attributes property is fully mapped only for each TableDef object. If you want to map attributes of the other objects, simply use the code for the TableDef object attributes as a model and insert the appropriate statements. This example assumes that the user running this code has at least Read Design permission on all tables in the database.

Sub MapDatabase()
   Dim dbs As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field
   Dim idx As DAO.Index, rel As DAO.Relation
   Dim intX As Integer

OnError GoTo ErrorHandler

   Set dbs = CurrentDb

   ' Map the Database properties.
   Debug.Print "DATABASE"
   Debug.Print "Name: ", dbs.Name
   Debug.Print "Connect string: ", dbs.Connect
   Debug.Print "Transactions supported?: ", dbs.Transactions
   Debug.Print "Updatable?: ", dbs.Updatable
   Debug.Print "Sort order: ", dbs.CollatingOrder
   Debug.Print "Query time-out: ", dbs.QueryTimeout

   ' Map the TableDef objects.
   Debug.Print "TABLEDEFS"
   For Each tdf in dbs.TableDefs
      Debug.Print "Name: ", tdf.Name
      Debug.Print "Name: ", tdf.DateCreated,
      Debug.Print "Name: ", tdf.LastUpdated,
      If tdf.Updatable = True Then
         Debug.Print "Updatable",
      Else
         Debug.Print "Not Updatable",
      End If

      ' Show the TableDef Attributes.
      Debug.Print Hex$(tdf.Attributes)
      If (tdf.Attributes And dbSystemObject) <> 0 Then
         Debug.Print "System object"
      End If
      If (tdf.Attributes And dbAttachedTable) <> 0 Then
         Debug.Print "Linked table"
      End If
      If (tdf.Attributes And dbAttachedODBC) <> 0 Then
         Debug.Print "Linked ODBC table"
      End If
      If (tdf.Attributes And dbAttachExclusive)<> 0 Then
         Debug.Print "Linked table opened in exclusive mode"
      End If

      ' Map Fields for each TableDef object.
      Debug.Print "FIELDS"
      For Each fld in tdf.Fields
         Debug.Print "Name: ", fld.Name
         Debug.Print "Type: ", fld.Type
         Debug.Print "Size: ", fld.Size
         Debug.Print "Attribute Bits: ", Hex$(fld.Attributes)
         Debug.Print "Collating Order: ", fld.CollatingOrder
         Debug.Print "Ordinal Position: ", fld.OrdinalPosition
         Debug.Print "Source Field: ", fld.SourceField
         Debug.Print "Source Table: ", fld.SourceTable

         ' Show the Field Attributes here.
         Debug.Print Hex$(fld.Attributes)
         If (fld.Attributes And dbSystemObject) <> 0 Then
            Debug.Print "System Object"
         End If
      Next fld         ' Get the next Field in the TableDef object.

      ' Map Indexes for each TableDef object.
      Debug.Print "INDEXES"
      For Each idx in tdf.Indexes
         ' Set the Index variable.
         Set idx = tdf.Indexes(intX)
         Debug.Print "Name: ", idx.Name
         Debug.Print "Clustered: ", idx.Clustered
         Debug.Print "Foreign: ", idx.Foreign
         Debug.Print "IgnoreNulls: ", idx.IgnoreNulls
         Debug.Print "Primary: ", idx.Primary
         Debug.Print "Unique: ", idx.Unique
         Debug.Print "Required: ", idx.Required
         ' Map the Fields of the Index.
         For Each fld in idx.Fields
            Debug.Print "Name: ", fld.Name
         Next fld      ' Get the next Field in the Index.
      Next idx         ' Get the next Index in the TableDef object.
   Next tdf            ' Get next TableDef in the Database.

   ' Map the Relation objects.
   Debug.Print "RELATIONS"
   For Each rel in dbs.Relations
      Debug.Print "Name: ", rel.Name
      Debug.Print "Attributes: ", rel.Attributes   
      Debug.Print "Table: ", rel.Table
      Debug.Print "ForeignTable: ", rel.ForeignTable
      ' Map the Fields of the Relation objects.
      For Each fld in rel.Fields
         Debug.Print "Name: ", fld.Name
         Debug.Print "ForeignName: ", fld.ForeignName
      Next fld         ' Get the next Field in the Relation object.
   Next rel            ' Get next Relation object in the Database.

   dbs.Close

   Set rel = Nothing
   Set idx = Nothing
   Set fld = Nothing
   Set tdf = Nothing
   Set dbs = Nothing

   Exit Sub

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & Err. Description
End Sub

Database Maintenance

As your application is used, the database file can grow in size. If you are responsible for the maintenance of your database, you’ll need to perform a number of system-oriented tasks from time to time, such as compacting the database and repairing it if it becomes corrupted. This section discusses these maintenance tasks.

If you are performing compact and repair operations, you will often use the menu commands provided in the user interface. The Compact and Repair Database command is available on the Database Utilities submenu (Tools menu) in the Access startup window.

However, if you want to provide a way for your users to compact and repair their copies of your application, you can provide them with either an icon that performs the compact and repair operations or code that compacts and repairs the database automatically.

You can create a shortcut that uses the /compact command-line option. This command-line option compacts and repairs your application without opening it. When you create the shortcut, type a command line that consists of:

• The Microsoft Access application file name, MSAccess.exe. If you are using Microsoft® Windows NT® Workstation, you must specify a full path to the Access application file.
  
  
• The file name of the front-end database.
  
  
• The /compact option.

Note   To compact and repair a database, the database and any connections to it must be closed. Also, if you have established user-level security for your application, the user running the compact and repair operation must have Open Exclusive permission for the database, and have Modify Design or Administer permission for all tables in the database. For more information about permissions, type setting permissions in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search. Additionally, when compacting, there must be sufficient disk space for both the original and compacted versions of the database, even if the database is being compacted to the same file name. This is because the compacted database is renamed as the original database only after compacting to a temporary file is successful.

Compacting a Database

To maintain a high state of performance, Access defers the removal of discarded pages until you shut down the database and compact the discarded pages. This design keeps the interactive performance of your database high at the expense of recoverable disk space.

Compacting a database:

• Reorganizes a table’s pages so they reside in adjacent database pages. This improves performance because the table is no longer fragmented across the database. If a table has a primary key defined, Access copies the records in primary key order, which makes reading ahead more efficient. This produces an effect similar to clustered indexes in Microsoft SQL Server™; however, unlike true clustered indexes, Access doesn’t maintain primary key order when users add, delete, or modify records after the database is compacted. If a table doesn’t have a primary key, Access copies records in the order that the records are stored on disk.
  
  
• Reclaims unused space created by object and record deletions. When objects or rows are deleted from the database, the space they occupied is marked as available. However, the size of the database doesn’t shrink unless the database is compacted.
  
  
• Resets incrementing AutoNumber fields (called Counter fields in Access versions 2.0 and earlier) so the next value allocated will be one more than the last undeleted record.
  
  
• Regenerates the table statistics used in the query optimization process. These statistics can become out-of-date over time, typically due to transactions that were rolled back or to the database not being closed properly.
  
  
• Flags all queries so that they will be recompiled the next time the query is run. This is important because database statistics can change, which could cause a previously compiled query to contain inaccurate optimization information.

To compact your database, point to Database Utilities on the Tools menu and click Compact and Repair Database. If the database you want to compact is currently open, it will be closed, compacted into a temporary file, and then reopened. If no database is open, you must specify the database to compact and the database to compact into. If you specify the same file name to compact into, Access compacts into a temporary file. When Access compacts to a temporary file, it renames the temporary file back to the original file name once the compacting process is completed.

You can also use the CompactDatabase method of the DBEngine object to compact a database. When you use the CompactDatabase method, you must compact to a file with a different name. The following code example uses the CompactDatabase method to compact the Orders sample database to a temporary file and then renames that temporary file to the original name if the compacting process is successful. You can download the Orders sample application and associated Help files from the Microsoft Developer Network (MSDN) Online Web site.

Sub CompactDB()

   Const FILE_PATH = "C:\Program Files\Microsoft Office\Office\Samples\"

   On Error GoTo ErrorHandler

   ' Compact the database to a temp file.
   DBEngine.CompactDatabase FILE_PATH & "Orders.mdb", _
      FILE_PATH & "OrdersTemp.mdb"

   ' Delete the previous backup file if it exists.
   If Dir (FILE_PATH & "Orders.bak") <> vbBullString Then
      Kill FILE_PATH & "Orders.bak"
   End If

   ' Rename the current database as backup and rename the temp file to
   ' the original file name.
   Name FILE_PATH & "Orders.mdb" As FILE_PATH & "Orders.bak"
   Name FILE_PATH & "OrdersTemp.mdb" As FILE_PATH & "Orders.mdb"
   MsgBox "Compacting is complete"

   Exit Sub

ErrorHandler:
   Msgbox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

The CompactDatabase method also gives you the option of changing the encryption state, the version, and the disk or folder of a database while it’s being compacted. In other words, you can choose to convert an encrypted database to a non-encrypted database, or vice versa.

For example, the following code compacts a database named Old and creates a new, encrypted database named New.

DBEngine.CompactDatabase "C:\Old.mdb", "C:\New.mdb", _
   dbLangGeneral, dbEncrypt

See Also   For more information about database encryption, see Chapter 18, “Securing Access Databases” in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.

Note   Compacting cannot be done inside a transaction, or on a database that is currently open by another user.

You can also compact your database automatically when you close it.

To automatically compact your database when you close it

1. On the Tools menu, click Options.
   
   
2. Click the General tab.
   
   
3. Select the Compact On Close check box.

Important Considerations When Compacting Replicated Databases

Replicated databases make extensive use of temporary space, especially when many design changes are made to an application. Just as with non-replicated databases, compacting regularly reduces file size and improves performance.

For optimal results, compact a replicated database twice. Compacting a replica the first time reclaims some space and flags other space as available for reclaiming later. Compacting a second time reclaims all available space. Compacting more than twice doesn’t provide additional benefits.

Improperly compacting the Design Master of a replica set causes the replica set to lose its Design Master. When Access opens a replica, it stores the name and location of the file in the database. The next time Access opens the replica, it checks to see if the file has the same name and location. If so, then the file opens normally. If not, Access gives the replica a new ReplicaID property value and, if the file was the Design Master, removes the Design Master flag. This prevents two replicas from having the same ReplicaID property value or a replica set from having two Design Masters.

To prevent a Design Master from losing its Design Master flag when you compact a replicated database, do one of the following:

• Open the Design Master database, and then click the Compact and Repair Database command on the Database Utilities submenu (Tools menu). When you do this, Access compacts to a temporary file, and only deletes the original file and renames the temporary file to the original file name if the compact operation is successful.
  
  
• If you prefer to save the original file as a backup, close the Design Master database before you use the Compact and Repair Database command, specify the Design Master as the database to compact from, and then specify a temporary file name to compact into, but do not open the database with the Open Database command (File menu) until you have renamed the compacted database back to its original file name. This also applies if you are using the CompactDatabase method in code to compact. The CompactDatabase method requires you to compact to a new file name. In both cases, if you open the temporary database before you rename it back to its original file name, Access removes the Design Master flag.

Repairing a Database

If your database is damaged, close the database, then point to Database Utilities on the Tools menu and click Compact and Repair Database. The Compact and Repair Database command checks all pages in the database for correct linkage, validates all system tables, and validates all indexes. Because the Compact and Repair Database command can’t fix all possible forms of database corruption, you should back up your database files regularly to avoid unrecoverable data loss. This kind of corruption can occur when the system isn’t shut down normally (such as during a power failure).