Modifying Table Templates

In Chapter 4, you learned how to get a jump-start on creating a new table by using one of the five table templates available in Access 2007-Contacts, Tasks, Issues, Events, and Assets. You started creating your contacts table in that chapter by using the Contacts table template as a base, and then you modified the resulting table to meet your specific needs. To define these table templates, Access uses special documents coded in XML that describe the table and field properties. Access reads this schema information when you click one of the five template gallery buttons on the Ribbon and builds your table based on these properties. In the following sections, we’ll show you how to modify the templates so that they generate tables that more closely match what you want.

Adding a New Field to a Table Template

The five template files have .accfl extensions and are located in the Program Files\ Microsoft Office\Templates\1033\Access folder on your OU drive in a default 2007 Microsoft Office system installation. You can open the .accfl files using Notepad or an XML reader. For our tests, we used Notepad to edit the XML.

Note 

You can download Microsoft Visual Basic 2005 Express Edition from Microsoft to help you create and edit XML files: http://msdn.microsoft.com/vstudio/express/vb/.

Caution 

If you want to follow along in this section to modify these template files, be sure to make a backup copy and place it in a different folder. If you leave the backup copy in the same folder as the template, Access creates duplicates of every field for that template.

When you used the built-in template to build your contacts table in Chapter 4, you might remember that Access did not automatically create a middle initial field-you had to manually add this field to the table design. Wouldn’t it be nice if Access added this field every time you used the Contacts table template? By changing the XML schema information in the Contacts.accfl file, you can have Access create a middle initial field . for you. Before you begin, let’s take a look at the original fields that Access creates with this template. Create a blank new database, called ModifyTableTemplate, and save it in a trusted location. Next, close the default new Table1 that Access creates for new databases. Now click the Table Templates button in the Tables group on the Create tab, and then click Contacts. Access creates a new table called Table1 with 18 fields to describe a contact. Switch to Design view, and save the table as OriginalContacts when Access prompts you for a name. As you can see in Figure 23–16, Access did not create a middle initial field for you.

Figure 23–16: The original Contacts.accfl file includes schema information for 18 fields.

Close the database, and now let’s change the schema information to create a new middle initial field. Open the Contacts.accfl file with Notepad or an XML editor, and scroll down until you come to the following area that describes the First Name field:

 </xsd:element> <xsd:element name="First_x0020_Name" minOccurs="0" od: jetType="text"     od:sqlSType="nvarchar">   <xsd:annotation>     <xsd:appinfo>       <od:fieldProperty name="ColumnWidth" type="3" value="-1"/>       <od:fieldProperty name="ColumnOrder" type="3" value="0"/>       <od:fieldProperty name="ColumnHidden" type="1" value="0"/>       <od:fieldProperty name="Requi red" type="1" value="0"/>       <od:fieldProperty name="AllowZeroLength" type="1" value="0"/>       <od:fieldProperty name="DisplayControl" type="3" value="109"/>       <od:fieldProperty name="IMEMode" type="2" value="0"/>       <od:fieldProperty name="IMESentenceMode" type="2" value="0"/>       <od:fieldProperty name="UnicodeCompression" type="1" value="1"/>       <od:fieldProperty name="TextAlign" type="2" value="0"/>       <od:fieldProperty name="AggregateType" type="4" value="-1"/>       <od:fieldProperty name="WSSFieldID" type="10" value="FirstName">       <od:fieldProperty name="GUID" type="9" value="8LewvB2Zg02/47ANPW8KHA=="/>     </xsd:appinfo>   </xsd:annotation> <xsd:simpleType> <xsd:restriction base="xsd:string">   <xsd:maxLength value="50"/>     </xsd:restriction> </xsd:simpleType>

Inside Out-Forming Field Names with Spaces in XML 

You might have noticed that the name assigned to the First Name field includes a strange ×0020 specification in the middle. Names coded in HTML or XML cannot embed spaces or special characters, so you must enter special codes if you want to include a space in the generated name. The hexadecimal code for a space in the ASCII character set is 20 (decimal 32), so the _x0020_ embedded within the name instructs XML to use an actual space in the name. If you scan down further in the .accfl file, you’ll find that the character / is embedded in State/Province using the code _x002F_ (decimal 47). You can find the ASCII decimal codes for special characters in Access Help by searching for ASCII character chart

