Challenges upgrading to 64-bit version of MS Office

The volume of data (sets) within organisations is growing constantly. Most organisations already use 64-bit computers/laptops as a result.
Before long they will be forced to upgrade from 32-bit to 64-bit versions of Office as well.
However, migrating Office-applications to 64-bit can pose serious challenges if VBA is applied.

Issues with controls in MSComCtl and MSComCt2 aside, most problems will arise from the VBA Application Programming Interface (API).
API Declare statements will have to be altered as well as the used ByRef variables.
The reason is that existing API Declare statements will not compile in 64-bit VBA without using the PtrSafe attribute. Extensive rework of the VBA codebase could be the result.
This is especially true if the application needs to run on both architectures (32-bit and 64-bit). In that case additional coding will have to implement conditional compilation.

My applications are already 64-bit proof but I’ve seen a lot of applications that aren’t. Especially large (traditional) organisations use dated but crucial Office-tools that often contain separate code modules with API Declares (used for File Open/Save-dialogs for instance).

Are organisations aware of this impending challenge? What are your experiences in this regard? What tools does Microsoft provide to accomodate this type of migration?
Microsoft only seems to refer to this website of a fellow Dutch VBA developer.
Surely I am not the only one who has experience making VBA code run on 64bit Office?

Connexa XS: Revision 6 is here!

The release of Connexa 2.49 rev. 4 last month was a big success. Thank you for all the positive feedback!
Some of the feedback came in the form of desired functionality. We listened. In revision 6 it is now possible to:

  • Sort data in a table sheet (finally!)
  • Retain customized column widths after save/refresh
  • Validate data as it is copied into a table sheet
  • Show the last custom SQL statement in the SQL input form

The sample database also got overhauled for you reviewing pleasure ūüôā

As always: tell us what you think!

ButtonSortData

QueriesDropDown

 

Development of Connexa SS kicked off

With the Connexa XS Excel add-in you can explore and edit data in a MS Access database. We increasingly notice that customers want to write back changes to MS SQL Server (Express).  To meet this need we decided to develop a specific version for this type of database.

Connexa SS will enable two-way communication between Excel and MS SQL Server (Express). The add-in will use the same interface technology as Connexa XS. Thus the look & feel of this new product will be very similar to Connexa XS. There will be differences as well of course; the way the connection is se up is different obviously. And in addition to exploring tables and queries / stored procedures you’ll also be able to execute SQL Server views from the add-in ribbon.

Write an email to info@redcirclesoftware.com if you’d like to participate in the beta-testing programme of Connexa SS. When testing is over you’d be entitled to a registered version of the final release!

Connexa SS Preview

 

Connexion XS: revision 4 is now available

Last spring we released a major version update¬† of Connexa XS. Since then we’ve been collecting feedback from our clients regarding bugs and desired functionality.

After three minor bugfix releases we are now proud to announce version 2.49 revision 4. Most notable changes:

  • Enhanced performance while writing back changes to the database
  • Prevention of cell drag and cut/paste in a table sheet
  • Cell formatting capabilities in a table sheet (which will allow you to filter on color!)
  • Availability of Undo functionality in a table sheet
  • Implementation of various bugfixes and checks

We‚Äôre sure you will love this version. But don’t take our word for it. Give it a go yourself!

Why you should alway use .Cells when calling a range object

A sneaky little cause of possible bugs is (inconsistent use of) the .Cells-property when calling Range-objects. This opening sentence should have scared of all the non-geeks :).

Let me explain why. Consider next code example:

Sub TestCells()

    Dim rMyRange As Range
    Dim rCell As Range
    
    '1. Initialize range
    Set rMyRange = Worksheets(1).Range("A1:B3")
    '2. Set range to first column only
    Set rMyRange = Intersect(rMyRange, rMyRange.Columns(1))
    
    '3. Display address
    Debug.Print rMyRange.Address
    
    'Iterate through the cells of the column
    For Each rCell In rMyRange
        '4. Display individual cell addresses
        Debug.Print rCell.Address
    Next

End Sub

The example is simple enough: reset a range to its first column and display the adresses of the individual cells.
When you run this example this is the obvious output:

$A$1:$A$3
$A$1
$A$2
$A$3

