New Hot Fix for Access 2007

The fix is available at http://support.microsoft.com/kb/960307.

Issues fixed include:

• You experience slower performance than you did in previous versions when you share a database file over a network.
  
  
• You may notice your database file increase in increments of 4 KB without any user interaction when the database is open.
  
  
• You may see a sub-form become blank after you set the main form’s recordset. You may also receive the following error: “Run-time error 2467 – The expression you entered does not exist”.
  
  
• When you make a change in a code module in an Access 2007 database under source code control, the changes are not saved if you do the following:
  
  
• Close the Visual Basic Editor without saving the changes
• Check the Object back in
• Use the Get Last Version feature to view the latest changes.

Latest SQL Server Updates (December 2008)

• Microsoft SQL Server 2005 Service Pack 3
• Feature Pack for Microsoft SQL Server 2005 - December 2008
  A collection of standalone install packages that provide additional value for SQL Server 2005.
• SQL Server 2005 Books Online
  
• Microsoft SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007
  This package includes two add-ins for Microsoft Office Excel 2007 (Table Analysis Tools and Data Mining Client) and one add-in for Microsoft Office Visio 2007 (Data Mining Templates).
• Microsoft SQL Server 2005 Express Edition Service Pack 3
• Microsoft SQL Server 2005 Express Edition with Advanced Services Service Pack 3
• Microsoft SQL Server Management Studio Express Service Pack 3
  Microsoft SQL Server Management Studio Express (SSMSE) is a free, easy-to-use graphical management tool for managing SQL Server 2005 Express Edition and SQL Server 2005 Express Edition with Advanced Services.

Image Formats

The Aivosto VB Shop has posted a very useful guide to image formats from a developer's perspective. Aivosto produces a number of handy coding tools for VB and VBA.

Download Updated Office 2007 Help Files

Microsoft has released updated Help files for Office 2007:

• Access 2007
  
• Word 2007
• PowerPoint 2007
• Excel 2007
• Publisher 2007
• Outlook 2007

Access in the Cloud: Beta Testers Required

The Microsoft Sync Framework and SQL Services team are working on a new framework that allows Access developers to store their application and data in a Web 2.0 cloud using SQL Server Data Services, and they are looking for beta testers.

The initiative was announced yesterday at PDC and is code-named Huron, a set of synchronization processes built atop of the sync functionality in SQL Data Services’ cloud database and the Microsoft Sync Framework to provide business database sharing, and offers a a "simple" way to scale data to large numbers of users while not parting from the investment made in current technologies. It also enables users to subscribe and then edit the data locally in the format of their choice whether that is Office Access, SQL Server Express or SQL Server Compact and then push changes into SQL Data Services.

More details are available here on the Access Team Blog.

MS Office Inside Your WebBrowser



Microsoft today announced at the Professional Developers Conference in Los Angeles that it plans to deliver Office Web applications – lightweight versions of Office – through the web browser.

Chris Capossela, Senior Vice President, Microsoft Business Division said : "As part of the next release of Office, we’re announcing that Microsoft will deliver Office Web applications - lightweight versions of Word, Excel, PowerPoint and OneNote - through a browser. With these new applications, people can use a browser to create, edit, and collaborate on Office documents. What’s great is that this provides a consistent Office experience when and where our customers want it, regardless of whether they are accessing their Office documents through the PC, phone, or browser.".

Full Story

Office 2007 SP2 Slated for Early 2009

An MS TechNet blog has announced that Service Pack 2 for Office 2o07 will be released on a date between February and April 2009.

Some details of changes in Office desktop applications in SP2 have been included in the announcement:

• Improved Outlook Calendaring Reliability
• Improved Outlook Performance
• Enabling Object Model support for Charts in PowerPoint and Word
• Improved cryptographic functionality by supporting all cryptographic algorithms offered by the operating system
• Improved functionality in Excel’s charting mechanism
• Ability to un-group SmartArt graphics and add animations in PowerPoint
• Ability for Visio to export UML models to an XML file compliant with the XMI standard
• Tool that enables the un-install of Office client Service Packs

