6 key considerations when creating Access 2007 databases

Microsoft Office Access 2007 Step by Step
By Steve Lambert, M. Dow Lambert III, and Joan Preppernau

Steve Lambert has written 18 books, most of which are about applications. As President of Online Publishing and Programming Solutions, Inc. (OP²S), he has managed the development of many tools for creating and viewing training material.

M. Dow L ambert III has worked for 20 years in academia. During this time, Dow authored or co-authored 19 social science research publications, developed curriculum and training programs for social services professionals, and managed longitudinal studies of human behavior. In 1995, he moved from academia to the private sector, where he worked for a small company that developed and maintained reservation systems for the travel industry. Here he learned the difference between writing research reports for scientific journals, writing technical specifications for programmers, and writing user guides for the people who actually needed to understand and use the software that his company produced.

Joan Preppernau is the author of more than a dozen books about Windows and Office as well as numerous online training programs, and a contributor to the development of the certification exams for the 2007 Microsoft Office system and Windows Vista. Joan is the President of Online Training Solutions, Inc. (OTSI).

To learn more about other books on the 2007 Microsoft Office system, visit Microsoft Press.

In this article

1. Creating a database from a template

2. Migrating a database from a previous version of Access

3. Importing information from other sources

4. Controlling the features available to database users

5. Assigning a password to a database

6. Securing a database for distribution

Creating the structure for a database is easy. But an empty database is no more useful than an empty document or worksheet. It is only when you fill, or populate, a database with data in tables that it starts to serve a purpose. As you add queries, forms, and reports, it becomes easier to use. If you customize it with a switchboard or custom categories and groups, it moves into the realm of being a database application. This article discusses six key things you should consider when creating a database application by using Microsoft Office Access 2007.

1. Creating a database from a template

A few years ago (the distant past in computer time), creating a database structure started with first analyzing your needs and then laying out the database design on paper. You would decide what information you needed to track and how to store it in the database before actually creating database objects. Creating the database structure could be a lot of work, and after you created it and entered data, making changes could be difficult. Templates have changed this process. Committing yourself to a specific database structure is no longer the big decision it once was. By using pre-packaged templates, you can create a dozen database applications in less time than it used to take to sketch the design of one on paper. Office Access 2007 templates might not create exactly the database application you want, but they can quickly create something very close that you can tweak to fit your needs.

When you create a database from a template, Access creates the tables and other objects necessary to support the type of database for which the template was designed. In some cases, the database structure created by the template can be quite complex. You can make changes to the default tables, provided you take care not to invalidate existing relationships. If significant changes are necessary to track the kind of information you want to maintain—for example, if the default tables include several fields you don't want or need, and not the ones you do—it is better to create the tables from scratch.

If the database structure created by the template is wholly unsuitable to your needs—for example, if it includes a lot of functionality you don't need—you might find it simpler to start with a blank database and manually create only those objects necessary to your specific project.

Important:  With most computer programs, it is important to save your work frequently to avoid losing it if your computer crashes or the power goes out. With Access, it is not only not important to save your data, it is not possible to manually save it. When you move the insertion point out of a record after entering or editing information, Access saves that record. This means that you don't have to worry about losing your changes, but you do have to remember that most data entry changes you make are permanent and can be undone only by editing the record again. Note, however, that changes to properties and layout are not saved automatically. When you create a new table, form, or report, or modify the properties or layout of an existing one, Access will prompt you to save the changes before closing the object or the database.

Top of Page

2. Migrating a database from a previous version of Access

Office Access 2007 stores data in a format that is different from earlier versions. Access 2007 can open and save changes to a database created in an earlier version of Access (indicated by an .mdb file extension), but earlier versions of Access cannot open a database saved in the 2007 format (indicated by an .accdb extension). If there is a chance that you or someone else will need to work on a database using a previous version of Access, then you might want to leave it in that format—Access 2007 will open it and automatically save it in the same format. However, some of the features that are new in Access 2007 won't be available unless you update the database to the new format.

Top of Page

3. Importing information from other sources

After you create an Access database, there are a variety of ways you can get information into it, including importing information from another database, a Microsoft Office Excel 2007 workbook, a Windows SharePoint Services 3.0 list, a text file, an XML file, or an HTML file.

Importing information from another Access database

You can easily import any of the standard Access objects: tables, queries, forms, reports, pages, macros, and modules from one Access database to another. When importing a table, you have the option of importing only the table definition (the structure that you see in Design view), or both the definition and the data. When importing a query, you can import it as a query or you can import the results of the query as a table.

When you import an Access object, the entire object is imported as an object of the same name in the active database. You don't have the option of importing selected fields or records. If the active database already has an object of the same name, Access imports the new object with a number appended to the end of its name.

Tip: If you need only some of the fields or records from a table in another database, you can create a query in the other database to select only the information you need and then import the results of the query as a table. Alternatively, you can import the table and either edit it in Design view or clean it up by using queries.

Importing information from an Office Excel 2007 worksheet

You can import entire worksheets or a named range from a worksheet into either a new table (one that is created during the import) or an existing table. You can also import specific fields from a worksheet or range.

