Latest Access Knowledge Base Articles

Courtesy of KBAlertz.com:

Access 2007

• 941877 Description of the Access 2007 hotfix package: February 26, 2008
• 948950 Description of the 2007 Office hotfix package: February 26, 2008
• 949946 Description of the Access 2007 hotfix package: March 15, 2008
• 943249 Description of the Access 2007 hotfix package: January 28, 2008
• 950277 Description of the Access 2007 hotfix package: March 22, 2008
• 949585 How to obtain the latest service pack for the 2007 Office suite
• 950488 Description of the Access 2007 hotfix update: March 31, 2008
• 951182 Error message when you try to install the Access 2007 Runtime: "Access 2007 Runtime: Another version of this product is already installed"
• 951339 Error message when you try to open the Office Customization Tool: "Files necessary to run the Office Customization Tool were not found"
• 950812 Description of the Office Access 2007 hotfix package: April 16, 2008

Access 2003

• 951646 Description of the behavior changes that occur after you install post-Office 2003 Service Pack 3 updates
• 950753 Description of the Access 2003 hotfix package: April 16, 2008
• 952609 Description of the Access 2003 hotfix package: May 19, 2008

Access 2007 Keyboard Shortcuts

You can now download from my aadconsulting.com Access resources page a complete list of all the Access 2007 Keyboard Shortcuts compiled as a searchable CHM Help file.

Access 2007 Bug Alert: Compact and Repair Might Delete Your Database

The Access Team Blog has just posted this advice:

KB article 950812: Compact and repair might delete your database (Access 2007)

KB article 950812 provides more information about a scenario where some people have reported that a compact and repair deletes their database. You can’t download the fix directly but can get it by calling CSS [MS Customer Service?]. This fix will be included in SP2 when that ships (I don’t have an ETA). Here is the most relevant part of the article:

In Office Access 2007, when you try to compact an .accdb database, you may receive the following error message:

Microsoft Office Access can't delete database name.accdb after compacting it. The compacted database has been named Database.mdb.

If you compact a database using the same name, Microsoft Access creates a new compacted database and then deletes the original database. In this case, however, the original database wasn't deleted because it is read-only.

If you can, remove the read-only status, delete the original database, and then rename the new database using the original name.
If you can't remove the read-only status, inform your workgroup administrator.

Additionally, after you click OK, you receive the following error message:

Could not find file 'database name.accdb'

Then, the original .accdb database and the temp-compacted Databasex.mdb database are removed unexpectedly.

This potentially catasrophic scenario is something we need to be mindful of whenever attempting a Compact & Repair in Access 2007.

I suggest you the close database, and manually back it up, and then do the Compact & Repair on the closed file and not in the open database.

In a comment to the Access Team Blog post, Russell Sinclair said on May 29, 2008 3:35 PM:

Glad to see you've posted this to the blog. This one killed quite a bit of work for me. FYI, there is a work around I detailed at one point to a team member. When the error message comes up, DO NOT click OK. Instead go to the folder and create a copy of the “Database.mdb” file before Access has the chance to delete it. You can then safely click OK and Access will destroy the original Database.mdb database.

Update 2 June 2008: New Access 2007 Backup Before Compact Tool

I have responded to this issue with Backup Before Compact, a free Access 2007 Add-In that I have developed that when invoked automatically closes Access to unlock the database file, backups, and then compacts & repairs the database before re-opening it in Access 2007. A fail-safe feature renames the original file, which is retained as a temporary redundant backup until the tool is next used on the database.

You can download the Add-in from my Free Access Tools page.

Access Help and How To Links at Office On-Line

On-Line Help

• Access 2007 Help
• Access 2003 Help
• Access 2002 Help
• Access 2000 Help

Training

• Access 2007 Training
• Access 2003 Training
• Access 2007 Demos
• Access 2003 Demos
• Access 2007 Webcasts

Templates

• Templates for Access

Latest SQL Server KB Articles

An ongoing MS DTC transaction is orphaned in SQL Server 2005

Microsoft does not provide support for SQL Server TDS enhancers

On Demand Web Seminar: True High Availability for SQL Servers

This free interactive web seminar from WindowsIT Pro on high availability on SQL Servers covers:

• WAN HA support
• HA Support for Standard vs. Enterprise Edition
• Creating a HA cluster including majority node set (MNS)
• How to remotely install HA solutions on your servers from a central location
• Why you need replica servers, and how to automatically configure them
• How to test and validate your replica environment without operator intervention
• Using the Rewind function (Continuous data protection) included in CA XOsoft High Availability
• Disaster Recovery Planning

Register here

Tips on Controlling the Access Application Window

You can use some simple API calls from VBA to manage the Access Application window when you want finer control on how your Access application opens or is displayed:

1. Make the Access application window invisible, so your application looks like an executable. Note: All menubars/toolbars/ribbons will also be hidden.
   
   
2. Resize the Access application window to fit your start-up form and place it at specified screen co-ordinates.

I have prepared two sample MDB's (for Access 2000 or later) to display each of these techniques, and they are available for free download here.

Latest SQL Server Fixes

FIX: An ongoing MS DTC transaction is orphaned in SQL Server 2005

FIX: The transactional replication does not run the corresponding stored procedure at the subscriber when the stored procedure is run at the publisher in SQL Server 2005