Microsoft SQL Server 2008 Feature Pack: October 2008

Download the Microsoft SQL Server 2008 Feature Pack, a collection of stand-alone install packages that provide additional value for SQL Server 2008:

File Name: instructions_enu.htm
Version: 10.00.1600.60
Date Published: 10/17/2008
Language: English
Download Size: 29 KB - 37.9 MB

Microsoft SQL Server 2008 Reporting Services: Report Builder 2.0

Microsoft SQL Server 2008 Report Builder 2.0 provides an intuitive report authoring environment for business and power users. It supports the full capabilities of SQL Server 2008 Reporting Services.

This download provides a stand-alone installer for Report Builder 2.0:

File Name: ReportBuilder.msi
Version: 10.00.1600.60
Date Published: 10/17/2008
Language: English
Download Size: 18.7 MB

Latest Access KB Articles

Courtesy of KBAlertz.com:

Access 2007

• 952699 Description of the Access 2007 hotfix package: May 23, 2008
• 954570 Description of the Access 2007 hotfix package: June 24, 2008
• 956054 Description of the Access 2007 hotfix package: August 26, 2008
• 956838 Description of the Access 2007 hotfix package: August 26, 2008
• 951944 MS08-055: Description of the security update for the 2007 Office system and for the Compatibility Pack for the 2007 Office system: September 9, 2008
• 954326 MS08-052: Description of the security update for GDI+ for the 2007 Office system: September 9, 2008
• 956054 Description of the Access 2007 hotfix package: August 26, 2008
• 956838 Description of the Access 2007 hotfix package: August 26, 2008

Access 2000-2003

• 955179 Microsoft Security Advisory: Vulnerability in Snapshot Viewer for Microsoft Access could allow remote code execution
• 954193 Description of the Jet 4.0 Database Engine cumulative hotfix package for Windows XP SP2 and SP3, Windows Server 2003 SP1 and SP2, Windows Vista, Windows Vista SP1, and Windows Server 2008: July 2, 2008
• 955441 MS08-041: Description of the security update for Access Snapshot Viewer 2000: August 12, 2008
• 955617 MS08-041: Vulnerability in the ActiveX Control for the Snapshot Viewer for Microsoft Access could allow remote code execution
• 955440 MS08-041: Description of the security update for Access Snapshot Viewer 2002: August 12, 2008
• 955439 MS08-041: Description of the security update for Access Snapshot Viewer 2003: August 12, 2008
• 956722 Description of the Access 2003 hotfix package: August 20, 2008
• 954593 MS08-052: Vulnerabilities in GDI+ could allow remote code execution
• 956722 Description of the Access 2003 hotfix package: August 20, 2008
• 954478 MS08-052: Description of the security update for GDI+ for Office 2003: September 9, 2008
• 953404 MS08-055: Description of the security update for Office 2003: September 9, 2008
• 956131 Description of the Office 2003 hotfix package: August 20, 2008
• 956575 Description of the Office 2003 hotfix package: August 7, 2008
• 954192 Only the first 12,000 tables appear in the Link Tables dialog box in Microsoft Access 2000, Microsoft Access 2002, or Microsoft Office Access 2003
• 957198 MS08-041: Description of the security update for Access Snapshot Viewer: August 12, 2008

Free SQL Server 2008 eBook

Download for free the eBook version of the new Microsoft book: Introducing Microsoft SQL Server 2008.

Security Update for Access Snapshot Viewer 2003 (KB957198)

A security vulnerability exists in the Snapshot Viewer that could allow arbitrary code to run when you open a maliciously modified web page or document. This update resolves that vulnerability:

File Name:	access2003-kb957198-fullfile-x86-glb.exe
Version:	1.0
KB Article:	KB957198
Date Published:	10/14/2008
Language:	English
Download Size:	1.1 MB

Download Office 2007 Data Connectivity Components v2.0

This download will install a set of components that can be used by non-Microsoft Office applications to read data from Office 2007 files such as Access 2007 (mdb and accdb) files and Office Excel 2007 (xls, xlsx, and xlsb) files. Connectivity to text files is also supported.

