Last Updated Thursday March 24, 2011

home

This page is intended to report differences found in Access 2007 compared with earlier versions, plus other information that may be of use.

The Faculty's labs have used Office (and thus Access) 2007 from September/October 2007.
Most of the Access functionality and VBA code should work as before, but the interface is radically different. Project students will need to be aware of this and allow time to familiarise themselves with the new interface.

Click here for a list of What's New in Access 2007. (summary - headings only)  

Click here for a list of Access 2007 specifications.

When you start up a database there will be a message bar below the ribbon that states: "Security Warning certain content in the database has been disabled". You will need to click on the Options button and then choose 'Enable this content' in order to run VBA code and macros. If you don't do this then clicking on buttons, for example, will not work! You can use the Microsoft Trust Center to specify folders (and optionally subfolders) that you trust - databases in these folders will then open without the Security Warning.

There's a good explanation of how to do this at http://www.towson.edu/adminfinance/OTS/trainingdoc/shr29-mso2007.asp . (Note - Towson university, Maryland, USA owns the copyright to all materials at this link).

There's also information about this (and much else) at http://msdn.microsoft.com/en-us/library/bb421308.aspx under the Enabling Executable Content in Your Databases link,

Items below are in the following categories:

• General
  - overlapping windows
  - embedded logos and attachments
  - multi-valued fields

• Wizard code

• Forms and reports

• Forms only

• Reports only

• Executable/MDE/ACCDE files

• DAO (Recordset) code

• Custom Menus

• Other Links

General:

1. Instead of overlapping windows for forms, tables and the like, objects are shown on tabs. If you want to change back to the overlapping windows used in previous versions then you do this via Access Options (click on the icon in the top left-hand corner then click on the Access Options link at the bottom of the popup box).
   

2. Two new features are the ability to add logos (images) to forms and reports, and to embed attachments (images, Word documents and the like) in a record. These are very easy to use (there is now a new table field datatype for Attachment) - just follow the instructions in Access Help, and experiment for yourself by creating a table and a form based on the table, then attaching images, documents, etc via the form.
   While these features may be OK for a novice database developer for a small personal database, I have my doubts about their suitability for larger commercial databases (not to mention backups and the size limit that applies to student personal areas on the University network). As the images and attachments are embedded the database will grow very large very rapidly. There is a size limit to an Access database of 2GB (look in Access Help for "Access 2007 specifications" - this is the 5th item in the list on my machine). This item says that you can get round the size limit by employing multiple back-end databases - but this seems a messy 'solution' to me.
   Embedding files in this way can also cause problems if, for example, a company logo changes. If the logo is embedded in all forms and reports then each of these objects will need to have the logo file replaced. If the logo is linked in (thus the file exists outside the database and is linked in when each form or report is opened) then all that the user needs to do is replace the logo file with a new one, using the same file name. Another example could be if attached documents change - these would have to be re-attached for the database to have the latest version. If they were linked then the database would always pick up the latest version.
   Look at the example databases page for 'Linking Images' and 'Opening Word Documents' - these methods would normally be more suitable for project and placement databases. For a project, you would need to justify any use of attachments. Student projects (year 2 and final) should not normally use this feature. Any use would have to be discussed and justified.
   You might also find the discussion at http://www.access-programmers.co.uk/forums/showthread.php?p=566993 useful.
    

3. Another new feature is that of multi-valued fields. These avoid novice database developers from having to grapple with many-to-many relationships, but can also limit the functionality of the database - and go against one of the basic rules of a relational database. See the What's New document for details and my comments. These fields should NOT be used in student assignments or projects.
   Perhaps this feature is intended for people who are not database designers (in which case I think they should not really be trying to implement a database in the first place...).
   July 2008 - I've done some Googling to see what others feel about this new feature:
   (a) http://www.theregister.co.uk/2006/07/18/multivalued_datatypes_access/ - excellent discussion (i.e. the writer agrees with me!) on why this feature should not be used.
   (b) http://office.microsoft.com/en-us/access/HA101492971033.aspx - Microsoft's guide.
   (c) http://www.databasedev.co.uk/multivalued-fields.html - databasedev's guide.
   (d) http://msdn.microsoft.com/en-us/library/bb258183.aspx - Manipulate Multivalued Fields With DAO
   (e) http://allenbrowne.com/Access2007.html - views and known bugs.
   (f) http://books.google.co.uk/books?id=61Ps8-AU12IC&pg=PA232&lpg=PA232&dq=multi-valued+fields+and+Access+2007&source=web&ots=gTy9wfRRQa&sig=auauHWZ5rO7n02sl-TzEJxYuVhA&hl=en&sa=X&oi=book_result&resnum=9&ct=result - someone else who doesn't like them.
   (g) http://www.eggheadcafe.com/software/aspnet/30144866/multivalued-field-functio.aspx - Q&A re porting this feature to Access 2003 (can't be done).

