Visual Basic Programmer

Microsoft Access Database Programmer

All about Microsoft Access 2007

The design environment of Access 2007 is very different from Microsoft Access 2003. The Visual Basic Integrated Development Environment 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 of Access 2007

• 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 are back in fashion, as there are less security concerns. 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 automated 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 Attachment data type can only be used with Bound Forms – and too many attached files will cause problems.
• 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.

Setting up 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 using .accdb database files.

• There have been many changes (Vista, Office, Access), and the Access programmer 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!

Why Convert to the new .accdb format?

• The .mdb format will soon become an historical relic
• Conversion to an .accdb database may eliminate some Access limitations
• New features are available - Attachment data types, Multi Valued Lookup Fields and Rich Text fields
• Microsoft Outlook blocks the old .mdb databases due to security concerns
• The .accdb format allows the database to be encrypted

But

• The .accdb format will not work with older versions of Access
• All obsoleted relics of past versions of Access will need to be removed

The new Microsoft Access 2007 File Types