Excel 2007 is a good intermediate format to use when importing information that isn't set up to import directly into Access. For example, if you want to add or remove fields, combine or split fields, or use complex mathematical functions to manipulate data before importing it into Access, Excel is a great place to do it.

Tip: If you want to import data into an existing table but the data structure isn't the same as the table structure, it is often easier to import the data into Excel, manipulate it there, and then import it into Access.

Importing or linking to a SharePoint list

You can bring content stored in a SharePoint list into Access in either of two ways—by importing the information from the list, or by linking to the list. The process is the same for both operations.

Importing a SharePoint list creates a copy of the list in the destination Access database. During the import operation, you can specify the lists that you want to copy, and, for each selected list, you can specify whether you want to import the entire list or only a specific view. The import operation creates a table in Access, and then copies the columns and items from the source list (or view) into that table as fields and records. Changes made to the imported data in either Access or Windows SharePoint Services will not be replicated.

If you want to work with data from a SharePoint list in Access but keep the information in both locations current, create a linked table. Linked tables are indicated in the Access Navigation Pane by a blue arrow pointing to a yellow table. Information you update in Access is reflected in the SharePoint list when you refresh the view, and vice versa.

Importing information from a text file

Access can import tabular data (tables and lists) from delimited and fixed-width text files.

In a delimited text file, each record ends in a carriage return, and each field is separated from the next by a comma or some other special character, called a delimiter. If a field contains one of these special characters, you must enclose the entire field in quotation marks.

In a fixed-width text file, the same field in every record contains the same number of characters. If the actual data doesn't fill a field, the field is padded with spaces so that the starting point of the data in the next field is the same number of characters from the beginning of every record. For example, if the first field contains 12 characters, the second field always starts 13 characters from the beginning of the record, even if the actual data in the first field is only 4 characters.

Importing information from an XML, or HTML file

Extensible Markup Language (XML) files are often used for exchanging information between programs, both on and off the Web. XML files are similar to HTML files in two ways: both are plain text files that indicate formatting within tags, and both use start and end tags. However, HTML tags describe how elements should look, whereas XML tags specify the structure of the elements in a document. Also, as its name implies, the XML tag set is extensible—there are ways to add your own tags. Access 2007 can apply a transform script to XML data as you import or export it. Transforms are a type of template used to convert XML data to other formats. When you apply a transform during import, the data is transformed before it enters the table, so you can adapt an XML file to a different structure.

When you import an HTML document into Access, it will parse the document and identify anything that looks like structured data. You can then look at what Access has found and decide whether to import it.

4. Controlling the features available to database users

If your database will be used by people with little or no experience with Access, you might want to control which features are available when the database opens. If you want users to be able to open only one database, you can add one or more options to the Access desktop shortcut and Start menu link. These options can open a specific database, run macros, and perform other tasks.

A more common way to control the user's environment is to set startup options in each database. You can use startup options to control the menus and Ribbon tabs available to the user, the initial form displayed (such as a switchboard), and other features. The startup form can include macros and Visual Basic for Applications (VBA) procedures that run automatically to set other conditions.

Top of Page

5. Assigning a password to a database

You can prevent unauthorized users from opening a database by assigning it a password. Access will prompt anyone attempting to open the database to enter the password. The database will open only if the correct password is entered.

A secondary benefit of assigning a password is that your database will automatically be encrypted each time you close it, and decrypted when you open it and provide the correct password.

Tip: In previous versions of Access, encrypting and decrypting a database was a separate function from assigning a password to it. If you open a database created in Access 2002 or Access 2003 from Access 2007, you will still have the option of encoding or decoding it, which is what the process was called in those versions.

To assign a password to or remove a password from a database, you must first open the database for exclusiveuse, meaning that no one else can have the database open.

Top of Page

6. Securing a database for distribution

When a database is used locally, on a local area network (LAN), or on a wide area network (WAN), you have considerable control over who has access to it. But if you send the database out into the world, on its own or as part of a larger application, you lose that control. There is no way you can know who is using the database or what tools they might have available to hack into it. If this is of concern to you, consider distributing your database as an Access Database Executable (ACCDE) file.

Tip: In previous versions of Access this was called a Microsoft Database Executable (MDE) file. The functionality and creation process are the same. If you open an older (Access 2002 or Access 2003) MDB file in Access 2007, a Make MDE command appears in the Database Tools group on the Database Tools tab in place of the Make ACCDE command that appears when you're working in an Access 2007 database.

Saving a database as an ACCDE file compiles all modules, removes all editable source code, and compacts the destination database. Users of the ACCDE file can view forms and reports, update information, and run queries, macros, and VBA code. They cannot do the following:

• View, edit, or create forms, reports, or modules in Design view.

• Add, delete, or change references to other objects or databases.

• Modify VBA code.

• Import or export forms, reports, or modules.

Access can save a database as an ACCDE file only if it is in Access 2007 format. Access 2002 and Access 2003 databases can be saved as MDE files.

You can't convert a database from ACCDE format to the source ACCDB format, so before saving a database as an ACCDE file, store a copy of the original ACCDB file in a safe place. If you need to make changes to forms, reports, or VBA code, you will have to make them in the original database and then save it as an ACCDE again.

Top of Page