ODBC and OLEDB drivers are installed for application developers to use in developing their applications with connectivity to Office file formats.

Microsoft advises that the Office 2007 Drivers are not designed as a general replacement for Jet: if you need a general replacement for Jet you should use SQL Server Express Edition.

File Name: AccessDatabaseEngine.exe
Version: 2
Date Published: October 3, 2008
Download Size: 25.3 MB

Security Update for SQL Server 2000 Service Pack 4 and MS Access MSDE 2000 (KB948111)

A security issue has been identified in the SQL Server 2000 Service Pack 4 and the MSDE 2000 sql server engine used by MS Access that could allow an attacker to compromise your system and gain control over it. Download the hot fix.

Quick Details:
Version: 8.00.2273
Date Published: 9/26/2008
Download Size: 16.5 MB - 39.4 MB Download size depends on selected download components.

Latest SQL Server Fixes

FIX: SQL Server 2005 Service Pack 2 is not successfully updated when you try to install or to uninstall security update KB948108 or security update KB948109

FIX: No rows are returned when you use the sp_replqueuemonitor stored procedure to list the queued messages for a queue-updating subscription in SQL Server 2005

New v4.0 of SQL Server Migration Assistant 2005 for MS Access

Microsoft SQL Server Migration Assistant (SSMA) 2005 for Access is a tool for migrating databases from Microsoft Access to Microsoft SQL Server 2005. SSMA for Access converts Access database objects to SQL Server database objects, loads those objects into SQL Server, and then migrates data from Access to SQL Server. SSMA for Access supports Access 97 - Access 2007 databases.

Access 2003 and 2007 Hot Fixes

Hot fixes are now available for the following Microsoft Access issues:

Access 2003

• An Access 2003 project (.adp) stops responding when in table Datasheet view, you delete all child rows from a parent row and then delete the parent row. This issue applies only to an Access 2003 project.
• A database in .mdb format in Access 2003 SP3 stops responding when you try to delete a parent record from a table in Datasheet view and the Datasheet view uses a sub-datasheet that is expanded to show the related child records.

http://support.microsoft.com/kb/956722

Access 2007

• In Access 2007, you create a query that does not contain a parameter. When you export the query to a Microsoft Office Excel Worksheet, the Enter Parameter Value dialog box opens.
• When you open a chart object in Access 2007 on a computer that is running Windows Vista or Windows Server 2008, you see a blank chart.
• After you upgrade Access 2003 to Access 2007, operations on existing forms have slow performance.
• You cannot use the SetValue macro in the click events of a Button control to set the Locked property of a Check Box control in Access 2007.
• You have a printer that has a staple feature. When you print an object in multiple copies in Access 2007, all the copies are stapled as one unit. However, you expect that each copy is stapled individually.

http://support.microsoft.com/kb/956054

Security Update for Office 2007

Microsoft today issued a security patch for a vulnerability in Office 2007 that could allow arbitrary code to run when you open a maliciously modified file.

XML in Office 2007

Open XML Format SDK 2.0
Online version: Open XML Format SDK 2.0
XML in Office Developer Portal

Latest SQL Server 2008 Downloads

SQL Server 2008 Microsoft SQL Server 2008 Books Online Release Notes
SQL Server 2008 Express with Advanced Services - English
SQL Server 2008 Express
SQL Server 2008 Upgrade Advisor
SQL Server 2008 Books Online
SQL Server 2008 Enterprise Evaluation: Trial Experience for Developers
SQL Server 2008 Enterprise Evaluation: Trial Experience for IT Professionals

Free Open Source Spell-Checker for Access Run-time

You can download free PCESoft.com's MS Access Spell Checker for Access 97 -2007, including source code from their web site.

Configuring the Windows Firewall to Allow SQL Server Access

Configuring the Windows Firewall to Allow SQL Server Access To access an instance of SQL Server through a firewall, you must configure the firewall on the computer that is running SQL Server to allow access. This MSDN article discusses how to configure the Windows firewall, but the basic principles apply to other firewall programs.

New SQL Server 2008 Articles

New articles from Techrepublic.com on SQL Server 2008:

