Friday, May 28, 2004

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.

Friday, May 21, 2004

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)

Thursday, May 20, 2004

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

Monday, May 17, 2004

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

Saturday, May 15, 2004

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
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:;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.

Saturday, May 01, 2004

Effective MS Access Security? Did you know that:

 1. You can access the Start-Up properties (such as disabling the Shift key bypass) of an .mde through an .mdb and change each property

 2. You can open an .mde with the Shift key, press Ctrl+G to open the Debug window, press F2 to open the Object Browser, and then search all the code modules and constants.

 3. You can import all the form and report objects but not the code from an unsecured .mde into an .mdb.

To effectively secure an Access database you MUST demote the Admin user from the Admins group. Otherwise your database will not be secure, as Admin cannot be removed from the Users group, and anyone using the retail system.mdw file logs on automatically as Admin.

Securing An Access Database

1. Use the Access Workgroup Administrator (AWA), wrkgadm.exe, to create a new workgroup (.mdw) file.

2. Join the new workgroup using the AWA.

3. Open Access and the database to be secured.

4. Using Tools, Security, User and Group Accounts..., in the User and Group Accounts dialog:

4.1 Create a password for Admin user.

4.2 Create a new user account. This account will be the new database owner account. For example, call the owner account DBOwner. Add "DBOwner" to all groups, including the critical Admins group.

5. Close and re-open Access, logging on as "DbOwner", and leaving the password blank, as you have not assigned one yet.

7. In the User and Group Accounts dialog, demote the Admin user account by removing it from the Admins group. Now Admin is only a member of the Users group and will have only those permissions assigned to that group by "DBOwner".

8. Create a password for "DBOwner".

9. Close and re-open Access, logging on as "DBOwner" using the password you created in step 8.

10. You can now start to secure the objects in you database.

Special Notes:

* A User account inherits the permissions of the Group to which it belongs.

* In Access 2000 and later, if you are not creating an .mde, you also need to secure your code by using Password Protection in the VBA Editor.