Expanded List of Formats to be Supported in Office 2007 SP2

Microsoft has announced that the release of Microsoft Office 2007 Service Pack 2 (SP2) scheduled for the first half of 2009, will include support for XML Paper Specification (XPS), Portable Document Format (PDF) 1.5, PDF/A, and Open Document Format (ODF) v1.1.

When using SP2, Office users will be able to open, edit and save documents using ODF and save documents into the XPS and PDF fixed formats from directly within the application without having to install an add-in. Users will also be able to set ODF as the default file format for Office 2007. To also provide ODF support for users of Office XP and Office 2003, Microsoft says it will continue to collaborate with the open source community in the ongoing development of the Open XML-ODF translator project on SourceForge.net.

SQL Server Data Types

10+ Common Questions about SQL Server Data Types SQL Server offers a variety of data types, and choosing the right one can be confusing. Download this White Paper from Susan Harkins of TechRepublic.com where she answers common questions about using SQL Server data types and explains their optimum usage.

Access 2007 Security

These two articles from Microsoft will give you an overview on how security in Access 2007 differs from previous versions:

• Getting Started with Access Security
• Encryption vs. Encoding

How Custom Access 2007 Ribbons Work



The most challenging issue for Access users and developers when upgrading to Access 2007 is the radical shift from CommandBars to the RibbonBar paradigm.

In this outline I will introduce the operation of the Ribbon in Access 2007, provide some guidance on customisation, and point you to various tools and on-line resources to assist your efforts at Ribbon customization.

Getting Started:

1. Download and install my Access Ribbon Switchboard Database demo database.
   
   
2. Start Access 2007. Open the Access Ribbon Switchboard Database using the {SHIFT} key.
   
   
3. Click the Microsoft Office Button, click Access Options, and then click the Advanced tab.
   
   
4. In the General section, select the option Show add-in user interface errors, when debugging a RibbonBar.
   
   
5. Click OK to close the Access Options dialog box.
   
   
6. Right-click the Navigation pane. Point to Navigation Options, and then click Show System Objects. Click OK.
   
   Note: The Access system tables appear in the Navigation
   pane. In the Demo Database the system table, USysRibbons,
   each record contains the XML for each custom Ribbon in the
   database.
   
   
7. Click the Microsoft Office Button, and then click Access Options.
   
   
8. Click the Current Database tab, in the Navigation Pane and Ribbon and Toolbar Options sections, you will see that the following option settings hide the Database Window, the Navigation Pane, and the Full Menus, and set the custom ribbon, Main, as the StartUp Ribbon Bar:
   
   
   
   
   
9. Click OK to close the Access Options dialog.
   
   
10. Close the Demo Database.
    

11. Re-open the Demo Database without using the {SHIFT} key.
    

12. The Ribbon UI displays the Main Ribbon Custom Tab:
    
    Note: You use a Ribbon from the USysRibbons table to
    supply the UI for a specific form or report by setting the RibbonName
    property to the name of the Ribbon record in USysRibbons table.
    
    
    
    

Creating the XML Ribbon Definitions:

The ribbon definition is a simple XML file and can be easily created in any text editor, but if you use the free Microsoft Visual Web Developer 2005 Express Edition (VWD), you can get IntelliSense for your code. You can point it at the custom ribbon schema at http://schemas.microsoft.com/office/2006/01/customui and VWD should pick up the correct file, which is called customUI.xsd. You can download the file from http://officeblogs.net/UI/customUI.xsd. This enables VWD to provide type-ahead IntelliSense for correct ribbon xml:

The contents of the XML file are:

1. customUI - this is the top level element for a custom ribbon.
   
   
2. ribbon - defines the ribbon itself. Set the startFromScratch attribute = "true" to create a new blank ribbon. If this attribute is omitted or left blank, the custom ribbon will merge with the existing Access ribbons.
   
   
3. tab - creates a new tab in the ribbon
   
   
4. group - creates a group. These are used to logically group controls in the ribbon.
   
   
5. id - unique name of a control in the ribbon label - static text displayed with a control
   
   
6. button - similar to a command button. Use the onAction attribute to specify the name of a macro in the Ribbon macro in the Demo database which will be called when the button is clicked.
   
   
7. dropDown - creates a dropdown list that cannot be updated. To create a drop down that the user can type in, use a comboBox control instead.
   
   
8. imageMSO - specifies the name of a built-in image control in Office 2007 that can be used as in icon in your ribbon. You can find the Image name or ControlID by hovering your mouse over a control image in the Customize the Quick Access Toolbar dialog, and reading the screentip for the control image. For example, the ControlID for the Form icon image is CreateForm:
   
   
   
   

Reviewing the Ribbon XML:

On aadconsulting.com I provide for free download two tools that will help you to debug the XML for your custom ribbon bars:

• Office 2007 Ribbon XML File Reviewer
• Access 2007 USysRibbons Reviewer Add-In
  

These freeware tools providing advanced Office 2007 Ribbon XML file review features. Open any Office 2007 Ribbon XML file in the XML File Reviewer to review an Office Ribbon's full menu hierarchy in a tree-view with all control attributes detailed in an adjacent panel. The Access 2007 USysRibbons Add-In reviews and previews the custom Ribbons in the USysRibbons table of an Access 2007 database. Extensive reference resources are incorporated into the GUI's of both products. Both tools require Access 2007.