• More New SQL Server 2008 Features
• How do I use the Microsoft SQL Server Best Practices Analyzer?
• Windows Server 2008 drops RDP console option
• Backup compression in SQL Server 2008

2007 Office Resource Kit Technical Library in Compiled Help format

This downloadable CHM file (3.5mb) is a copy of content in the 2007 Office Resource Kit technical library. The CHM is current as of 29 August 2009.

NEW Ezy Dashboard Builder for Access 2002/2003/2007

My latest Access tool is Access Dashboard Builder, a free cutting-edge tool from aadconsulting.com for quickly building a fully-functional Access database dashboard.

You don't need a switchboard in your existing application to build a dashboard using the Builder, and you don't need to know VBA or write any macros - the builder automatically handles everything.

You add this cool professional-looking dashboard to your existing database:




using an easy to use 1, 2, 3 wizard:







Download

Untangle MS Access Relations with Ezy Documenter Premium

One of the great features of MS Access is the interactive Table Relations Window, where you graphically create and review table joins. But once the joins start to multiply, the relations window becomes unwieldy, and understanding the joins is like untangling a hopelessly tangled ball of string. This is the relations window that greets you when you open the Access 2007 Northwind sample database:


click to zoom...

You can try moving tables around in an effort to get a better perspective, but perversely it appears even more tangled:


click to zoom...

My Ezy Documenter Premium Access add-in to the rescue! With the Table Relationships Report you can in one mouse-click easily review and automatically document even the most complex table relations - click on the screenshot to zoom:


click to zoom...

This report is only one of a number of reports that this Access Add-In produces:


click to zoom...

Check it out - only US$39.95 - demo available

Cool Firefox PDF Plug-In

I must confess to using Firefox (FF) and not IE - it has so many free high quality plug-ins to enrich the browsing experience. The Open in IE add-on allows you to open any web page in an embedded IE window inside a FF tab. This has allowed to me save pages as MHT files. The problem with MHT files are that they takes ages to load in IE and are easily corrupted.

Now NitroPDF has added a cool new feature to its pdfdownload plug-in for Firefox: Convert Web pages to PDF. By clicking File|Save Page as PDF... from the FF menubar you can convert and download any Web page to a PDF file. The quality of the PDF produced is excellent.

Neat Office Toolbar Tricks

There is always something you can learn about MS Office. In a post today on her Tech Republic blog, Susan Harkins reveals two really neat tricks to quickly copy or move a toolbar button in any Office 97 to Office 2003 application

New Excel Chart Wizard

Microsoft Office Labs has developed the Chart Advisor: a prototype add-in that provides an alternate approach for creating charts in Excel 2007 and uses an advanced rules engine to scan your data and, based on predefined rules, displays charts according to score.

Top scoring charts are generated charts, ranked, and displayed as thumbnails in a dialog for preview and tweaking by filtering data and how it is mapped.

I have asked for an Access version.

Latest SQL Server Express Releases

Microsoft SQL Server 2008 Express with Advanced Services a version of SQL Server Express that includes a graphical management tool and powerful features for reporting and advanced text-based searches.

Microsoft SQL Server 2008 Express with Tools a version of SQL Server Express that includes graphical management tools.

Microsoft SQL Server 2008 Report Builder 2.0 provides an intuitive report authoring environment for business and power users. It supports the full capabilities of SQL Server 2008 Reporting Services. The download provides a standalone installer for Report Builder 2.0.

Running Stored Procedures in SQL Server from Access

Randall Rode has posted an article on the process of running parameterized stored procedures in a SQL Server database from an Access client application.

Data Mining: The Parallax View

This look into the future of the semantic web will be fascinating for anyone involved in databases:




More: The Future of the Web

The Lowdown on SQL Server 2008 Express

Destination.NET has published a concise article by Ty Anderson introducing the features and capabilities of the free SQL Sever 2008 Express Edition. In summary:

SQL Server 2008 Express is an easy-to-use database for learning and building small server applications while also providing a seamless upgrade path. It is a solid database intended for real-world applications, and provides most of the security and manageability features available in the full version of SQL Server. The main caveat is that these features come with built-in performance limitations:

• Max CPUs Supported: 1
  
• Max RAM: 1 GB
  
• Max Database Size: 4 GB

Latest SQL Server 2008 Resource Links

These have just been released by Microsoft:

• SQL Server 2008 Release Notes
• SQL Server 2008 Analysis Services Datasheet
• SQL Server 2008 Predictive Analysis Datasheet
• Microsoft SQL Server 2008 Upgrade Advisor
• SQL Server Data Mining Add-ins for Office 2007
• Microsoft SQL Server 2008 Feature Pack, August 2008
• SQL Server Reporting Services Add-in for SharePoint Technologies

Microsoft Releases SQL Server 2008

Microsoft today announced the release to manufacturing of Microsoft SQL Server 2008. Press release

New MS Access (Sub) Blog

I have just come across a new blog, Cyrpis' Lookout, from Renaud that has an MS Access section. So far Renaud has posted some very elegant Access solutions complete with sample databases:

• Checking network paths without freezing your application
• Enhanced Message Box Replacement
• Restarting the database programmatically
• Changing the Access 2007 colour scheme programmatically
• Modal Dialogs with transparent background

More SQL Server Fixes

FIX: SQL Server 2005 Express Edition cannot pass the Windows logo certification when you install an ISV application on a Windows Vista-based computer or on a Windows Server 2008-based computer.

FIX: When you update more than one row in a table that is joined to another table in SQL Server 2005, some rows are not added.

Latest SQL Server 2005 Fixes

FIX: Error message when you try to insert data on a subscriber of a merge replication in SQL Server 2005: "Msg 548, Level 16, State 2, Line 1. The insert failed"

FIX: The size of a database keeps increasing when you perform many DELETE operations and many INSERT operations on the database in SQL Server 2005

Security Update for SQL Server 2000 SP4 and MSDE 2000

Microsoft today issued a security hotfix for SQL Server 2000 and the Access MSDE engine:

Version:	8.00.2050
Date Published:	7/29/2008
Language:	English
Download Size:	10.3 MB - 29.6 MB*

Ezy Dashboard Builder BETA Now Available




The free BETA of Ezy Dashboard Builder for Access 2002/2003/2007 is now available for download. You don't need a switchboard in your existing application to build a Dashboard using the Builder.

Coming soon... Ezy Dashboard Builder
from aadconsulting.com

I will soon be launching a cool new add-in for Access, that will in a few minutes transform your Access application switchboard from this:



to this:



using an easy to use 1, 2, 3 wizard:







A free BETA will be available shortly. Watch this space

Create Custom Menus and Shortcut Menus in Access 2007

An updated article on the AddMenu Macro Action and the new topic Create Custom Menus and Shortcut Menus using Macros in Access 2007 are now available on Office Online.

These techniques are the only way in Access 2007 to create custom menus and shortcut menus. The custom menus appear in the Add-Ins tab on the Access Ribbon, and can be assigned as right-click pop-up menus for a form or report by assigning the custom menu to the object's Shortcut Menu property.

Build Related Tables with the Access 2007 Lookup Wizard

Mary Ann Richardson of TechRepublic.Net has posted an interesting article on using the Access 2007 Lookup Wizard to create a related table.

Access To VB6 Wizard

I have today trialled Access to Visual Basic 6 Converter from Microtools.us, and have found it the best such tool I have seen. The product seamlessly converts all forms in an Access database, and optionally the code behind forms and the required recordsets, to build a complete VB6 database project, which then requires only minimal tweaking. The VB data forms include the VB6 data control and data operation buttons:



Other Access conversion products include:

• Access to ASP .NET Visual Basic Converter
• Access to Visual Basic .NET Windows applications Converter
• Access to ASP .NET Visual C# Converter
• Access to Crystal Reports 8/8.5/9/10/11/11.5 Converter
• Access Form Controls to Visual VB .NET/ASP Converter
• Access Form Controls to Visual VC# .NET/ASP Converter

Using Access and Excel Together

