Wednesday, May 30, 2007

Using Advanced Table Hints in SQL Server

This article from explores the benefits and side effects of using three types of table hints when running queries in SQL Server: READCOMMITTED, REPEATABLEREAD, and SERIALIZABLE. The previous article in this series covered NOLOCK and READPAST.

Other SQL Server articles from Tech Republic:

Sunday, May 20, 2007

Focus on Access 2007 for Access Junkies

Access Junkie, Jeff Conrad, Access MVP Alumni and a Microsoft Software Engineer on the Excel - Access Services Team, has established a very useful site devoted to Access 2007 and its new features and technologies. There is a broad range of resources and links available. The best Access 2007 site I have visited.

Jeff has also co-authored Microsoft Office Access 2007 Inside Out (MS Press)

Tuesday, May 15, 2007

Microsoft Office Access 2007 Developer Reference Portal

The Microsoft Office Access 2007 Developer Reference Portal provides conceptual overviews, programming tasks, samples, and references to guide you in developing Access solutions.

The reference has the following sections:

Monday, May 14, 2007

How To Use VBA Variables

Download a free sample chapter from VBA for the 2007 Microsoft Office System (Que) exploring variables and how to use them in your VBA procedures.

Chapter topics include:
  • Declaring variables
  • Avoiding variable errors
  • Understanding variable data types
  • Using array variables
  • Working with constants
  • Storing user input in a variable
Courtesy of

Tuesday, May 08, 2007

Easy User Filtering in Access

A new feature in Access 2007 is on-the-fly filtering of forms.

My Ezy Filter Plus Access Add-In, which has been around for a while, provides more advanced on-the fly filtering for forms and reports, and for Access 97 to Access 2003 as well:
  • Easily filter any existing data form or previewed report in any Access database
  • An add code options allows developers to add Ezy Filter to their applications.
The following graphical representation of Ezy Filter Plus in action in the sample Northwind MDB that ships with Access, highlights the essential features of Ezy Filter Plus and shows you how easy it is to use and implement (click on an image to zoom in):

1. Invoke the Ezy Filter Plus Add-In and click Add Code to add Ezy Filtering to your database:

2. In any Access form or report click the Ezy Filter button on the form view toolbar or popup menu and start filtering:

3. Click the Filter toggle button to return to the unfiltered form:

Buy Ezy Filter Plus now and get the Ezy Reporter Add-In for free - a 50% saving.
Security In Access 2007

Workgroup security has been dropped from Access 2007, which I personally see as a big negative.

In many Access applications users can create new objects at runtime, such as queries, forms and reports. A compiled MDE/ACCDE will not cut it as you can't protect your developer objects and code, and allow users to create their own objects. In prior versions of Access 2007, you could selectively protect developer objects and give users permissions to create new objects, using the very robust Access workgroup security features. This means that many of my Access application and add-ins will not be distributed in the Access 2007 format. One wonders if the Microsoft Access development team considers the views of developers and users.

Having said this, MSDN has just published an article by MVP and fellow-Aussie, Garry Robinson, Security Considerations and Guidance for Access 2007, which is the first comprehensive discussion of security in Access 2007.

Gary has also written a valuable book on pre-Access 2007 security: Real World Microsoft Access Database Protection and Security.

Thursday, May 03, 2007

Keep Your Custom Access Application Icon Loaded

The Access StartUp dialog file path entry for a custom icon is hard-coded, so if you distribute your application and the absolute path is not be replicated in the user's PC, your custom icon will not load with your database.

The following code in a module will allways load your custom Icon provided the ICO file is in the same folder as your application database:

Option Compare Database
Option Explicit

Public Function fnAddAppIconProp()
'Call this function from the OnLoad Event of your StartUp form

Dim intX As Integer
Const DB_Text As Long = 10
intX = AddAppProperty("AppIcon", DB_Text, CurrentProject.Path & _

End Function

Function AddAppProperty(strName As String, varType As Variant, varValue _
As Variant) As Integer

Dim dbs As Object, prp As Variant
Const conPropNotFoundError = 3270

On Error GoTo AddProp_Err

Set dbs = CurrentDb
dbs.Properties(strName) = varValue
AddAppProperty = True


Exit Function


If Err = conPropNotFoundError Then
Set prp = dbs.CreateProperty(strName, varType, varValue)
dbs.Properties.Append prp
AddAppProperty = False
Resume AddProp_Exit
End If

End Function

Save as PDF or XPS Add-In for Office 2007

XPS is a file format, like PDF, that preserves document formatting in a fixed layout and ensures that when the document is viewed or printed, it will retain the exact original format. You can save documents in XPS format in Office 2007 programs after installing the Microsoft Save AS PDF OR XPS Add-In for Office 2007 programs, which you can download here.

Office 2007 Terms You Should Know

Mini-Glossary: Office 2007 Terms You Should Know From