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

NEW v1.2 Ezy StartBar for Windows
A faster more versatile Start Menu for your Desktop



Ezy StartBar for Windows v1.2 is now available. After feedback from users, I have updated this very popular download, which has been listed by many software sites.

Ezy StartBar adds a supplemental Start Menu SideBar to your Windows Taskbar, and provides quick access to groups of menu items. It offers more flexibility than the standard Windows Start Menu, and the number of links is virtually unlimited. You can manage Ezy StartBar exclusively using your mouse.

Ezy StartBar behaves like the standard Start Menu, but also allows you to create groups of shortcuts, which can be added, edited and deleted on the fly, with full drag and drop functionality. You can drag shortcuts onto any group in the SideBar when Ezy StartBar is pinned to your Desktop.

A link to any program, file, folder, shortcut, or URL can be added to the Ezy StartBar SideBar by either pinning the SideBar and dragging and dropping a file or a shortcut onto a group, or by using the SideBar's File Open dialog whether the SideBar is pinned or un-pinned.

A group or an item can be renamed, edited, or removed from the SideBar by right-clicking the SideBar and using the commands available from the popup menu. The order of the items in a group can be arranged by dragging and dropping them to the preferred position within a group or from one group to another.

Unlike the standard Windows Start Menu, a SideBar link item created from a shortcut that is subsequently moved or deleted will still work, as the Ezy StartBar link points to the actual file location not the shortcut.

Download (500kb)

New features and fixes include:

• System-wide Hot Key
• Default skin is now the user's Windows scheme - custom skin option removed
• URL link source not lost when source shortcut is deleted
• Launch bar now fully repositions and resizes with Taskbar position and size
• Launch bar now visible when Taskbar is set to auto-hide
• Menubar replaced by graphical toolbar

Office 2003 SP3 to provide Office 2007 Security Features

Microsoft plans to make some of the security improvements and features built into Office 2007 available for Office 2003, a company representative said Thursday. Service Pack 3 for Office 2003 will be focused on security. Joshua Edwards, a technical product manager for Office at Microsoft said: "We're not going to take everything, but we will take as much as we can."

Microsoft hasn't yet set a release date for the Office 2003 update, which like other service packs will be available as a free upgrade.

More from Source: news.com.com

Windows SharePoint Services 3.0 Application Templates

Windows SharePoint Services 3.0 Application Template Documents: All Application Template Datasheets This download combines all of the datasheets for the Application Templates for Windows SharePoint Services 3.0 into one convenient package.

AutoRuns for Windows v8.61

This utility from MSDN goes way beyond the MSConfig utility bundled with Windows. Autoruns is the most comprehensive startup monitor I have seen and shows which programs are configured to run during system bootup or login, and the entries in the order Windows processes them, including applications in your startup folder, Run, RunOnce, and other Registry keys. You can configure Autoruns to show other locations, including Explorer shell extensions, toolbars, browser helper objects, Winlogon notifications, auto-start services, and more.

Autorun's Hide Signed Microsoft Entries option lists third-party auto-starting images that have been added to your system, and supports the auto-starting images configured for other system accounts. Also included in the download package is a command-line equivalent, Autorunsc, that outputs in CSV format.

Download the Office 2007 Formats Compatibility Pack

Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007 File Formats
Open, edit, and save documents, workbooks, and presentations in the file formats new to Microsoft Office Word, Excel, and PowerPoint 2007.

Training presentation: Get up to speed with Office 2007

Download this Powerpoint template of training content about the new look of the Office 2007 and its new tools and benefits. This content is geared for a corporate trainer to present to a group.

Version: PowerPoint 2000 or later
Download size: 1350 KB (5 mins @ 56 Kbps)

Office 2003 and 2007:
MSDN Technical Articles, How-To Content, and Code Samples

MSDN have researched the MSDN Library – Office Solutions Development Node and pulled tons of useful links and categorized them by content type. MSDN will update these pages as more content is published.

These are all new resources pages that compile links to articles, code samples, and how-to articles for Office 2007:

• Code Samples 2007
• Technical Articles 2007
• Tools and Utilities 2007
• How-To Center Home Page:
  
  • How Do I ... in 2007 Office System Programs
    
  • How Do I ... in 2007 Office System Services
  • How Do I ... in 2007 Office System Servers
  • How Do I ... in Tools and Technologies

They have also built some resource pages for Office 2003:

• Code Samples 2003
• Technical Articles 2003
• Tools and Utilities 2003

Source: Erika Ehrli's MSDN Blog - Adventures in Office Development and .NET

Connecting to Oracle With MS Access

These are helpful walkthroughs on using Access to connect to Oracle back-ends. The first article also addresses the differences between Oracle SQL and Access SQL:

• The Database Journal - Connecting with Oracle: Accessing Oracle via Access and Excel
  
  
• Oracle FAQS - Connecting Oracle with MS-Access

Up-sizing from Access to SQL Server

A useful resource portal from MS Gold Certified Partner ssw.com.au on the rationale for up-sizing from Access to SQL Server and managing the up-size process. You can download an up-sizing plan template and these white papers:

• What’s New and Different when Moving Your Backend from Access to SQL Server 2000
• How to Migrate Your Backend from Access to SQL Server 2000

Fifty Ways to Improve Your Visual Basic Programs

An excellent article from VB author J.P. Hamilton on 50 things you can do to make your VB [and VBA] code more effective and easier to maintain.