The MS portal, Office Online, has posted a substantial article on using Access and Excel together to manage data solutions: Top 10 reasons to use Access with Excel. This is the introduction to the article:

When using the Office family of products, should you use Excel or should you use Access to manage your tabular data? A companion article, Using Access or Excel to manage your data, recently discussed the benefits of each product and what they "bring to the table". But why choose between one or another? If you store your data in Access and connect to it from Excel, you gain the benefits of both. Here are ten reasons why a marriage between Excel and Access makes lots of sense. Excel users, don't be afraid to let go of your data. Think of it this way. You're not losing an Excel daughter; you're gaining an Access son-in-law.

Access 2007 Run-time Deployment Issues

Today, the Access Team Blog features an interview with the MS program manager for the Access 2007 Runtime, covering deployment issues that arise where there are multiple versions of retail Access installed.

Microsoft to Offer Cheap Hosted Exchange Services

Microsoft has announced it will provide a new low-end Exchange Server service, Deskless Worker Suite, for US$3 per user per month. A complete Microsoft-hosted Business Productivity Online Suite — Exchange Online, SharePoint Online, Office Communications Online (for instant-messaging), and Office Live Meeting - will cost US$15 per user per month. There will also be an option allowing users to subscribe to individual Microsoft-hosted services.

Security Fix for SQL Server and MSDE Released

Microsoft today released
Security Bulletin MS08-040 – Important Vulnerabilities in Microsoft SQL Server Could Allow Elevation of Privilege (941203):

This security update resolves four privately disclosed vulnerabilities. The more serious of the vulnerabilities could allow an attacker to run code and to take complete control of an affected system. An authenticated attacker could then install programs; view, change, or delete data; or create new accounts with full administrative rights.

This security update is rated Important for supported releases of SQL Server 7.0, SQL Server 2000, SQL Server 2005, Microsoft Data Engine (MSDE) 1.0, Microsoft SQL Server 2000 Desktop Engine (MSDE 2000), Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2000 Desktop Engine (WMSDE), and Windows Internal Database (WYukon). For more information, see the subsection, Affected and Non-Affected Software, in this section.

The security update addresses the vulnerabilities by modifying the way that SQL Server manages page reuse, allocating more memory for the convert function, validating on-disk files before loading them, and validating insert statements. For more information about the vulnerabilities, see the Frequently Asked Questions (FAQ) subsection for the specific vulnerability entry under the next section, Vulnerability Information.

Recommendation. Microsoft recommends that customers apply the update at the earliest opportunity.

Access Security Alert: Snapshot Control

Microsoft has today posted Security Advisory 955179, which contains information regarding active targeted attacks using a vulnerability in the Snapshot Viewer Active-X control in Access 2000, Access 2002 and Access 2003.

Currently only manual workarounds are included in the Advisory. Although these workarounds help block known attack vectors, the underlying vulnerability is not corrected.

MS says attacks appear to be targeted and not widespread.

Free Security Tool for MS Office Files

Security company Sourcefire has released a freeware utility to help identify potentially unsecure Microsoft Office files. The tool, called OfficeCat, can be used to scan Word, PowerPoint, Excel and Publisher documents. Unlike other products that detect attempts to exploit known Microsoft vulnerabilities, Sourcefire said OfficeCat can determine if a file contains hostile content before it is opened.

More info from source: ZDNet.com

Using Access with SQL Server: Panel Discussion from TechEd 2008

Check out this TechEd panel discussion: Are we there yet? Successfully navigating the bumpy road from Access to SQL Server.

The discussion is described as follows:

“Use SQL Server, not Access”, is advice commonly handed out when the Jet database engine outgrows its usefulness. However, that’s often easier said than done. Each database application is unique; some may have salvageable components, whereas others may need to be redesigned and rebuilt from scratch. In addition, there are substantial differences between the Jet and SQL Server database engines that can make a straight port unworkable in many situations. This panel discusses criteria for deciding whether to migrate data from Access to SQL Server, and for determining the optimal migration path once the decision is made. The panelists will share their real-world expertise to help you determine a successful strategy that best suits your business needs. With Mary Chipman, Armen Stein, Luke Chung, Paul Sheriff, and Stephen Forte."

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


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

