MS Office Migration Articles from Microsoft:

Whitepaper: Microsoft Office 2000 to Microsoft Office 2003 Migration

Whitepaper: Office 97 to Office 2003 Migration

Whitepaper:: Microsoft Office XP to Microsoft Office 2003 Migration

How to Customise Menus and Menu Bars in MS Office This article uses MS Excel to demonstrate customisation of Office CommandBars.

Query Dependencies in MS Access Tracking complex query dependencies in Access was difficult before Access 2003, which provides this functionality natively. But what abour earlier versions of Access? Thankfully, questica.com provides an excellent add-in for earlier Access versions as donationware. Just run the setup.exe file and all your installed versions of Access will now have this functionality: right-click on a query in the Queries tab of the Database Window and select Dependencies from the popup menu to see a treeview of the dependencies in a custom dialog. There are addtional features: run a query from the dialog or view the design, or copy the dependencies to the Clipboard. Download

G-Mail I have another invitation to give away... Go!

New Articles of Interest:

How to Customise Menus and Menu Bars in Excel Applies generally to Office 2000 and later.

Security in Office 2003 Editions How to hook into the security features in Office 2003 Editions and, more importantly, how to avoid accidentally circumventing them in your Office-based solutions.

Working with Files and Folders in Office 2003 Editions Tips on how to use the Office object model to enhance your custom solutions.

MS Access KB Browser Now On-line All the Access KB article links from my Access Knowledge Base Browser application are now available on-line. Try it. You can browse or search 2,752 Access KB article references in your browser, with a link to each article at the Microsoft KB site.

BTW, the KB Browser application is free for all buyers of a Premium Access Tool from aadconsulting.com, with prices starting from a low US$9.95.

Access Knowledge Base Browser The Articles database for my Access Knowledge Base Browser has been just updated. The number of article references has been increased by nearly 101 to 2,742 article titles. The KB Browser is an easy way to search the on-line Microsoft Access Knowledge Base. Existing Users can click the "Update KB Table" button on the Application Toolbar to get the update. KB Browser is free for all buyers of a Premium Access Tool from aadconsulting.com, with prices starting from a low US$9.95.

New MS Access KB Articles
Use this URL: http://support.microsoft.com/default.aspx?scid=kb;EN-US;xxxxx
where xxxxx is the Article No.