Update 9 June 2008: Check out the Office 2007 RibbonCreator WYSYWIG tool from http://www.ribboncreator.de/en/

More Information:

• Access 2007 Ribbon Attributes
• Access 2007 Ribbon Class
  
• Customizing the New Access UI
• Extend The 2007 Office System With Your Own Ribbon Tabs And Controls
• Office RibbonX API- Extend The 2007 Office System With Your Own Ribbon Tabs And Controls
• Ribbon Extensibility in Access 2007
• Office Custom UI Editor
• Customize the Office (2007) Ribbon UI for Developers (Part 1)
• Customize the Office (2007) Ribbon UI for Developers (Part 2)
• Customize the Office (2007) Ribbon UI for Developers (Part 3)
• Access Freak Access 2007 Tutorials
• AccessRibbon.com
• Ribbon Customizer

Free MS Access Databases and Templates

For those who are not aware of the many free databases available for download from aadconsulting.com this post features all current downloads with direct links.

Recent Additions:

Access 2003 versions of the Advanced Issues Tracker database, and a new Case (Project) Manager Access 2003 database.

There is also a new Donations database for Access 2000 or later.

All these databases have modern GUI's and enhanced functionality. All except the Donations MDB use only embedded macros and are aimed at introducing new users to the code-free capabilities of Access. All code is exposed.

Other Open Code Databases:

These databases can be run in Access 97/2000/2002/2003/2007.

Service Call Management Developed using the Database Wizard -tracks workorders, parts, and labour inputs. Invoice generater etc. Additional code adds inventory tracking. Remove sample data before use.

Managers For divisional managers. Review managers and staff by division. Remove sample data before use.

Resumes For personnel managers. Review staff and resumes by region. Remove sample data before use.

Client & Contact Manager Based on the Access97 Database Wizard -tracks all clients and contacts, import data from Outlook, mail-merge and more. Additional code adds client data and admin functions. Remove sample data before use. Access2000 version

Subscriptions Manager Tracks subscribers by name and organisation, label and envelope wizards, mail-merge and more. Remove sample data before use. Access2000 version

FREE Fully Featured Applications:

The source code .mdb for the following databases can be purchased for only US$40 each. Secure payment with PayPal. Files will be e-mailed. Order

These databases can be run in Access 97/2000/2002/2003/2007. Download the Access2000 versions to run in Access 2000/2002/2003/2007.

SMS Call Logging DB Application NEW Updated GUI for Access 2000 version (Download includes both Access97 and Access2000 versions) SMS - Service Management System - is a fully integrated and comprehensive Service/Maintenance Contract Call and Revenue Management Database Application.

FreeTLT HRIS DB Application NEW Updated GUI for Access 2000 version (Download includes both Access97 and Access2000 versions) Free TLT is a comprehensive HRIS application that tracks employee leave and training, with training profiles and extensive reporting. Review leave and training in the daily, weekly, and monthly look-aheads. Use the exclusive TLT SmartToolbar to filter and find records in all forms. Full Details

Mailing List Manager NEW Updated GUI for Access 2000 version Features a new Access navigation paradigm. Manage large mailing lists, print labels, envelopes, export data to delimited files for mail merges, check data integrity, and advanced filtering. Access2000 version

Document Tracker NEW Updated GUI for Access 2000 version With an optional automated Word front-end. Document management system. Automatic document referencing and creation. Version 2.3 adds the DokTrack.dot Word template and adds a Title field to the table tblAuthors. The Title for the selected Author is automatically picked up in a new Word document based on the template. Users can now use use Word to create and manage documents without opening Access. Access2000 version

Meeting Tracker NEW Updated GUI for Access 2000 version Create Tasks, allocate responsibility to a Task Owner, assign people to Task Teams, record meetings and attendance. An instant Meeting Summary Report can be printed, emailed or exported to Word or Excel. Access2000 version

Program Manager NEW Updated GUI for Access 2000 and Access 2002 versions A complete business solution. Manages Clients, Contacts, and Client Programs. Allows multiple contacts for each client, and individual address and contact details for each contact. Nominate separate client program and invoicing contacts. Full reporting and database maintenance. 22May01- new version allows update of Countries field in address table. Access2000 version or Access2002 version

Project Manager NEW Updated GUI for Access 2000 version Fully featured business application for project tracking and control in a business unit. Access2000 version

Sales Manager NEW Updated GUI for Access 2000 version The Sales Manager is a fully-featured sales management application with comprehensive reporting and product-costing functionality. Access2000 version

Contractor Tracker NEW Updated GUI for Access 2000 version The ultimate contractor tracking and control application. Remove sample data before using. Access2000 version

Webcast: Essential SQL Server 2008 for Developers
Sponsored by Microsoft May 12 - 15, 9:00 a.m. PDT

