Whats New in Access 2007 VBA?

What’s New in Access 2007 VBA?

Access 2007, in general, has changed substantially from Access 2003, although Access 2007 VBA has not changed much from Access 2003 VBA. One significant change with Access 2007 is the introduction of a new ACCDB file format, instead of the MDB file format of the past. Another significant change to Access 2007 and all other Office 2007 programs in general is the new user interface design. The new user interface for Access 2007 uses ribbons on the toolbar that replace the layers of menus and toolbars found in earlier releases. Actions or options on the ribbons are grouped together in a logical fashion based on the task they accomplish. While the user interface for Access 2007 has changed significantly, the Visual Basic Editor for Access 2007 has remained mostly unchanged. The Visual Basic Editor opens in a separate window from Access 2007. In developing a VBA application, you will work with both Access 2007 and the Visual Basic Editor.

Some examples of the additional changes to Access 2007 in general (but not Visual Basic for Applications) include:

• Templates – Many prebuilt templates are included to get you started quickly with certain common applications.

• Rich text in memo fields – Text can now be formatted with options, such as bold, italic, colors, and the like.

• Navigation pane – The navigation pane replaces the old database window and contains all of the database objects, such as tables, forms, and so on. What is interesting about the navigation pane is that it also allows you to create custom groupings of objects, such as tables, forms, reports and so on to a particular meaningful grouping, such as Products.

• Embedded macros – Embedded macros support simple variables, some looping, and even some error handling. You can also embed a macro within a control’s event property. For example, if you need a button to open a form, you can write a one-line embedded macro instead of using VBA code to accomplish this simple task. In other words, you can save VBA for the more complex coding tasks and can use macros for some of the simplest tasks.

• Custom ribbons – You can create custom ribbons for the toolbar, since ribbons are built dynamically from XML contained in hidden system tables in the Access database.

Another change that is likely of interest to an Access VBA developer is the change in user-level security. With the Access 2007 ACCDB file format, user-level security is not supported. A database password can still be used to protect the database, but it is no longer stored in the Access data file. If more comprehensive security is needed beyond a database password, then the ACCDE file format (which replaces MDEs) can be used to protect your code, forms, and reports. The ACCDE format, however, does not provide any specific security on the data stored in the application. In addition to removed user-level security, replication has also been removed for ACCDB files. In other words, you cannot replicate data and design changes between databases using the ACCDB file format. A workaround for user-level security and replication is to use the Access 2000 or Access 2002–2003 MDB format when working in Access 2007.

Tip 

Note that user-level security is not the same concept as the security model you create for your Access applications. You can store user names, passwords, and credentials in your database and implement a security model for your application that uses these stored values. The prior section on Access security discusses the intrinsic security features of Access.