Looks can be deceiving though. Some of you may have already noticed the cumbersome way of determining the first column part of the range.
Why not simply do it like this?:

    '2. Set range to first column only
    Set rMyRange = rMyRange.Columns(1)

Ok smarty pants, predict the output after you changed that line of code and run the example again. Same output? Not so much:

$A$1:$A$3
$A$1:$A$3

How can this be? For reasons beyond me the default property of a returned range is sometimes different according how you went about getting it.

The function Application.Intersect returns a collection of individual cells as expected; as a default a defined ranges and its¬† .Cells-property are identical; just¬†comment out the line ‘Set rMyRange = rMyRange.Columns(1)’ and run the example. This is the expected output:

$A$1:$B$3
$A$1
$B$1
$A$2
$B$2
$A$3
$B$3

Apparently range-properties like Range.Columns and Range.Rows (and god knows what else) play by different rules; Range and Range.Cells no longer point at the some thing.
This is all the more confusing because the adresses of the different range-results are the same.

I ran into this issue when I was refactoring some code in Connexa and wanted to get rid of redundant Intersect-calls. The only way to tackle this inconsistency is to always call the .Cells collection of the range after you set it:

For Each rCell In rMyRange.Cells
    '4. Display individual cell addresses
    Debug.Print rCell.Address
Next

Live and learn!

 

New version Connexa XS released!

As of 23 april 2015¬†a brand new version of Connexa XS¬† is available for download. Apart from numerous ‘under-the-hood’ improvements and some minor bug fixing these are some of the notable changes:

  • Introduction of custom context menu in the table tab
  • Changes regarding on-the-fly SQL execution
  • Enhancement of multi-row edit operations
  • Much needed style & layout overhaul
  • Improved 64-bit (API) support

Download a trial version and tell us what you think. We’d love to hear from you!

Screenshots (click to enlarge)

ContextMenu

InputSQL

ButtonAddRecords

Support for Connexa XS Classic has ended.

As of Januari 1, 2015 there will be no more updates or technical support for Connexa XS Classic. Since April 2014 Microsoft seized support for Office 2003 although most users already moved on from that version.

Connexa XS Classic will still be available for legacy purposes.

Version11Teaser

Activate a Connexa add-in manually

Sometimes a Connexa add-in is not correctly installed or loaded; the Connexa tab is not present after Excel is opened.
It occurs when antivirus software or security policies prevented you from installing the setup.exe correctly or when MS Office was installed using Click-to-Run.
Follow the next few steps to activate the add-in manually:

– Open Excel
– Select¬†the File tab in the ribbon and select ‘Options’:

– In Excel Options select Add-ins
– Make sure ‘Excel Add-ins’ is displayed in the drop-down and click button ‘Go’:

– In the Add-ins screen click button ‘Browse..’:

– In the Browse for file dialog navigate to the folder where the add-in is located (default location is: C:\Program Files [x86]\Connexa XS [Trial])
– Select the add-in file Connexa.xlam.
– Click ‘OK’ to exit the dialog.
– Click ‘OK’ in the Add-ins screen to activate the Connexa add-in:

– The add-in is loaded. Prove of which is the new Connexa tab and the following message:

InstallAddin-5

Handling MS Access form errors generically

As a database owner you will not want users to edit data in MS Access tables directly. It’s one of the reasons forms exist; some of the obvious advantages are:

  • Specific fields can be locked or hidden
  • The field order can be changed
  • Event-handling can be implemented

However, errors in forms can occur due to inadequate data handling. If this happens the messages displayed are often not very user-friendly. For a common user following message looks quite intimidating:

“The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate values and try again.”

This is especially true for forms with a datasheet view since they display multiple records.

To address this issue I’ve created a generic solution to handle data-related form errors. It consists of two components:

1. Generic code

The routine HandleFormError is called from the indivual Form_Error events. Therefore insert following code it in a vba module:

'Constants
Public Const APP = "My application"
Public Const ERR_ONETOMANYCONFLICT = 3101
Private Const ERR_RELATEDRECORDS1 = 3200
Private Const ERR_RELATEDRECORDS2 = 3201
Private Const ERR_REQUIREDDATA = 3314
Private Const ERR_DUPLICATEKEY = 3022
Private Const ERR_DATATYPE = 2113
Private Const ERR_INPUTMASK = 2279
Private Const ERR_NULLKEY = 3058
Private Const ERR_NULLVALUE = 3162
Private Const ERR_ZEROLENGTHSTRING = 3315
Private Const ERR_DATAVALIDATION1 = 2107
Private Const ERR_DATAVALIDATION2 = 3317
Private Const ERR_ITEMNOTINLIST = 2237