You could type similar XML to create another field, but the First Name field has most of the same properties that we want to set for the middle initial field. To make the task easier, let’s make a copy of this code and change what we need. Highlight and copy all the previous code to the Clipboard, place your insertion point just after the </xsd: simpleType> line, press Enter, and then paste the code back into the file. You now have a duplicate entry of the First Name field.

In this second copy of the XML code, you need to change the field name and the maximum length. Look for the following line of code that has the field name:

 <xsd:element name="First_x0020_Name" minOccurs="0" od: jetType="text" od:sqlSType="nvarchar">

Change the element name to ="MiddleInitial" (without any spaces) in order to have Access name the new field MiddleInitial. You’ll also need to make the same change in this line of code:

 <od:fieldProperty name="WSSFieldID" type="10" value="FirstName"/>

Change the value to ="MiddleInitial" to complete changing the field name. To change the length of the field (a middle initial should be restricted to one character), find the following lines of code:

 <xsd:restriction base="xsd:stri ng">     <xsd:maxLength value="50"/> </xsd:restricti on>

Change the value 50 to 1, and then save and close the Contacts.accfl file.

Note 

Although the table templates clearly specify the MaxLength property for text fields, the initial release of Access 2007 fails to apply this property. As a result, all text fields in templates are created with the maximum length of 255. You should change this property of any text field that you add to a template in anticipation of fixing this problem in future updates to the product.

Open the ModifyTableTemplate.accdb database you created earlier, click the Table Templates button in the Tables group on the Create tab, and then click Contacts. Access creates another table called Table1 and opens it in Datasheet view. Switch to Design view, and name the table ContactsInitial. In Figure 23–17, you can see Access creates the MiddleInitial field and places it after the First Name field. Each time you use the Contacts table template from this point, Access creates this additional field.

Figure 23–17: Access creates the MiddleInitial field because you added XML schema information.

Modifying a Field in a Table Template

You can also modify the existing field properties by changing the XML in the .accfl files. You’ve noticed by now that all the field names for the five table templates include spaces-Last Name, First Name, and so on. We recommend you do not include spaces in your field names unless you plan to migrate the database to Windows SharePoint Services (version 3). You could take the time to change all the field names whenever you need to use the table template commands, but if you change the XML schema information, Access can do the work for you each time.

Close the ModifyTableTemplate.accdb database you’ve been working on if you still have it open. Open the Contacts.accfl file in Notepad or an XML editor. For this example, let’s change the properties only for the First Name field so that Access does not create a space when it builds this field.

If you look through the XML code near the beginning of the file, you’ll notice there is property information for all the indexes in the table. Look for the index called First Name shown next.

 <od:index index-name="First Name" index-key="First_x0020_Name "primary="no" unique="no" clustered="no" order="asc"/>

In the XML schema, the First Name field is always referenced as “First_x0020_Name.” If you eliminate the extra characters between the two words in each instance, Access creates the field with the name FirstName. It could be tedious to manually search through all the XML for each instance of First_x0020_Name. To make your job easier, you can use the Replace command in Notepad, and enter First_x0020_Name in the Find What box and FirstName in the Replace With box. Click Find Next, and then click Replace for each instance of First_x0020_Name that Notepad highlights. (Note you could also change the index name from First Name to FirstName if you prefer.) You should find two instances you’ll need to change. Save and close the template file when you’re finished.

Open your ModifyTableTemplate.accdb database again, and let’s see the results. Click the Table Templates button in the Tables group on the Create tab, and then click Contacts. Access creates another table called Table1 and opens it in Datasheet view. Switch to Design view, and name the table ContactsRevised. In Figure 23–18, you can see Access created the FirstName field with no spaces in the field name. Each time you use the Contacts table template from this point, Access creates the FirstName field with no spaces. If you like, you can open the template file again and remove all the spaces and special characters in the other field names. By examining the XML in the five .accfl files, you can create additional fields and modify existing field properties in any of the table templates to tailor them more to your needs.

Figure 23–18: The FirstName field now has no spaces when you use the revised table template.