SQL Server 2008 offers an impressive array of capabilities for professional developers that build upon key innovations introduced in SQL Server 2005. The Essential SQL Server 2008 for Developers Webcast Series will help you understand how to build enterprise-class applications which deeply exploit the rich data types, programming models and new development paradigms in SQL Server 2008:

	Part: 1 TSQL Improvements And Data Types	5/12/2008 09:00 AM PDT
	Part: 2 Beyond Relational	5/12/2008 11:00 AM PDT
	Part: 3 Service Broker And Messaging	5/13/2008 09:00 AM PDT
	Part: 4 .NET Code In SQL Server	5/13/2008 11:00 AM PDT
	Part: 5 Client Programmability	5/14/2008 09:00 AM PDT
	Part: 6 ADO.NET Entity Framework	5/14/2008 11:00 AM PDT
	Part: 7 HTTP Programming And SQL Server	5/15/2008 09:00 AM PDT
	Part: 8 Occasionally Connected Systems	5/15/2008 11:00 AM PDT

NEW Dashboard Demo
for Microsoft© Access 2000/2002/2003/2007

Screenshot from Access 2003 in Windows XP: click on the image to zoom in

I have just uploaded my MS Access Dashboard Switchboard Sample Database, which uses only native Access controls, and is a fully functional demo database featuring an integrated dashboard switchboard with easy single screen navigation and advanced usability and filtering:

• Filter by user-selected fields
• Fully-integrated cascade update record editing
• Full find first and find next functionality

Download the DEMO: Access Dashboard Demo

You can use the demo as an Issues Management Application or buy the source code for only U$39.95 to use the GUI in your own Access applications.

UI Builder for Access

Just came across this product, UI Builder for Access from OpenGate Software, and from the on-line demo was very impressed with the tool's wide functionality and the clean gui produced by the Builder. No VBA knowledge required but pricey. Download the sample databases from here.

Latest SQL Server Fixes

FIX: The SQL Server service may stop responding when you run a query that contains many OR clauses in SQL Server 2005

FIX: The update operation is performed on incorrect rows when you update a column of the VARCHAR data type on a remote table by using a linked server in SQL Server 2005

FIX: The SQLGetData function may return zero for the actual size of the data that is to be copied when you use the function to retrieve data for a binary column in SQL Server 2005

FIX: The application seems to stop responding when an application executes a large query that has parameters in predicates in SQL Server 2005 Compact Edition

Windows XP SP3 Available for Download

Windows XP SP3 is available for download from Windows Update.

Free BETA of Executive Assistant for Microsoft© Access v3.0

Download the free BETA of the new v3.0 of my Access-based CRM System with Integrated Executive Diary Management:

• Easily manage Clients, Contacts, Client Visits, and Internal Meetings
• Quickly create detailed Visit and Meeting Reports
• Built-in Visit scheduling
• Automated email distribution
• Comprehensive reporting
• Contact Interests tracking
• Event and Marketing Campaign management
• Modern Windows Vista-themed GUI
• Visits Alerts and overdue visit monitoring
• Client address information
• More comprehensive reporting options

Used by senior executives at Munich Re Australia, Samsung Fire & Marine, and UBS.

New in Version 3.0:

• Ad-hoc client calls management
• Advertising tracking for client campaigns

Beginner Error-Handling

TechRepublic.com blogger, Susan Harkins, has posted a great article on how novice Access developers can start adding advanced error-handling to their forms.

Live Mesh: The Collaborative Cloud

Live Mesh has left the building. Is this future of the Desktop? Have a look.

At this point it is a shared space in a web browser, with limited collaboration services. Microsoft says it will evolve into browser development platform, where existing (Office?) applications can occupy a browser window.

Generate Synchronization Scripts in SQL Server with TableDiff

TableDiff is a console executable packaged with SQL Server that allows you to compare data between SQL Server tables. Tim Chapman of TechRepublic.com shows how to use TableDiff.

Latest SQL Server and Data Access KB Articles

FIX: SQL Server 2005 is not installed on a Windows Web Server 2008-based system

An SPN for the SQL Server Browser service is required when you establish a connection to a named instance of SQL Server 2005 Analysis Services or of SQL Server 2005

FIX: No results are returned when you run a stored procedure in Server Explorer in Business Intelligence Development Studio if the stored procedure returns a column of the unique identifier data type in SQL Server 2005

FIX: Error message when you try to delete the rows from the table in SQL Server 2005 Compact Edition or in SQL Server Compact 3.5: "Major Error 0x80004005, Minor Error 0. Attempted to divide by zero"

Release Schedule for Windows XP SP3

Neowin.net says it has procured the release schedule for Windows XP SP3:

• April 14, 2008: Support is available for the release version of Service Pack 3 for Windows XP
  
  
• April 21, 2008: Original Equipment Manufacturers, Volume License, Connect, and MSDN and TechNet subscribers
  
  
• April 29, 2008: Microsoft Update, Windows Update, Download Center
  
  
• June 10, 2008: Automatic Updates

Recent Access 2007 Fixes

From the Access Team Blog today:

• Avoiding security prompts for Access 2007 runtime applications
  
  
• Hotfix to correct “database is in an unrecognized format” error with Access 2007 SP1 compiled databases

Latest SQL Server 2005 Fixes

FIX: Error message when you run queries on a database that has the SNAPSHOT isolation level enabled in SQL Server 2005: "Unable to deallocate a kept page"

Error message when you execute a CLR routine or use an assembly in SQL Server 2005: "Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050)"