' Handle data-related form errors by showing user-friendly messages.
' Assign the appropiate return value to the Response parameter.
Public Sub HandleFormError(oForm As Form, _
        DataErr As Integer, ByRef Response As Integer)

    Const NEXT_MSG = "See next message for details."

    Select Case DataErr

        Case ERR_REQUIREDDATA
            MsgBox "Required value missing!" & vbCr & _
                   NEXT_MSG, vbInformation, APP
            Response = acDataErrDisplay

        Case ERR_RELATEDRECORDS1, ERR_RELATEDRECORDS2
            MsgBox "Relation conflict!" & vbCr & _
                   NEXT_MSG, vbInformation, APP
            Response = acDataErrDisplay

        Case ERR_NULLKEY, ERR_NULLVALUE, ERR_ZEROLENGTHSTRING
            MsgBox "Field can not be empty.", vbInformation, APP
            Response = acDataErrContinue

        Case ERR_DUPLICATEKEY
            MsgBox "The value is already in use to " & _
                   "create an unique record. Change the value.", _
                    vbInformation, APP
            Response = acDataErrContinue

        Case ERR_DATATYPE, ERR_INPUTMASK
            MsgBox "The value has an incorrect data type" & vbCr & _
                   "(f.i. text value in a numeric field).", _
                   vbInformation, APP
            Response = acDataErrContinue

        Case ERR_ITEMNOTINLIST
            MsgBox "Select an item from the list.", vbInformation, APP
            Response = acDataErrContinue

        Case ERR_DATAVALIDATION1, ERR_DATAVALIDATION2
            'Custom validation rules: show corresponding error message
             MsgBox AccessError(DataErr), vbInformation, APP
             Response = acDataErrContinue

        Case Else
            'Display the default error message
            Response = acDataErrDisplay

    End Select

End Sub

Regarding the errors ERR_REQUIREDDATA, ERR_RELATEDRECORDS1 and ERR_RELATEDRECORDS2 a work-around was needed due to a MS-bug. Ideally we would like to show the error messages with the field specific information using our own messagebox formatting (just like the errors ERR_DATAVALIDATION1 and ERR_DATAVALIDATION2). However, in that case the original message is not shown correctly. Therefore the default message is preceeded with our own. An alternative could be to comment out the cases regarding ERR_REQUIREDDATA, ERR_RELATEDRECORDS1 and ERR_RELATEDRECORDS2, so that only the default error message is shown.

2. Event-code per form

Per individual form implement next event:

' Data-related error is triggered in the form
Private Sub Form_Error(DataErr As Integer, Response As Integer)

    'Check for one-to-many relation conflict
    If DataErr = ERR_ONETOMANYCONFLICT Then
        'User entered data on the "many" side for which
        'there is no matching record on the "one" side.
        '[TODO:] Specify [field] in message
         MsgBox "First select a(n) [field]", vbInformation, APP
        Response = acDataErrContinue
    Else
        'Other data error; go to generic form error handling
        Call HandleFormError(Me, DataErr, Response)
    End If

End Sub

In this way I’ve been able to prevent a lot of¬†cumbersome coding.¬† I hope it will benefit you as much as it did me. Feel free to use this code and alter the messages to your own liking.

 

Deep dive: Opening an encrypted Access file via VBA based on the shortcut to the workgroup information file

 

Connexa XS add-ins enable you to select and alter Access databases from Excel.
Some databases, however, have been encrypted using user-level security; a workgroup information file handles the security regarding database objects and a workgroup file shortcut is needed to allow individual users to open the encrypted database with the right permissions.
We wanted users to be able to connect to these secured databases from Excel as well. This second article in our Deep Dive series explains how we did this.
The easiest way to open a secured Access database is to let the user select the link to the workgroup file (to be able to select a .lnk file in an Open File-dialog is another issue which will not be discussed in this article). The user-specific arguments stored in the shortcut target path are subsequently used to automatically open the encrypted Access file.

