Tips and tricks from the Access team - part 1

  • Article

I have been working on a three part series of non-obvious tips and tricks for using Access 2007. The other series will be on designing forms and reports and my favorite keyboard shortcuts. I put together a rough draft of this series and sent it around to the Access team. Fortunately, many team members responded with some good additions.

Enjoy and have fun building great applications!

Clint

Open Last Database

Office Button | Access Options | Advanced Tab in the Advances section there’s a new option to have Access automatically open the last opened database on boot. –Scott (developer lead)

New Database Settings

It is possible to customize the default database settings for new databases. Our UA team and Allen Browne have nice write-ups about it—so I won’t repeat it.

https://office.microsoft.com/en-us/access/HA100378371033.aspx?pid=CH100948231033#blanktemplate

https://allenbrowne.com/ser-43.html#dbtemplate

Table and Field Templates

Table and field templates are quick ways to create tables and new fields with a number of property settings already set. The field templates have a number of core field types and fields taken from common schemas.  It is possible to override and extend the fields in the field templates task pane and the tables that get created from the Create | Table menu.

The fields and tables are created based on the ACCFL files found in the drive:\Program Files\Microsoft Office\Templates\1033\Access directory. These files are XML files based on the same format if you exported a table as XML and XSD. Any schema files stored in the directory named ACCFL will show up in the fields task pane. Unfortunately, you can’t add additional tables or rename existing tables (painful cut).  However, if you are always creating Order tables and never Issues—you might want to change Issues to Orders. Export your Orders table to XML/XSD. Rename the XSD to Issues. ACCFL and save it to the directory. Next time you create an Issues table it will be an Orders table.

Here is a link to a UA article on the subject.

New ADP

However, I frequently see questions about how to create a new ADP. It is a bit buried in the new getting started interface. For the users that need to create new ADP files, here are the steps:

1. Click on the new Database in the Getting Started screen.

2. Click on the folder icon next to the file path.

3. In the files of type choose Access Data Project.

4. Name the file and click Create

New file dialog with option for ADP.

Here is a link to a UA article on New ADPs for more information.

There is a hidden menu in the navigation pane that is quite useful. Right click on the navigation pane title for this menu:

Navigation pane context menu. 

My two favorite options in this menu is the Search Bar and the Sort | Custom Sort options. The search bar performs a Like *value* filter on the object name. This is really handy if you have a naming convention and want to quickly narrow down the displayed list. The Custom Sort option allows you to sort the objects in a custom order.

Another thing, if you have focus in the navigation pane CTRL F will automatically turn on the search bar. You can use the down arrow to select items and the enter key to open the object. It makes it pretty easy to find and open objects with the keyboard, especially if the object has a unique string in its name.

Navigation pane search box.

Filtering and Queries

As a user interactions designer, I’m convinced context will continue to drive UI innovation. Contextual tabs in the ribbon are one example of some great work done in this space. Filtering is another place inside Access that being smart about context really improves the user experience. In the past, text in the context menu was Filter by Selection and Exclude Selection. Access 2007 I clarifies context through vocabulary. Selected next now shows up in the context menu to provide more clarity. Did you know that the right-click context filtering options change based on your selection? Give it a try—look at the right click menu when the first and last character is selected. Now try selecting the whole word. You will also notice the filter options change based on the data type.

The new filtering UI in Access makes it really easy to quickly create complicated WHERE clauses in queries. Save As for queries will flatten the active filter into the where clause. Open your favorite query and filter a date column using the This Month filter. Click on the Office Button | Save As.  Give the new query a name.

Copy and Paste From Excel

Many good lists start in Excel and need to transfer into Access. Here is a simple way to get data into Access quickly.

1. Copy the data from Excel.

2. Create a new table inside Access

3. Click on the Add New Field

4. CRTL V – just like that you get a table inside Access. The new Table Tools contextual ribbon allows you to make the most common schema changes without flipping into design.  

Paste from Excel. 

You should also notice the behavior of copy and paste from Excel to Access has been improved in other areas.

Import From Excel

When importing from Excel files you can now override the data-type for a field. Sorry, I missed setting the data-type on link. Hopefully, the Access team will do that feature in the next version.

Outlook Reminders for Saved Import and Export Operations

You might have noticed at the end of an import or export operation there is a victory dialog. There is an option asking you if you want to save the operation. You might notice at the bottom if you want to create an Outlook reminder.

Export victory dialog. 

This feature was designed for people that need to publish a report every couple days. You can use the Outlook reminders feature to remind the user to perform the task on a scheduled basis. The task has a Run Export/Import command that will repeat the recorded operation.

Outlook task reminder. 

Here is a link to a good UA topic on the subject.

SharePoint Performance

The team spent a bunch of time improving SharePoint performance but it still is influenced by the speed of the connection to the server, server performance, and size of the data. I have found working with SharePoint data offline and using the synchronize command a far better experience.  This works much faster unless you are working on data that everyone else is changing. The conflict resolver UI gives you the chance to override server changes or discard your own changes.

In the future, Access should provide the option for merge changes. Oh well, something for vnext.

SharePoint also has a flag called Cache List Data. This stores a copy of the list data in the database file. When the database opens the Access database engine only requests changes from the server. It is a nice performance win for the client and server.

One last thing… If your list on SharePoint is getting really large—you might consider linking to a view and not the list. This works well for scenarios where you have archived data in a table that isn’t necessary for your view. There is a trick—you can’t bind to the view using the UI. Use the TransferSharePointList method to create the link table. It has a property for the view.

Templates

You just created the projects template and think,

“Wow, MS did a pretty good job designing this template. I would really use it if only they had named the tables and fields something different (Projects should be called tblSpecs for my purpose).”

Name fix-up is helpful at renaming objects and field names in templates. I know, I know—-name fix-up has a terrible reputation. In this release we made a significant investment in the name fix-up code base. Why? Early on we made the decision to use name fix-up to create localized templates. Every template we ship has an internal structure of English names. When a localized template is instantiated Access uses the name map to rename all objects from their English name to the localized name.

What does this mean to you instantiating a template? It means that if you instantiate the projects template and rename projects to specs, name fix-up will make sure all references to the project table gets fix-up to specs. Access 2007 fix-up references in embedded macros, labels on field names, and conditional formats. The code was extremely well tested because if name fix-up failed a localized template would be broken. This commitment to use name fix-up dramatically increased the amount of testing and overall reliability of the feature.

Zac’s book on templates The Rational Guide To Microsoft Office Access 2007 Templates is the definitive guide. If you are thinking about building a template—this is the book to get.

Template Internals

Want to see what’s inside a template file, the gory details of what makes up the .accdt file format?  Just rename the package to .zip and double click to open.  Inside you’ll find all the different parts of the database represented in XML and TXT formats, along with resources like the picture Access displays on the template’s tile in Getting Started. (Zac—form and report program manager)