How to detect, if you are running 64-bit Office

New data type LongPtr is Long integer on 32-bit systems and LongLong integer on 64-bit systems, by checking it VarType you can find out on what environments you run your VBA code:

Function Is64BitOffice() As Boolean
    Dim lp As LongPtr
    Is64BitOffice = Not (VarType(lp) = vbLong)
End Function

This trick I learned from fellow MVP Brent Spaulding

Navigation Pane in Access 2010

Get lost with Access Navigation pane? Here some great tips from Albert D. Kallal how to be even more productive with the navigation pane in Access 2010: Become friends with the nav pane in 2010

Also look at Microsoft Access Shortcut Keys for Data Entry and Navigation from FMS.

ImportXML method "Cannot establish connection to the server" error

My friend Peter, Access MVP, was recently puzzled by weird error, produced by simple line:

Application.ImportXML strPathFile, acStructureAndData

Runtime error was 31521 "Cannot establish connection to the server". There was no any network connection involved, strPathFile was as simple as "C:\Temp\Customer.xml". This xml file has been generated by:

Application.ExportXML acExportQuery, strQuery, _
strPathFile, , , , , acEmbedSchema

It turned out that Access Trust Center option "ActiveX Settings" was a source of such error, it was set to "Disable all controls without notification". Once it was changed to "Prompt me before enabling all controls …" – it works!

Thanks for sharing, Peter!

Migrating Access tables to the cloud

A great video by Albert Kallal Migrating Access tables to Office 365 steps through the process of moving Access tables to SharePoint site or Office 365 and enabling referential integrity for those tables.

Multi-value Fields - Appending / Updating with query

Access 2007 introduced Multi-value fields (MVF), some people like it, some – hate it. I personally like UI for MVF in Access, but don't like realization in table level – you can't get access to  junction table of MVF. Anyway - any Access developer have to learn working with it, sooner or later, and here something your can look at.

A.D. Tejpal, Access MVP, has published Multi-value Fields - Query Based Solution For Bulk Appending / Updating sample database, which shows several techniques to work with MVF.

Hotfix: Running query takes longer than expected

Access 2010 hotfix package (Ace-x-none.msp): August 30, 2011

Issue that this hotfix package fixes:

Assume that you run a query in Access 2010 on a computer that is running Windows 7 or Windows Server 2008 R2. In this situation, the taskbar displays "Running query" together with a process bar and it takes longer than expected to return the query results.
Note This issue does not occur if you run the query in Access 2010 on a computer that is running Windows XP.

Workaround for Access 2010 SP1 crash

How fix Access crash after installing SP1: Acc2010: You receive an error in Microsoft Access using the built-in wizards after installing Microsoft Office 2010 SP1

Problems reported on Office 2010 SP1

Office 2010 SP1 and Access 2010 Runtime SP1 are broken Cypris' lookout

Running 32 and 64-bit versions of Access Database Engine 2010 together

According to Tony’s post you have to add /passive switch and install 64-bit version from command line after you have 32-bit version running. And keep in mind, that side-by-side installations is unsupported scenario.

New Access Database template

I have already blogged about new database template in Access, but original post on team blog was gone (thanks to my reader motioning this!). Jeff Conrad has it in his Access 2007 FAQ. The idea is: if you want to have new database created with predefined settings – you can make a Blank.accdb database and put it in Program Files\Microsoft Office\Templates\1033\Access.

And one more link to Office online article: Create a custom blank template.

Same applies to Access 2010.

Creating ADE in Access 2010

This tip I got from Brent Spaulding, Access MVP.
With the default file format option set to "Access 2007" or "Access 2002 - 2003", you can create an ADP (it will be in the respective file format), then subsequently create a corresponding ADE. But if the default file format option is set to "Access 2000", you can create an ADP (it will be in A2000 ADP format), but a corresponding ADE can not be created.

How to export table’s rich text to Word

In Access 2007 and 2010 you can set Memo field property Text format to Rich Text and start entering formatted text there.

But if you export that table to Word RTF file – you lost all formatting. In order to keep formatting – make an export to HTML Document, and then you can open it in Word, and perhaps save as RTF.

Application for Access 2003 and 2007+: Substitute missing class in reference with custom MDE.

This trick I learned from Graham Mandeno, Access MVP. Here we talk about Ribbon interface, but same trick can be used for other libraries.