Typically the target path of a workgroup file shortcut looks like this:

“%ProgramFiles%\Microsoft Office\OFFICE[version]\MSACCESS.EXE” “\[path]\MyDb.mdb” /WRKGRP “\[path]\MyWorkgroup.mdw” /USER MyUser /PWD MyPassword

Often the last two arguments (username and password) are omitted in which case the user will have to enter his/her credentials manually when opening the database.

Underneath code excerpt demonstrates in a simplified manner how we accomplished this. In order to access the shortcut target you’ll need Wscript to instantiate a shell object. The shell creates a temporary link object based on the selected shortcut. The property TargetPath of the link object contains the string value with the desired argument information.
Finally the arguments are determined through string manipulation of the TargetPath .

Option Explicit
'Global constants
Public Const ERR_ABORT = 666

'Members
Private msWorkgroup As String
Private msUser As String
Private msPassword As String

'Test routine DetermineShortcutArgs
Sub TestMdwLinkArguments()

    Dim sLinkFile As String
    Dim sDbFile As String

    On Error GoTo ErrH

    'Determine the path to the mdw-shortcut
    sLinkFile = "[path]\MyShortcut.lnk"

    'Call the routine with the proper argument
    sDbFile = DetermineShortcutArgs(sLinkFile)

    'Display the argument values in the immediate window (Ctrl+G)
    Debug.Print "Database:", sDbFile
    Debug.Print "Workgroup:", msWorkgroup
    Debug.Print "User:", msUser
    Debug.Print "Password:", msPassword

    Exit Sub
ErrH:
    Select Case Err.Number

        Case ERR_ABORT
            MsgBox Err.Description, vbExclamation
        Case Else
            MsgBox "An unexpected error occurred:" & vbCr & _
                Err.Description, vbCritical
    End Select
End Sub

' Get the properties stored in the shortcut to the workgroup file
Private Function DetermineShortcutArgs(sFilename As String) As String

    Dim oShell As Object
    Dim oLink As Object
    Dim sarrArgs() As String
    Dim sArg As String
    Dim iPos As Integer
    Dim i As Integer

    On Error GoTo ErrH

    'Instantiate shell object
    Set oShell = CreateObject("WScript.Shell")

    'Create a new temporary shortcut to get the arguments
    Set oLink = oShell.CreateShortcut(sFilename)

    'Check validity of the link.
    If InStr(1, oLink.TargetPath, "MSACCESS.EXE", vbTextCompare) = 0 Then
        Err.Raise ERR_ABORT, , "The workgroup link is invalid."
    End If

    'Split the arguments into a string array
    sarrArgs = Split(oLink.Arguments, "/")

    'Loop through the array
    For i = LBound(sarrArgs) To UBound(sarrArgs)

        'First argument contains the target database
        If i = 0 Then
            DetermineShortcutArgs = _
                    Trim$(Replace(sarrArgs(i), Chr(34), vbNullString))
        Else
            'Manipulate the string to extract the property name
            iPos = InStr(1, sarrArgs(i), " ")
            sArg = LCase$(Left(sarrArgs(i), iPos - 1))

            'Determine the property value based on the property name
            Select Case sArg

                Case "wrkgrp"
                    msWorkgroup = GetArgValue(iPos, sarrArgs(i))
                Case "user"
                    msUser = GetArgValue(iPos, sarrArgs(i))
                Case "pwd"
                    msPassword = GetArgValue(iPos, sarrArgs(i))

            End Select
        End If
    Next

ErrH:
    'Clean up
    Set oLink = Nothing
    Set oShell = Nothing
    If Err.Number <> 0 Then
        Err.Raise Err.Number, Err.Source, Err.Description
    End If
End Function

'Distill the value part from the argument and return it
Private Function GetArgValue(iPos As Integer, sArgument As String) _
                                                            As String
    GetArgValue = _
    Trim$(Replace$(Mid(sArgument, iPos + 1), Chr(34), vbNullString))
End Function

User-level security is not supported anymore by the new file formats in MS Office Access 2007/2010. However it still works for .mdb files, even in the latest Office versions. Since no easy alternative exists (except elaborate programming) expectations are that workgroup security will be around for a while. Thus the code presented here will hopefully be of some use to someone!