Ways to share an Access database

A good article from Office.com on how to deploy run-time and web-based Access applications.

Streamline Reporting on Recurring Data Imports

The article from Microsoft walks you through the creation of an Access database that supports importing monthly sales data from a text file, and provides the sample data used to create the article, as well as the database file complete with all the data imported.

The scenario comprises the following tasks:

1. Set up an import specification to allow Access to import the same data in future months.
2. Use the built-in Table Analyzer tool to create a normalized structure for the data.
3. Create a set of append queries that will parse the incoming data into the normalized structure.
4. Write a macro that automates the data import process.

Northwind 2010 Web Database is now available

A new version of the Northwind sample database has just been released. The best way to get to the template is to start up Access 2010, under Office.com Templates, click on Sample Templates, and then click on Northwind Web Database. You can also get the template here: Northwind Web Database. 

New Access 2010 Resources PDFs

Download:

• Microsoft Access 2010 Features and Benefits
• Microsoft Access 2010 Getting Started Guide
• Microsoft Access 2010 Product Guide
• Microsoft Access 2010 Reference Access 2003 to Access 2010

Free Corporate Event Manager for Access 2007 and Access 2010

I have just uploaded a cool new Access application that streamlines the process of managing corporate events. No other free database application offers the degree of functionality offered by the The Event Manager application. Download ( 210kb Zipped ACCDE file for Access 2007/2010).

New MS Office Developer Portals

• Office VBA Developer Center
• Office Developer Center
• Developing Office Business Applications with Office 2010 and SharePoint Server 2010

Latest Access KB Alerts

Courtesy of KbAlertz.com:

Access 2002
977896   MS10-003: Description of the security update for Office XP: February 9, 2010
978214   MS10-003: Vulnerability in Microsoft Office (MSO) could allow remote code execution


Access 2003
977645   Description of the Office Access 2003 hotfix package (Msaccess.msp): December 15, 2009
983235   MS10-036: Vulnerability in COM validation in Microsoft Office could allow remote code execution
982311   MS10-036: Description of the security update for Office 2003: June 8, 2010


Access 2007
978402   Description of the Office Access 2007 hotfix package (Access-x-none.msp, Ace-x-none.msp): February 23, 2010
981049 Description of the Access 2007 hotfix package (Access-x-none.msp, Ace-x-none.msp): April 27, 2010
201284 When you create a Pivot Chart or a Pivot Table in Microsoft Office Access 2007, the Field List is not visible
201284   When you create a Pivot Chart or a Pivot Table in Microsoft Office Access 2007, the Field List is not visible
982312   MS10-036: Description of the security update for the 2007 Office system: June 8, 2010

Testing Access 2010 BETA? - Free Access Sharepoint Hosting Trial

Access Hosting is a full service hosting platform and data migration service for current Access databases. They are offering a limited free hosting trial with SharePoint Server 2010 Beta for early adopters. This is a sandbox beta hosting environment designed to allow developers to try out the service without having to install and configure SharePoint.

Free Access Stuff from aadconsulting.com

A reminder of some of the Access tools available free from aadconsulting.com:

Sample Date Picker for Access Databases Versatile Vista/Win7 styling and functionality.

Kiosk Dashboard Sample Access Database Mouse-driven full-screen modal kiosk dashboard.

Access Dashboard Builder A cutting-edge tool 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 wrtie any macros - the builder automatically handles everything.

Office 2007 Ribbon XML File Reviewer Open any Office 2007 Ribbon XML file to review an Office Ribbon's full menu hierarchy in a tree-view with all control attributes detailed in an adjacent panel. Extensive reference resources incorporated into the GUI.

Access 2007 USysRibbons Reviewer Add-In Preview the custom Ribbons in the USysRibbons table of an Access 2007 database. Extensive reference resources incorporated into the GUI.

Access Control Center "Get Centralized Project Management for Microsoft Access" from independent review 1 Dec 2003 by Access Advisor Zone. Open the correct version of Access. Easy compacting and decompiling, workgroup switching. Projects are managed centrally. MDBs are opened by the appropriate Access version automatically. Includes a shortcut wizard, and many other features!

Access SideBar Add-In for Access 2000/2002/2003 Uses only native Access controls: no DLLs/OCXs The easy way to navigate objects in your Access MDB, the Access SideBar is available at any time. After opening an object the SideBar is hidden, and pops-up by moving your mouse to the left edge of the screen.The SideBar window size is independent of the size of the Access application window, so you can locate all objects without resizing the application window. A custom right-click pop-up menu gives easy access to useful Access Menu commands. The download also includes a second wider version.

Classic Database MenuBar and Window for Access 2007 This free sample Access 2007 database gets you beyond the frustrations of learning the new Office 2007 Ribbon and helps you learn the location of familiar commands in the unfamiliar Ribbon environment.

A Better ZoomBox A free tool for Microsoft Access versions 97 to 2003 to edit the following control properties when designing Access Forms and Reports: Record Source, Row Source, Control Source. Full find/replace functionality and saved customization font and Enter Key settings.

Access DASHBOARD® Manage all your Access databases in one application. The DASHBOARD automatically determines in which version of JET a database was created. Each file is opened in the appropriate version of Access. Use the Shortcut Builder to create custom Desktop shortcuts. Backup, Repair, Compact, and Decompile database files without leaving the DASHBOARD.

Custom Form and Report Design Toolbar A handy Custom Toolbar for Access, for faster and less tedious resizing and positioning of form and report controls: import the Toolbar into any Access97 or later database. For example, you can resize a selected group of controls to match the smallest or largest of a group, in just two mouse-clicks.

Table Links and Connections Manager The Connection Manager will find all the Microsoft Access files in the location you select and a report will display all table links and base string connections, including dependents of, and any connect errors grouped by: Jet Version, File Type, Linked Database Paths and linked tables, and Base Connection Strings. Separate reports are produced for .mdx and .adx files.

Access Database Mechanic Automates the process of attempting to recover a corrupt MS Access database, using three operations: compact & repair, decompile, and complete rebuild of an .mdb. The applet also features a fail-safe mechanism that creates a uniquely named back-up copy of the .mdb before any recovery operation is attempted. Versions for Access 2000/2002/2003 and Access 2007.

Ezy Access 2000 Access is intimidating for new users and the learning curve is steep. Here is a new tool that makes using Access a WHOLE lot easier: an Access database for retail MS Access that lets you easily create Access databases with little or no knowledge of Access.

Ezy Debug A frustration in debugging in Access 2000 and later is the clunky rigmarole required to check/set error-trapping. Now you can dramatically simplify the process. Ezy Debug is launched from the right-click popup menu in the code window of the Access VB Editor. The applet provides quick access to the Access error-trapping settings in the code window, and once invoked it is available both in the Access Database and VBE window. The current error-trapping setting is displayed and can be hanged on the fly...

Two Excellent Coding Blogs for Access Developers

I recently discovered these two bogs maintained by Shanmuga Sundara Raman Murugan, a Systems Engineer:

• VBA Tips & Tricks
  
  
• SQL Tips & Tricks

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.

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

XML in Office 2007

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

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.

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.

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."

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

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.

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

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.