196809 ACC97 CompactDatabase Method Requires Locale to Convert 2.0 MDB
148287 ACC2 Neatcod2.mdb Available in Download Center
148402 ACC95 Neatcode.mdb Available in Download Center
148572 ACC95 Viewable Wzcnf70.mda Code Available in Download Center
148581 ACC95 Using Updatable Queries White Paper Available in Download Center
149357 ACC2 Parameter Typing Utility Available in Download Center
149358 ACC2 Sample to Undo Changes in Forms Available in Download Center
149360 ACC2 Multiuser Wizards Available in Download Center
209505 ACC2002 Expressions to count yes, no, and other responses in Access
235280 ACC2000 """Can't Find the Database You Specified"" Error When Starting Access 97 After Upgrade to Office 2000"
259134 ACC2000 Microsoft Policy on Forgotten Password Assistance with Access 2000
207868 ACC2000 """Microsoft Access Can't Represent the Join Expression"" Error Message in Query Design"
225989 ACC2000 Cannot Use Access Client-Server Tools After Installation of Microsoft Data Engine
236531 MOD2000 Cannot Insert HHCtrl.ocx into a Microsoft Access Form
232595 ACC2000 How to Sum a Calculation in a Data Access Page
241471 ACC2000 """Microsoft Jet Database Engine Could Not Find the Object"" Error Message Exporting Visual FoxPro Table"
234343 ACC2000 How to Secure Database Diagrams in a Microsoft Access Project
209643 ACC2000 How to Determine the Version of Catalog Stored Procedures
245181 ACC2000 Expression Builder Is Not Available in Visual Basic Environment
244904 ACC2000 ODBC Call Failed (#200) Error Running a Union Query on Linked Visual FoxPro Tables
255132 ACC2000 """Stop 0x1e"" Creating a Report with the Access Report Wizard"
282751 ACC2000 Linked Excel Worksheet Grows by 50 to 100 Percent When Updated in Access
240962 ACC2000 Starting Hyperlink from Access Minimizes Access Session
210181 ACC2000 Visual Basic Editor Displays Hidden Modules
208888 ACC2000 How to Attach Existing SQL Server 7.0 Database Files to Microsoft Data Engine (MSDE)
197232 ACC2000 New Microsoft Excel Data Does Not Appear in Microsoft Access Form
223201 ACC2000 Shortcut Keys May Not Work in Access Project
197111 ACC2000 Lookup Wizard Error
207597 ACC2000 AutoLookup Field Returns Unexpected Value in Word Mail Merge
207632 ACC2000 TransferText Ignores Indexes in Import Specification
209170 ACC2000 Starting Another Application from a Text Box on a Form
209148 ACC2000 Cannot Save a Blank Record in a Table or Form
207662 ACC2000 Parameter Prompt If LinkMasterFields or LinkChildFields Contains Table Name
209132 ACC2000 Troubleshooting Tips for Error Values
207624 ACC2000 How to Create a 'Continued' Header with Groups That Span Multiple Pages
205703 ACC2000 Can't Import Single Cell Excel Named Range
207471 ACC2000 You Receive No Warning When a Number Is Too Large for a Single Data Type
208178 ACC2000 GoToControl to Control with Focus in AfterUpdate Ignored
205596 ACC2000 Optional Fields Not Added to Database Made by Database Wizard
275563 ACC2000 Programmatically Populated Combo Box or List Box Is Not Sorted in the Order Expected
209759 ACC2000 Unsupported SQL Keywords Are Not Listed in Microsoft Access
223248 ACC2000 PreviousSection Property Causes Microsoft Access and Internet Explorer to Quit Unexpectedly
225891 ACC2000 Microsoft Script Editor Does Not Execute the OnBeforeUnload Window Event Procedure
231839 ACC2000 Cannot Use Legacy Database Templates with Access 2000
234303 ACC2000 How to Start Another Application from a Data Access Page
204204 ACC2000 Option Buttons on a Data Access Page Must Have Name and ID Property Values
197221 ACC2000 Error Message Clicking Start Page on Web Toolbar
199086 ACC2000 Can't Use ENTER to Move Data to Next Line of Wizard
203447 ACC2000 Hiding Control on a Page Doesn't Hide Control's Label
209001 ACC2000 How to Print a Variable-Length Character String
209006 ACC2000 How to Control the Number of Records Printed per Page
199253 ACC2000 Spelling Checker Ignores Misspelled Uppercase Words
209072 ACC2000 Two Device Independent Bitmaps in Paste Special Dialog Box
209108 ACC2000 How to Use In-place Activation with OLE Objects
198666 ACC2000 Standard Toolbar Not Available in Microsoft Access
235267 ACC2000 Table in a Microsoft Access Project Cannot Be Updated
240246 ACC2000 Operational Range for Dates in Microsoft Access 2000

G-Mail I have two invitations to give away... Go!

What is XML? The XML Programming Bible covers all the most recent XML core and related specifications including XML 1.1, J2EE 1.4, Microsoft .NET's latest iteration, as well as open source XML items from the Apache project. You can download free the first chapter - an excellent introduction - as a.pdf.

Importance of Installing MS Office Patches Access 2000 error, "Network connection may have been lost," might indicate Vbe6.dll conflict. An article for Tech Republic: In Access 2000, receiving the message "Error accessing file. Network connection may have been lost" may not really reflect a connection issue. Instead, it may merely reflect the hazards of patching security holes over time. The solution is another patch, of course.