FIX: SQL Server 2005 requests columns that you do not expect when you run a SELECT statement

SQL Server 2008: The Deal

The April 2008 issue of TechNet Magazine places the spotlight on SQL Server 2008:

• What's New
• Security
• New Data Types
• Data Warehouse Query Performance
• Minimize Blocking in SQL Server

Integrating Access Databases with SharePoint

A very timely article from the April 2008 issue of the MS TechNet Magazine, which focuses on:

• The benefits of integrating Access and SharePoint
• Migrating Access data to SharePoint
• Extending Access functionality with SharePoint

Access 2007 Runtime SP1 Now Available

Access 2007 Runtime SP1 is now available for download.

The Runtime SP1 contains files versions later than the files updated by Office 2007 Service Pack 1 (SP1). Get additional information from the Access Team Blog.

Implement Custom Paging with SQL Server 2005

The default behavior of .NET data components involves retrieving the entire results of a query when loading data for display on a page. A better approach involves loading only data that will display on the page. In this article Tony Patton of TechRepublic.com shows you how to use ListView and GridView controls to implement custom paging with a SQL Server 2005 backend.

Microsoft Business Desktop Deployment Kits

Business Desktop Deployment 2007 provides planning, building, and technical guidance, templates, and scripts to facilitate deployment of Windows Vista® and the 2007 Microsoft® Office system. This release includes updates to support Windows Vista SP1 in addition to previously supported platforms.

Microsoft Deployment Toolkit 2008 is the next version of Business Desktop Deployment (BDD) 2007. It is the recommended process and toolset to automate desktop and server deployment. This release includes updates to support Windows Vista SP1 and Windows Server 2008 in addition to previously supported platforms.

SQL Server Resources and Links

Official SQL Server Resources:

• SQL Server 2005 Elearning Portal Nine FREE Microsoft E-Learning courses for Database Administrators, Database Developers, and Business Intelligence Developers.
• Microsoft SQL Server
• MSDN SQL Server Developer Center
• Microsoft SQL Server TechCenter
• SQL Server Support Center

SQL Server Community Resources:

• Professional Association for SQL Server (PASS)
• SQL Junkies

SQL Server 2005 Webcast Live and on Demand:

• Webcasts and Chats for SQL Server
  
• TechNet Support WebCast: Overview and deployment of Microsoft SQL Server 2005

Latest SQL Server Fixes

FIX: The %TEMP% folder is not cleaned up after you use database mail to send an e-mail message that has an attachment in SQL Server 2005

FIX: Error message when you use a symmetric key to decrypt data in a SQL Server 2005 database: "The decryption key is incorrect"

Access and the Web: Integrating with Office Live Small Business

A fascinating article has been posted on the Access Team blog, on a SIMPLE and seamless way to bring Access 2007 databases to the web, using the free Office Live Services portal: Pushing Access Data to the Web: Integrating with Office Live Small Business

Latest Access Knowledge Base Articles

Courtesy of KBAlertz.com:

Access 2000

• 913178 Description of the Jet 4.0 Database Engine post-837001 hotfix package: February 17, 2006
• 943509 Description of the Jet 4.0 Database Engine hotfix package for Windows XP SP2, for Windows Server 2003 SP1, for Windows Server 2003 SP2, and for Windows Vista: October 23, 2007

Access 2003

• 938792 Issues that are fixed in Office 2003 by Office 2003 Service Pack 3
• 923618 Description of Office 2003 Service Pack 3
• 943967 After you install Office 2003 SP3, you encounter various problems in an Access project when you work with a Bit data type column in a linked SQL Server table.
• 943509 Description of the Jet 4.0 Database Engine hotfix package for Windows XP SP2, for Windows Server 2003 SP1, for Windows Server 2003 SP2, and for Windows Vista: October 23, 2007.
• 945674 Description of the Access 2003 post-Service Pack 3 hotfix package: December 18, 2007.

Access 2007

• 946621 Security-related Group Policy settings in the 2007 Office system do not work as expected.
• 938888 Description of the 2007 Office hotfix package: June 17, 2007.
• 924739 Error message when you try to add an existing Windows SharePoint Services list to an Access 2007 database: "You do not have the necessary permissions to use the '. Describes a problem that occurs when you try to add an existing SharePoint list that contains a column name that has more than 64 characters to an Access 2007 database. Provides a workaround.
• 937999 Error message when you run a macro that calls a VBA function in an Access 2007 database: "Action Failed Error Number: 2950". Discusses a problem that occurs after you use the ADE 2007 Package Solution wizard to package an Access 2007 database application. After you install the packaged application, a potential security concern message appears when you open the application.
• 935370 Issues that you may encounter when you open or are using a Microsoft Office Access database on a computer that is running Windows Vista.
• 895456 How to change the product key for Office 2003 and for Office XP.
• 932994 You receive an error message when you run a query that includes a field name that begins with a double-byte Arabic number in Access 2007 or Access 2003.
• 937998 The Access 2007 Developer Extensions command is missing from the Microsoft Office Button in Access 2007 .
• 936748 Issues that are fixed in Access 2003 by Office 2003 Service Pack 1. Discusses a problem in which you receive error messages when you use wizards and Visual Basic for Applications commands in Access 2003 on a Windows-Vista based computer when the computer has both Access 2003 and Access 2007 installed.
• 938809 Error message when you try to install an Access 2003 add-in for all users by using the Add-In Manager on a Windows Vista-based computer: "There was an error adding to the registry" .
• 924460 How to manage templates in 2007 Office programs. Describes the different template categories and the locations of templates in 2007 Office programs. Also describes the registry settings that control where to find your custom templates.

