Using Advanced Table Hints in SQL Server

This article from TechRepublic.com 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:

• Experiencing the power of SQL templates
• Building a data values dictionary with SQL
• Using recursion in stored procedures
• Develop more quickly by re-using databases
• Recovering from hardware upgrade problems with SQL
• Create a histograph in SQL Server

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)

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:

• What's New New members by object and in alphabetical order.
• How Do I... in Access 2007 A map of how-to topics of custom Access solutions.
• Concepts Important concepts for developing custom Access solutions.
• Access Object Model Reference
  
• Access Macro Actions Documentation for all of theAccess macro actions .
• Microsoft Data Access Objects (DAO) Reference
  
• Microsoft ActiveX Data Objects (ADO)
  
• Microsoft Jet Replication Objects (JRO)
  
• Microsoft Access SQL Reference
  
• Microsoft Access Database Engine Error Reference
  
  

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 TechRepublic.com.

Latest SQL Server Updates

How to run a SQL Server 2005 Integration Services package as a SQL Server Agent Job

FIX: A gradual increase in memory consumption for the USERSTORE_TOKENPERM cache store occurs in SQL Server 2005

FIX: The Check Database Integrity task and the Execute T-SQL Statement task in a maintenance plan may lose database context in certain circumstances in SQL Server 2005 builds 3042 through 3053

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.

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 & _
"\myapp.ico")
Application.RefreshTitleBar

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

AddProp_Exit:

Exit Function

AddProp_Err:

If Err = conPropNotFoundError Then
Set prp = dbs.CreateProperty(strName, varType, varValue)
dbs.Properties.Append prp
Resume
Else
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 TechRepublic.com