Access SQL Tip From TechRepublic: Search for two or more single characters in a field. You can use the [ ] wildcard with the Like operator in your queries to search for two or more single characters in a field.

For example, suppose you want to find all customers with the following ZIP codes: 08052, 08053, or 08055. To use the [ ] wildcard, enter the following in your query's Criteria row under the ZIP Code field:

Like "0805[235]"

This expression searches for all field entries whose last character matches one of the characters specified between the brackets. Conversely, to search for all customers that don't live within these three ZIP code areas, place an exclamation point before the list, as shown below:

Like "0805[!235]"

The exclamation point inside the brackets stands for Not in the list. The query results will include all entries whose characters do not match any character in the list within the brackets.

You can combine the [ ] wildcard with any other wildcard character. For example, you can combine the * wildcard character with [ ] to search for any ZIP codes that begin with 0805, 0807, or 0808:

Like "080[578]*"

New from MSDN:

Creating Charts in Excel 2003 Using Visual Basic for Applications Code Methods of creating charts though the macro recorder, using VB and automation.
Building Office 2003 Research Services That Work Offline Give users the benefit of research services whether they are in their office plugged into the network or writing a proposal on a plane without network connectivity.
Migrate Word VBA Solutions to Visual Studio Tools for Office Learn how to migrate an existing Word VBA solution to a Visual Studio Tools for Office solution.
Microsoft Office Access 2003 VBA Language Reference The new objects, properties, and methods in Access 2003.
Microsoft Office Excel 2003 VBA Language Reference The new objects, properties, methods, and events in Excel 2003.
Migrating Word VBA Solutions to Visual Studio Tools for Office Explores the process of migrating an existing solution to managed code in Visual Studio Tools for the Microsoft Office System.

Access Knowledge Base Browser The Articles database for my Access Knowledge Base Browser has been just updated. The number of article references has been increased by nearly 100 to 2,641 article titles. The KB Browser is an easy way to search the on-line Microsoft Access Knowledge Base. Existing Users can click the "Update KB Table" button on the Application Toolbar to get the update. KB Browser is free for all buyers of a Premium Access Tool from aadconsulting.com, with prices starting from a low US$9.95.

Access Extra MS Access Reources CD v5.0 I have also just launched the Updated CD full of MS Access and MS Office resources: a massive 650mb of articles, code, add-ins, wizards, utilities and sample databases. Take your Access skills to the next level: the contents is fully organised and classified. You can use the CD's Searchable Browser Application to locate content.

Quick MS Access Tips:

Give your applications a quick makeover by globally replacing MS Sans Serif font with Tahoma.

Overcome the limited color choices offered by the Format menu for the backcolor of cells in datasheets by using VBA:

Private Sub Form_Open(Cancel As Integer)
Me.DatasheetBackColor = 12910591 'a soft yellow
End Sub

Free Evaluation of InstallShield X The new InstallShield X creates Windows Installer (MSI), InstallScript, and cross-platform installations and extends them to database servers, Web services, and mobile devices.

Tutorial: Validating XML Learn what validation is and how to check a document against a Document Type Definition (DTD) or XML Schema document.

I recently discovered the MSDN Library Archive for older content.

New Articles:

Bind an Access Form's Record Source from an XML File at Runtime Learn how to bind a form dynamically to a recordset created from an XML file.

Excel 2002/2003 Add-in for SQL Server Analysis Services Access multidimensional data, perform in-depth analysis, and create flexible, interactive reports in Microsoft Excel.

Access 2003/2002/2000 Sample: Summing in reports sample database Provides example reports that demonstrate creating group sums, report sums, page sums, running sums, and how to number the rows in your MS Access reports.

Office Talk: Easy Task Reports with Outlook and WordML How to export Outlook 2003 task data to XML and then use this as a source for reports in Word 2003. Sample Files