Download SQL Server Compact

SQL Server Compact 3.5 Books Online and Samples SQL Server Compact 3.5 is a small footprint in-process database engine that allows developers to build robust applications for Windows Desktops and Mobile Devices.

Try SQL Server 2008

Try SQL Server 2008 Free for 180 Days Download the latest SQL Server 2008 Community Technology Preview and try out the latest features.

SQL Server 2008 Feature Pack Community Technology Preview - February 2008 Download the February 2008 Feature Pack Community Technology Preview - a collection of stand-alone install packages that provide additional value for SQL Server 2008.

SQL Data Services: Cloud Obscures Visibility

Microsoft has launched this new BETA product, which I guess you could liken to SQL Server for the Web.

This is how it is described in a post on the Access Team Blog (and I thought it was an Access blog):

Microsoft is now getting into the lightweight database game with SQL Server Data Services. It looks to follow a similar pattern to simpleDB and CouchDB with named, attribute pairs in a flat storage space and accessibility through REST. Some of the interesting things they are bringing to the game:

• Query data using LINQ
• Property support for simple data types (string, numeric, date, boolean)
• Paging management for data retrieval
• Rich client library in C# and VB with LINQ support
• SSL Security

It looks like a great cloud based simple storage system with some of the goodness MSSQL bring to the table. They are now signing up BETA testers and we will be putting some demos together to show how Access can work with these lightweight database systems.

Translation please...

Critical MS Office Updates On March 11

In a security bulletin yesterday Microsoft announced it will issue four critical patches for Office on March 11 for:

• Excel 2000
  
  
• Outlook 2000, 2002, 2003 and 2007
  
  
• Remote code execution vulnerabilities in Microsoft Office 2000 (SP3)
  
  
• Web components for Office 2000

NEW V4.0 of Ezy Exporter Premium Edition
Add-In for Microsoft© Access

The functionality Microsoft forgot! My Ezy Exporter Premium Edition Access Add-In is the easy way to export unlimited Access objects and data in bulk.

In v4.0 you can now also export relationships, commandbars, import/export specs, and library references.

A best-seller at the Office Marketplace.

NEW NESTED TreeView MS Access Switchboard
for Microsoft© 2000/2002/2003/2007

Fully Nested with Unlimited Levels and Tree Menu Builder Add-In

Screenshot from Access 2000 in Windows XP: click on image to zoom

I have just uploaded my latest Access Switchboard: the NESTED TreeView Demo Database feature cutting-edge application navigation and the unique Nested TreeView Switchboard Builder, which lets you easily build complex windows-explorer like tree menus.

When you purchase the source code MDB, you get as a free bonus the Treeview Switchboard Builder Access Add-In, which allows you to automatically populate the TreeView Switchboard in your Access database with multi-level hierarchical switchboard menus and menu items.

Download the DEMO:

Convert your Access 2007 Application from Design to Runtime Mode by Changing its Extension

A useful tip from TechReplublic.com blogger, Mary Ann Richardson.

SQL Server Guides from SQL Server Magazine

Download these free pocket guides from SQL Server Magazine:

• SQL Server 2008 Memory and CPU Highlights
• The Top 10 things You Need to Know about Data Protection for SQL Server 2008
• 13 Things You Need to Know About SQL Server 2005

DreamSpark: Free MS Software for Students

Microsoft is offering free application development software to students world-wide. Go to Microsoft DreamSpark to get these applications free:

Access Data Projects Switchboards Triple-Pack


Click on image to zoom in...

Many Access developers have asked for ADP versions of my very popular Access Switchboards.

The wait is over.

I have just uploaded the Demos package for the Access ADP Switchboards with Access 2007 Black Theme Triple Pack. Source code is only US$49.95.

The three demo projects feature cutting edge application navigation:

• Native SideBar Switchboard Manager - uses only native Access controls
  
• SideBar Switchboard - flexible freeware 3rd-party OCX royalty-free
  
• TreeView Switchboard - uses the royalty-free mscomctl.ocx tree-view control
  

A SQL Server Connection is not required to run these demos: dummy forms and reports are used to demonstrate the operation of the Switchboards, which are populated by loading metadata from CSV text files at run-time.

When you purchase the source code ADPs, you also get the ADP SWB CSV Files Builder MDB Application, which is used to build the Switchboard Items table and automatically create the CSV files from that table’s records. The CSV files are then deployed with the ADP.


Click on image to zoom in...

Download the ADP Switchboard Demo.

Latest SQL Server 2005 FIXES

FIX: A stored procedure cannot finish its execution in SQL Server 2005.

FIX: Error message when you run a stored procedure that references a linked server that is linked to the local server itself in SQL Server 2005: "Transaction context in use by another session".

FIX: In SQL Server 2005 the "SQLMoreResults" function returns incorrect results when you use it to retrieve a result set.