In Access 2007 command bars were replaced with Ribbon. You can develop application which can run on 2003 and 2007/2010, but if you build custom menu bar or command bar for Access 2003, when running in A2007+ the menu bar gets buried in the "Add-Ins" ribbon. So you would like to create a proper ribbon UI which would appear if the MDB/E is opened in A2007+, and still have the old-style CommandBar UI appear if opened in A2003.

You can create the ribbon for 2007/10, but the callbacks fail to compile in A2003 because class objects such as IRibbonControl do not exist in the Office11 object library. You can try to declare all instances "As Object" and that keeps A2003 happy, but then the ribbons don't work in A2007+. Let us give word to Graham:

What I have done is create a very small MDE containing nothing but class modules for IRibbonControl and IRibbonUI, each with the necessary properties and methods declared as empty procedures. The class modules need to have instancing set to “PublicNotCreatable”. Then I added a reference to the MDE to my project, ensuring that it is last in the list – BELOW the reference to MSO.DLL.

Now, when running on A2007+, the code picks up the reference from the Office 12/14 object library and works fine. On A2003 it finds it in the dummy MDE and compiles OK (of course, the actual code is never called in A2003).

In case anyone else is interested, here are the three class modules (I don’t actually use IRibbonExtensibility, but I included it for completeness):

' Class: IRibbonControl

Option Explicit

Public Property Get Context() As Object

End Property

Public Property Get Id() As String

End Property

Public Property Get Tag() As String

End Property

================================

' Class: IRibbonUI

Option Explicit

Sub Invalidate()

End Sub

Sub InvalidateControl(ControlID As String)

End Sub

================================

' Class: IRibbonExtensibility

Option Explicit

Public Function GetCustomUI(RibbonID As String) As String

End Function

================================

Access 2010: Interactive menu to ribbon guide

The most popular post in this blog is Compact database in Access 2007, people really get lost in Ribbon. Access 2010 has better organized Ribbon, but still - transition from Access 2003 to 2010 is not so easy due to the Ribbon. Microsoft just posted new program to help new Access 2010 users - Interactive menu to ribbon guide. Look how it shows where Compact database menu item in Access 2010:

Fortunately in Access 2010 Compact and Repair Database item was moved in more intuitive place:

A Soundex Search for Access Web services

A great article by Albert D. Kallal, Access MVP, shows how you can port VBA code to data macros to be used in Access web services.

Access to SQL Azure

Latest SSMA for Access now supports data and schema migration to SQL Azure.

Office 2010 Tool: Compatibility Inspector

The Compatibility Inspector compares existing (legacy) code against the Microsoft Office 2010 object model to identify possible code issues. Works in VBA IDE and Visual Studio 2008.

New Free Ribbon and Access 2010 Navigation Form Tools

from Anthony D'Ambra

1. Access2007 Ribbon Builder and Editor for Microsoft© Access 2007 BETA builds and maintains Access 2007 and Access 2010 Custom Ribbons. It is a simple yet sophisticated tool for building custom ribbonbars using XML files. The ribbon being built is displayed as the active RibbonBar. You use the treeview representation of the ribbon to navigate and build the ribbon by adding, editing, copying, pasting, and deleting controls. http://accessextra.blogspot.com/2010/03/new-ezy-access-ribbonx-builder-editor.html

2. Easily Migrate Legacy Access Switchboards to the Access 2010 Navigation Form. In Access 2010 the venerable Native Switchboard Manager has been retired. The old Switchboard form has been replaced by a cool web 2.0 style Navigation Form using a tabs paradigm of up to two levels. The Navigation Form Control and Button controls cannot be created in code, so there is a degree of hard-coding involved. I saw the opportunity to ease the design process by adapting the legacy Switchboard Items table and porting it to the new Navigation Form. I have created a demonstration Access 2010, which can be downloaded from the url below. The code can be used freely in your applications 'as is'. All the required code is behind the custom Navigation Form, which is pre-built with a generic two-level tabs control: 8 level one options and each option having 8 buttons, which are populated at run-time from the Switchboard Items table, using custom code adapted from the Access 2003 vba module behind the native Switchboard form. http://accessextra.blogspot.com/2010/04/in-access-2010-venerable-native.html

3. The Access Switchboard to Ribbon Converter Wizard in just two mouse-clicks turns your legacy Access Switchboard (as built by the native Access Switchboard Manager) into a fully functional Ribbon Switchboard. http://accessextra.blogspot.com/2010/05/access-switchboard-to-ribbon-converter.html