Visual Basic Programmer

Access Database Programming

All about Microsoft Access 2007

The design environment of Access 2007 is very different from the previous release. The Visual Basic Integrated Development Environment (IDE) is (thankfully) unchanged.

Toolbars and Menus have been replaced by the Ribbon. There are minimal productivity gains, if any, from the new Ribbon. The Ribbon can be frustrating when trying to find routines. The Ribbon may need to be minimised or hidden, as it occupies much of the screen.

The second Service Pack for Microsoft Office is now available. Many of the issues with the original release have been resolved. There are still some issues from past Access versions that have not been fixed.

New Features

• The Split Form view combines Form view and the Datasheet view.
• There can be alternating colours on Forms and Reports.
• There is a new Layout View which allows live data to be viewed.
• The MVLF (Multivalued Lookup Field) allows many-to-many relationships.
• The Attachment data type allows for the storage of compressed binary data.
• There is a new Microsoft Office Button and Navigation pane.
• The Navigation pane has a new option "Tables and Related Views". This will display the objects that use a Table, like Forms, Queries, Reports, etc.
• Text and Memo fields have a property that allows for rich text – like bold, italic or colour. The fields are stored as HTML, not the .rtf format type.
• There are new facilities for PDF printing. On the Ribbon, select External Data/PDF or XPS.
• Date/Time fields have a calendar icon that allows the date to be selected.
• Macros now respond to Events, and can be embedded in a Form. They will work without security restrictions or security messages. Macros are intended to make life easier for the novice or amateur programmer. Despite the improvements, the VBA coding language is still far better for most automation procedures.
• For simple systems, the Navigation pane can replace a Menu (Switchboard) form.
• For the first time, all ADO (Active-X Data Objects) and ADOX libraries can be found in the References.
• There is a new Collection called TempVars. This allows variables to be maintained even after a crash.

But …

• Data Access Pages are no longer supported – use ASP.Net instead.
• The Attachment data type, MVLFs and Rich Text fields are not supported by SQL Server.
• The Microsoft Office Button will allow users to make changes to the functionality of the database.
• DAO (Data Access Objects) is still available – but ADO is needed for accessing external data sources, like a SQL Server Back-End database.
• The Attachment data type is incorporated into the database and can dramatically swell the database size. It is best to store only the Windows file name.

Trusted Folders

Security messages are a pain. Whenever a Macro (or Function or Procedure) is run, the message "A potential security concern has been identified" appears. To get rid of these messages, the Access database will need to be saved to a Trusted folder.

• Open Microsoft Access 2007
• Click the Microsoft Office Button (top left hand side).
• Click "Access Options"
• Click "Trust Center"
• Click "Trust Center Settings"
• Select "Trusted Locations"
• Click "Add new location"
• Enter the folder where the database is to be kept.
• Enter a description and then click OK.

Place all the database files into the Folder that you have marked as Trusted. You should not get any Security warnings again for the database.

Upgrading using .mdb database files.

• Access 2007 accepts the .mdb files from Access 2003, and the upgrade is painless – with a few exceptions.
• Code derived from Access 97 may not work – like File Dialogs. The code will need to be upgraded.
• Controls derived from Access 97 may not work. The not very helpful message is "There is no object in this control". Copy, then paste the Form. Using the newly copied Form, the message should disappear. Any missing control (that caused the problem) will need to be replaced with an Access 2007 version.
• You may have to turn off UAC (User Account Control), in order to get some features (like SendKeys) to work.
• Smart Indenter is now standard.
• Data Access Pages will be shown in Internet Explorer, but you cannot modify the design.

Upgrading to .accdb database files.

• There have been many changes (Vista, Office, Access), and one should expect some problems in converting from Access 2003.
• The file size on converting from the old format to the new, is unchanged.
• A new Switchboard can be created using Macros. It is better to use the VBA version that is created with the .mdb database files.
• You will gain the ability to use complex data types – attachments and multivalue fields.
• The .accdb format supports the new Access Themes.
• User-level security is not supported. This will involve some redesigning before conversion.
• Online documentation and user solutions on the Internet are now plentiful.
• If all Forms are displayed as maximised, here is what to do to restore the original size:
  • Click the Microsoft Office Button
  • Click Access Options
  • Click Current Database
  • Find Application Options/Document Window Options
  • Select Overlapping Windows
  • Click OK
  This will remove the tabbed option which uses a full screen.

All conversions should be thoroughly checked!

The new Microsoft Access 2007 File Types