NEW NATIVE SideBar MS Access Switchboard Demo
for Microsoft© 2000/2002/2003/2007


Screenshot from Access 2000 in Windows XP: click on the image to zoom in

I have just uploaded a new Access 2007 Black Themed Native SideBar Switchboard Demo, which features Outlook-style application navigation using only native Access controls.

There are now three themes:

• Windows XP
• Access2007 Vista Silver
• NEW Access 2007 Black

included in the package which is still only US$39.95.

Features include:

• Seamlessly use the native Switchboard Items table created by the Access Switchboard Manager
• Outlook-style navigation
• Custom Icons for each Switchboard Bar and Switchboard Item
• THREE Switchboards for the price of one!

You can use the demos as a Contacts Manager or buy the source code to use the GUI in your own Access applications. Native SideBar Switchboard Demos

NEW NOIR SideBar MS Access Switchboard and GUI Demo
for Microsoft© 2000/2002/2003/2007

Screenshot from Access 2000 in Windows XP: click on the image to zoom in

Get the cool Access 2007 Black Theme in old versions of Access!

I have just uploaded this new Access Demo Database featuring cutting edge application navigation. Maximize screen space by using these dynamic features:

• Uses the native Switchboard Items table created by the Access Switchboard Manager
• Outlook-style navigation
• Custom button graphics
• Users can hide the Switchboard to maximize form sizes
• Users can adjust the width of the Switchboard on the fly
• Access Menubar and Toolbars are optional
• Ribbon-style toolbar resides in a subform

You can use the demo as a Contacts Manager or buy the source code to use the GUI in your own Access applications. Ditch the tired and boring native MS Access Switchboard for only US$39.95!

NOIR SideBar Switchboard Demo (NOIRSideBarSWB_DEMO.zip- 500kb)

Latest SQL Server 2005 Fixes

FIX: Error message when you preview a SQL Server 2005 Reporting Services report that uses the Microsoft .NET Data Provider 1.0 for SAP NetWeaver Business Intelligence: "An error occurred during local report processing"

FIX: Error message when you run a maintenance plan that contains a Back Up Database task in SQL Server 2005: "Failed:(-1073548784) Executing the query 'BACKUP DATABASE"

What's New in SQL Server 2008

SQL Server 2008 Video Cast Series – Optimize Your SQL Server 2008 Randy Dyess, SQL Server MVP, discusses new features of SQL Server 2008 that help database administrators to optimize and tune their database environment, including a demo of Performance Studio, one of the new built-in automated features of SQL Server 2008 that allows performance auditing.

The video also covers other new features like:

• Dynamic Management Views
• Dynamic Management Functions
• Resource Governor

Using Access Menu Add-Ins Without The Windows Registry

Access menu add-ins require a registry entry to be available from the Access Add-Ins Menubar menu item.

In many corporate environments Access developers and users are unable to use 3rd-party Access add-ins, as users are not given permission to add entries to the Windows registry.

There is a way round this limitation, by referencing the add-in file in the database you want the add-in to be available in.

To illustrate this technique, I will use my free add-in Access SideBar Add-In for Access 2000/2002/2003, which provides an easy way to navigate objects in your Access MDB.

These are the steps to get the tool working as a library database in Access 97 or later, without the need for any changes to the Windows registry:

1. Copy the file sidebr2k.mda to a folder on your PC.
   
   
2. Open your MDB in Access and hit F11 to open the VBA Editor (VBE) window.
   
   
3. In the VBE, from the Menubar select Tools|References...
   
   
4. In the References dialog, click the Browse... button, and in the Add Reference dialog, select Add-ins(*.mda) in the Files of type drop-list, locate and select the file sidebr2k.mda, and click Open.
   
   
5. The MDA file will now appear as a reference in the References dialog:
   
   
   
   
6. Note the name of the library file in the Available references listbox, as you need the library name to call functions from the library. In my example the library name is: The Access SideBar.
   
   
7. Create a new VBA module and insert this public function:
   
   Option Compare Database
   Option Explicit
   
   Public Function pfnRunSIDEBAR()
   Call [The Access SideBar].aad_OpenLibraryDB
   End Function
   
   This code calls the function in the library database that invokes the add-in from the Access Add-Ins menu item: the syntax is [LibraryName].[FunctionName].
   
   To find out the name of the invoking function, open the add-in file in Access using the SHIFT key. From the Access Menubar select Tools|Options..., and in the Show section of the View tab of the Options dialog, ensure that the check-boxes for System objects and Hidden objects are both checked. In the Access Database Window open the USysRegInfo table. The invoking function will be the text after the = sign in the Value column for the record where the value "Expression" appears in the ValName column.
   
   
8. Save the module and call the function pfnRunSIDEBAR from anywhere in your MDB. In my work I create a custom toolbar for my development coding functions, and add a custom button to this toolbar to call a function.
   
   
   

New Free Access Database Templates

Over the past month I have made available a number of new free open-code Access databases:

1. Two of my new Access 2007 database templates , Advanced Issues Tracker and Meeting Tracker, are featured on the Official US Office 2007 Templates Site. Both these templates have 4-star user-ratings and are holding their own against the official Microsoft templates.
   
   
2. From my web-site, aadconsulting.com, you can also download:

• By popular demand an Access 2003 version of the Advanced Issues Tracker database,
  