I have just uploaded two new Access 2007 Themed Native SideBar Switchboard Demos, which feature Outlook-style application navigation using only native Access controls.

There are now FIVE themes:

• Windows XP
• Access 2007 Vista NOIR
• Access 2007 Black
• NEW Access 2007 Silver
• NEW Access 2007 Blue
  

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
• FIVE 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.

Download the Native SideBar Switchboard Demos.

Office 2007 Security Guide

Understanding the 2007 Microsoft Office Security Guide This guide from Microsoft sets out best practices and automated tools to help strengthen the security of computers running Office 2007 on Windows XP/Vista.

What's New in Microsoft SQL Server 2008: Free Courses

Free E-Learning: What's New in Microsoft SQL Server 2008 A free E-Learning collection from Microsoft. In three clinics, participants learn what's new in SQL Server 2008 for Enterprise Data Platform, SQL Server 2008 for Business Intelligence, and SQL Server 2008 for Database Development.

Improving Access Database Performance

I just came across this very useful article on Mircosoft's Office On-line portal:
Improve Performance of an Access Database.

Significant performance issues for Access developers are deploying Access databases over a network and in multi-user environments. This article has some really well-presented advice and tips. A tip on improving the performance of linked tables is particularly helpful:

You can greatly enhance performance, when opening the main database and opening tables and forms, by forcing the linked database to remain open. To do this, create an empty table in the linked database, and link the table in the main database. Then, use the OpenRecordset method to open the linked table. This prevents the Microsoft Jet database engine from repeatedly opening and closing the linked database and from creating and deleting the associated .ldb file.

New Access 2007 Backup Before Compact Tool

As reported in my AccessExtra post Access 2007 Bug Alert: Compact and Repair Might Delete Your Database last week, Access KB Article 950812 describes a scenario where a compact and repair in Access 2007 deletes the database. You can’t download the fix directly but can get it by calling MS Customer Service. This fix will be included in SP2 when that ships.

Backup Before Compact is a free Access 2007 Add-In that I have developed that 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.

Updated BETA of PRO Version of 123 Error Handler Wizard
Add-In and Wizard for Microsoft© Access

Click on image to zoom in...

Download (580kb) the latest free BETA of 123 Error Handler PRO Wizard:

1. The Wizard reviews your database and identifies all procedures without error-handling, and reports the On Error... statement for all other procedures.
2. You select which procedures to add error-handling.
3. The Wizard adds error-handling to the selected procedures in one automated operation

The PRO version has these additional features:

• User-definable custom error-handlers, and
• Automatically add/remove line numbers to your code for enhanced error-handling.
  
• The June 2008 Update adds an Error Handling Status report to Access 2000+ versions

The most important part of error-handling is locating where in your code an error occurs. The PRO version of 123 Error Handler can help you pin-point the line of code where an error originates, and you can add or remove line numbers to a module with a single mouse-click.

Adding a Program to an Access 2007 Deployment Package

This new training video from Microsoft shows you how to add an add-in to an Access 2007 deployment package created using the Package Solution Wizard.

Dot vs. Bang: Put to Rest

The issue of whether two use a DOT(.) or BANG(!) when referring to a form field or control in Access VBA viz:

• Form1.Control1
• Form1!Control1

has been an issue of contention ever since Access 95.

Now Access MVP Michael Kaplan in a post on the Access Team Blog has provided a definitive guide:

• The dot gives you early binding and is resolved at compile time, the bang is resolved at runtime.
• In the case of Forms, both controls and fields in the underlying query can be referenced via a dot since they are all in the type library for the form.
• Also in the case of Forms, if you change the underlying query at runtime - dot references to the old query's fields will fail at runtime since update of the Form's type library cannot happen at runtime.
• Because the dot is early bound, IntelliSense happens by default with the dot, not with the bang.
• The dot (since it is early bound) is faster than the bang, but no one runs enough code that uses these items enough for the performance difference to actually matter.

I have been a long-time convert to the DOT based on the sheer convenience of the IntelliSense default being the DOT.

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