Recent MS Access Knowledge Base Articles:
Use this URL: http://support.microsoft.com/default.aspx?scid=kb;EN-US;xxxxx
where xxxxx is the Article No.

Access 2000

840644 "There are no Office Assistant character files present" error message when you start an Office 2000 program for the second time
841530 Office 2000 post-Service Pack 3 hotfix package: May 13, 2004

Access 2002

839781 You may receive an error message when you try to open a data access page in Access 2002
827983 Access 2002 post-Service Pack 3 hotfix package: May 7, 2004

Access 2003

838983 You may receive "The feature you are trying to use is on a network resource that is unavailable" error message when you try to remove an Access 2003 runtime application from your computer

Access

826137 You may receive the "Can't find the database you specified" error message when you open an Access database by using a shortcut
824165 ActiveX control that is contained in an Access form does not appear when the design-time license is not available
827983 Access 2002 post-Service Pack 3 hotfix package: May 7, 2004
826760 Incorrect object dependencies information appears for database objects in a read-only database
828411 You cannot use the MSODSC control when you insert an Office Chart 11.0 control in a data access page
841779 How to reset the page number and the total page count for each group in a Microsoft Access report
840992 Description of the Office XP post-Service Pack 3 hotfix package: May 8, 2004
828412 Programmatically modifying an action query in an Access database invalidates the digital signature of the Access database
824189 The AutoExpand property may not work when ANSI-92 syntax is enabled and the Combo Box RowSource does not use DISTINCT values
824171 Administrative installation of the Access Run-time Application is not successful
824176 "The Text You Entered Isn't an Item in the List" error message when you programmatically add a new record to a table by using the NotInList event of the Combo Box on a form
824180 Compilation is not successful when you convert a macro to a module
823228 BUG: Field size is changed when you copy and paste a table in an Access project
825445 Data remains unchanged when you modify dates by using the Calendar Control
824279 "Undefined Function '{FunctionName}' in Expression" error message when you open a data access page in Internet Explorer
824261 Jet 4.0 OLE DB Provider requires a Jet 4.0 system data

Recent MS Access Tips I stumbled upon:

A neat way to test both for Null or an empty string using the MS Access Nz function:

If Nz(Me.txtFaxNo,"") = "" Then Me.cmdSendFax.Enabled = True

MS Access Shortcut Keys for data entry:

[Shift][Enter] to save a record
[Ctrl][Alt][Spacebar] to re-instate the default value
[Spacebar] to toggle values in a check box or option
[Ctrl][;] to insert the current date
[Ctrl][Shift][;] to insert the current time
[Ctrl][+] to add a new record
[Ctrl][-] to delete the current record
[Esc] to undo a change to the current record

Check the Access Extra Newsletter Archive for more shortcuts


More new MS Office stuff from Microsoft:

Microsoft Office Information Bridge Framework 1.0 - BETA
A set of components, tools, and prescriptive guidance that enable developers to create solutions that connect Microsoft Office System applications to enterprise systems.
Microsoft Office Information Bridge Framework 1.0 Resource Kit - BETA
The Kit contains tools and sample code designed to make MS Office development job easier.
Office 2003 Editions: VBA Language Reference for the Graph Object Model
The Visual Basic® for Applications (VBA) Language Reference for the Microsoft® Graph Object Model as a compiled Help file.
Office 2003 Tool: Local Installation Source Tool
A wizard to help manage Local Installation Source (LIS) on computers running Microsoft Office 2003
Microsoft Office Data Assistant for PowerPoint 2003
An easy-to-use method of inserting and managing graphical data objects such as Microsoft Office Visio® drawings, Microsoft Office Excel charts, and named ranges into PowerPoint presentations.

MS Office 2003 Editions: VBA Language References

PowerPoint VBA Language Reference

Access VBA Language Reference

VBA Language Reference for the Document Imaging Object Model

FrontPage VBA Language Reference (Page Object)