Wizard code:

1. If you create your database with a 2000/2002/2003 file format then you should be able to port it between the new and previous versions of Access (which can help if you have an older version on your home machine or want to ask a staff member [who may well be using the previous version] for help).
    

2. If you create your database in an Access 2007 format the wizards will generate macros rather than the code we have been used to. If you want to see/use the code then there is a facility to convert macros to code - see the document attached here. Note that the code is slightly different from 2003 and earlier versions.
    

3. If you have created your database in an earlier format then you should still get wizard code created as before, even if using Access 2007.

Forms and Reports:

1. There is a new 'feature' when creating wizard forms and reports - the fields added are all set in a fixed grid (in a very simplistic and poor layout). In order to move the fields around to get a decent design for your form  or report (and you will need to do this) you need to do the following:
   (a) right-click on the control(s) you wish to move
   (b) choose 'Layout' from the drop-down list
   (c) choose 'Remove' from the second list.
   March 2010 This feature seems to have been removed from Access 2010.
    

2. Using logos - see discussion under General above.

Forms only:

1. There may be problems with using the Forms Collection or DoCmd.ApplyFilter (or when using them both together). See the information about Login Filter problems in the code for the Further VBA Trainer.
    

2. It appears that the Access 2007 command button wizard generates the code for a Close form button that uses the Dirty property. This can cause an error at run-time - see Access FAQ 36 What does the message "You entered an expression that had an invalid reference to the property Dirty" mean?

      

3. If you use the query design window to change the RecordSource for an object such as a combo or list box, when you close the window you are prompted to save the changes to the RecordSource. But in Access 2007 the changes made do not seem to save (they do in earlier versions). So, go to the SQL window and copy the SQL then paste it directly into the RecordSource property for the object.
    

4. April 2009 - a student has brought to my attention that Combo Box lists based on a 'value list' can now be edited at run-time. The LimitToList property must be set to Yes/True. See http://office.microsoft.com/en-us/access/HA101130521033.aspx, the item headed "Edit a value list in Form view". The article then goes on to describe how to switch this facility off if required.
   This new facility does not appear to need, or use, data in the RowSource property of the combo box. It appears that the list is held elsewhere. Adding to list items seems straightforward, however, it is possible to remove an item from the list without getting an error message stating that data now fails referential integrity!
   My personal opinion is that Value List combos are best restricted to items that are highly unlikely to change (Gender [Male/Female], for example) and the user should not be allowed to edit them. Anything else, which will normally involve data that is applicable to the application, should be held in a table and the combo box should be based on the table, with referential integrity enforced. If the user is to be allowed to add entries at run-time (not always desirable; it depends on the data and the application) then this is best done using VBA, perhaps by the method discussed in section 6.3 of the Getting Started VBA Trainer. Admin facilities should be provided to edit and delete entries (the latter checking to see if there is data that uses the value; DCount could be useful for this). If the user is not to be allowed to add to the entry at run-time then this Admin facility would also add new entries. Admin facilities may need to be restricted to certain users (two possible methods are to use a separate database linked to the table in question or via permissions at log-on).
   Perhaps this is another of Access's new facilities for naive users that appear to hide what's going on and why.

Reports only:

1. When running code by clicking on the report icon in the navigation pane you will open the report in Report View and not all code events will fire up - this includes the Detail Print event. You need to open the report in Report Preview to be able to run all the code. (If you are opening the report from a preview button on a form, all will be OK). I have tried looking through Access Help to see what the difference is between Report View and Report Preview, but I can't find anything there (Access help seems to get more unhelpful with every new release - it lists loads of stuff you don't want and very little that you do).
     

2. See the document attached here for information about locating the sorting and grouping information. this also mentioned on the What's New document.
    

3. It is now possible to save an Access report as a PDF file. See the document attached here.

Executable/MDE/ACCDE files

This procedure works differently in Access 2007 from previous versions - now you create an ACCDE file if you have an Access 2007 format database. See section 6 of article at http://office.microsoft.com/en-us/access/HA102190051033.aspx .

 Extract from: http://office.microsoft.com/en-us/access/HA100678311033.aspx

July/August 2008 - I have just discovered, when running an Access 2002 MDE file under Access 2007 that you can get at the Navigation Pane and see/change tables (design and data!) and create/alter queries (even though I had removed these options in the Access 2002 .mdb version).

By following the Access 2007 Security Paper instructions (see pages 4 to 8 of the document) information can be hidden from casual users, but the author suggests using SQL Server to protect data in the back-end database(s). I used the advice on pages 4 to 8 on my Access 2002 database using Access 2007, then transferred it to a machine with Access 2002 to create the MDE - this now has various items 'hidden' as I wanted when run under Access 2007. (Some staff here still have Access 2002-2003 on their machines).

But I'm puzzled as to why Microsoft makes it so easy for a user to see the raw data back-end tables from an MDE or ACCDE front-end...

4th August 2008 - some more links:

http://www.vb123.com/Toolshed/06_access/securitypaper2007.htm - Access 2007 Security Paper by Garry Robinson for MSDN. Shows how to hide objects on the navigation pane so that non-technical users cannot see the data you wish to keep hidden, plus a lot else about database security. This looks to be an interesting and useful article.

DAO (Recordset) code

See VBA FAQ #25 on the Frequently Asked Questions page.

Custom Menus

previous versions of Access allowed you to create and edit custom menus (via Tools-->Customise), which you could then set up as a new toolbar.

Access 2007 will allow existing custom menus to be used, but it does not appear possible to edit these menus (!).

The following links may shed some light:

• http://msdn.microsoft.com/en-us/library/bb258174.aspx

• http://msdn.microsoft.com/en-us/library/bb203849.aspx#OfficeAccess2007MigratingExistingApplications_Conclusion

• http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_24247256.html

• http://www.databasejournal.com/features/msaccess/article.php/3790286/Setting-Up-Right-Click-Menus-for-Access-2007.htm

• http://groups.google.co.uk/group/microsoft.public.access.modulesdaovba/browse_thread/thread/5412a3dfc5524c89

I have recently (spring 2009) hit this problem with a legacy database. Fortunately I have Access 2002 at home so could port the database to there to amend the menu, then re-install the database where it is to be used. Judging by forums on the Internet I am not the only one with this problem...

If you no longer have Access 2002 available then you may have to replace the custom menu by a more conventional (form-based) menu of your own, and set this to open when the database opens. If you want to simulate the way the custom menu worked then you might like to see the database linked here. A nicer method could be to use combos or list boxes, but I haven't worked out how to do that. Added 22/6/2010.

A link to various other sites that may help with creating custom menus on the ribbon: http://accessjunkie.com/faq_30.aspx added 11/8/2010.

Other Links

Some links here that I have discovered. Please email the site owner if you find others that could be useful.

1. http://allenbrowne.com/Access2007.html. - "Microsoft Access Tips for Serious Users" - very useful for all sorts of things!

2. http://accessjunkie.com/faq2007.aspx - "This page presents answers to some of the most frequently asked questions concerning Microsoft Office Access 2007. These questions are loosely organized by category."

3. http://blogs.msdn.com/access/archive/2007/12/18/access-2007-tips-and-tricks-from-the-access-team.aspx - "Access 2007 tips and tricks from the team"

4. http://msdn.microsoft.com/en-gb/library/bb905409.aspx - "Developer Considerations for Choosing File Formats in Access 2007"