• A new Case (Project) Manager Access 2003 database, and
• A new Donations database for Access 2000 or later.

All these databases have modern GUI's and enhanced functionality. All except the Donations MDB use only embedded macros and are aimed at introducing new users to the code-free capabilities of Access.

VB6 Security Update

Microsoft has today released Visual Basic 6.0 Service Pack 6 oleaut32.DLL Security Update to fix a security issue that could allow an attacker to compromise a computer running Microsoft Visual Basic 6.0 Service Pack 6.

Finding Dependencies in SQL Server 2005

Tim Chapman from TechRepublic.com shows you how to write a procedure that will look up all of the objects that are dependent upon other objects in a SQL Server database.

Latest SQL Server 2005 Fixes

FIX: Error message when you run a complex query after you install Cumulative Update 3 or Cumulative Update 4 for SQL Server 2005 Service Pack 2: 'The query processor ran out of internal resources and could not produce a query plan'

FIX: you receive incorrect data from SQL Server 2000 Analysis Services after you use the Page Heap tool (Pageheap.exe) to troubleshoot heap corruption issues

Scaling Access Forms

An open source VBA module for scaling Access forms for different display resolutions has been posted at SourceForge.

It is worth a look. I quickly tested the code in Access 2003, and for vanilla Access forms, it seems to work OK, subject to some tweaking(?):

• Comment out the first line in the module
  Attribute VB_Name = "modScaleForm"
  as it does not compile and appears redundant
  
  
• Change this line of code
  Dim fw As New clFormWindow
  to
  Dim fw As New Form
  as there is no such class module and using the Form object seems to work
  
  
• Font-scaling needs attention

In Access 2007, you can use the new native Anchoring property for form controls to achieve scaling, but don't use these techniques on complex forms with subforms or tab controls, as the result is less than satisfactory...

Useful Access 2007 Demos

Up to speed with Access 2007

Use control layouts on forms and reports

Collect data in Access 2007 by using e-mail

Bring your business data together in an Access database

Windows Vista and the 2007 Office system — better together

Find what you need faster with the redesigned Office Online

Meet the Navigation Pane

Office 2007 Ribbon XML File Reviewer Freeware

Click on the image to zoom in...

My new Access freeware tool provides advanced Office 2007 Ribbon XML file review features. Open your Ribbon XML file in the Reviewer to review the Ribbon's full menu hierarchy in a tree-view with all control attributes detailed in an adjacent panel. Extensive reference resources are also incorporated into the GUI.

Download (Requires Access 2007)

You can also try the free BETA 2 of Access 2007 RibbonX Reviewer and Builder Add-In

The Future of VBA?

A reading through this post, VBA in Office 14, on the Access Team Blog, seems to confirm that Office 14 will mark the beginning of the end for VBA.

This should be no surprise. Bill Gates in the months preceding the release of Office 2003, flagged that VBA would be demoted if not dumped in the version of Office after Office 12.

I have added my 2 cents to the debate in a comment to the post.

New Access Downloads Portal

Check out the new MS Silverlight Downloads Center. This is a link to the Access portal on the site.

How do I… Transfer data from a Word form to an Access database?

This is an excellent article from TechRepublic.com blogger, Mark Kaelin, on how to use a Word form to collect data for an Access database, even when the user's PC does not have Access installed.

Support WebCast:
Introduction to the Access 2007 Outlook Add-in for Data Collection and Publishing

Friday, February 15, 2008 10:00 AM Pacific Time

This Support WebCast from Microsoft will help you understand the features and capabilities provided by the Microsoft Access Outlook Add-in for Data Collection and Publishing. Register

Collect Data for an Access 2007 Database Using Outlook Emails

TechRepublic.com Office blogger, Mary Ann Richardson, has posted a very useful article on how to use the new Access 2007 Collect Data Through Outlook E-mail feature to automatically log data submitted by email direclty into an Access 2007 table.

SQL Server 2005 Best Practices Analyzer (January 2008)

This download is the January 2008 release of SQL Server 2005 Best Practices Analyzer (BPA), which gathers data from Microsoft Windows and SQL Server configuration settings.

BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in your SQL Server database environment.

Access Team Blog: For the Latest on Access 2007

The Access Team Blog from Microsoft has over recent weeks been receiving
some serious attention from the Access team at Redmond. I highly recommend that all
Access 20007 developers subscribe to the feed for tips and sample code from the guys who build Access.

A sample of recent posts:

• VBA in Office 14
  17 January 08 12:27 PM


• Four new video demos about reports
  16 January 08 10:40 PM


• Rich formatted text that includes data from fields
  16 January 08 12:45 PM


• Easy Drill Through with Macros
  14 January 08 11:18 PM


• Interactive chart/form filtering code sample
  13 January 08 10:38 PM


• Tips and tricks designing forms and reports
  11 January 08 01:02 AM


• My favorite keyboard commands
  10 January 08 10:28 AM


• New Vehicle Maintenance and Expense Reports database available
  09 January 08 09:15 PM


• Using Hyperlinks in Access: Display As Hyperlink and Is Hyperlink
  09 January 08 08:47 PM


• New Ribbon Reference: RibbonX for Dummies
  06 January 08 02:12 AM