MS Office XML Developer Center The deal on using XML in Office

Microsoft Office 2003 Editions XML Reference Schemas and Documentation: April 2004 Update

MS Outlook 2003 Sample Custom Calendar Providers for Outlook 2003: sample files for creating custom views in MS Outlook using a Web service.

Microsoft DevDays 2004 Presentations Tracks on how to design, develop, and deploy Windows applications

Designing a Better GUI A very useful article on constructing better MS Access data entry forms from ssw.com.au

TIP: Enforce UpperCase Input Mask

VBA Ascii values 97-122 represent the lowercase letters a-z and 65-90 the uppercase letters A-Z: each uppercase letter's value is 32 less than the corresponding lowercase value.

In a control's KeyPress event procedure add the following code:

Private Sub txtMyName_KeyPress(KeyAscii As Integer)

If KeyAscii >= 97 And KeyAscii <= 122 Then
KeyAscii = KeyAscii - 32
End If

End Sub


Double Quotes Demystified

In string variables and when constructing SQL strings in VBA, using double-quotes can be confusing and frustrating. Try these approaches:

1. Create a global constant to represent double quotes, which makes code easier to read:

Public Const pubconQuotes = """"

Example: strSQL = "Instr(MyField," & pubconQuotes & " " & pubconQuotes & ")<>0"

2. Example: strSQL = "Instr(MyField," & chr(34) & " " & chr(34) & ")<>0"


TIP: Alternate line shading in MS Access Reports

1. Declare a constant in the Declarations of the Report's module:

Const vbLightGrey = 12632256

2. Add the following code to the Detail section OnFormat Event:

If Me.CurrentRecord Mod 2 = 0 Then
Me.Section(acDetail).BackColor = vbLightGrey
Else
Me.Section(acDetail).BackColor = vbWhite
End If

The Report will display a white background for odd records and a light gray background color for even records. The BackStyle of record TextBoxes should be set to Transparent.

Recent MS Access Knowledge Base Articles Use this URL: http://support.microsoft.com/default.aspx?scid=kb;EN-US;xxxxx
where xxxxx is the Article No.

841431 You may receive an error message when you try to modify the field information in the Access 2003 Import Wizard
840169 Full-file version update may request Office source files
837146 Dialing rules are ignored when you use the AutoDialer feature in Access
840169 Full-file version update may request Office source files
292634 How to Use a Query to Filter Unique Data
837001 MS04-014: Vulnerability in the Microsoft Jet Database Engine could permit code execution
837146 Dialing rules are ignored when you use the AutoDialer feature in Access
223452 GRAPH2000: Pattern Fills Do Not Print Well
839782 You receive a "You do not have exclusive access to the database at this time. If you proceed to make changes, you may not be able to save them later." error message when you try to open database objects in Access
839785 The data in a linked Excel spreadsheet column is truncated to 255 characters in an Access database

Just Published:

A Developer’s Take on Smart Tags Demystifies Smart Tags.

Smart Documents Résumé Sample Application Smart Documents Résumé Sample Application for Microsoft Word 2003

Office 2003 Tool: Add a Bilingual Translation Dictionary Service to the Microsoft Office System

Windows Script Control Download the Microsoft® Windows® Script Control is an ActiveX® control that provides developers with an easy way to make their applications scriptable.

ADOMD.NET ADOMD.NET is a .NET object model, used for building client data access applications, for accessing an XML for Analysis (XMLA) 1.1 compliant data provider, such as that provided by the XML for Analysis 1.1 SDK.

Calling Visual Basic for Applications Code from Visual Basic.NET Learn how to run VBA procedures in key Microsoft Office programs directly from Visual Basic.NET.

Office2003 Tool: Add a Bilingual Translation Dictionary Service to the Microsoft Office System Sample source code and helper files used in the preparation of bilingual translation dictionaries for use with the Microsoft Office System.