Wednesday, May 29, 2013

Replacing Access 2010’s TAPI AutoDialer with a No-Frills Dialer using the Hayes ATDT Command


Adding an AutoDialer button with the Command Button Wizard creates an embedded macro that runs Windows’ built-in Dialer.exe, a Telephone API (TAPI) application that reads configuration data and dialing rules from Control Panel’s Phone and Modem tool. By default, the macro transfers the phone number from the preceding control in the tab order to a text box. Clicking the button opens a dialog that lets the user confirm (and, optionally, edit the number), and then click OK to dial or Cancel to abort it:



imageClicking OK also activates an AutoDialer icon in the taskbar:

Clicking the taskbar icon opens the following two dialogs anchored ot the top left of the screen:


Most developers and their clients will consider these two dialogs superfluous and confusing to end users. What’s worse, there appears to be no readily apparent method for preventing the Phone Dialer dialog from appearing when the user just wants to hang up. Users expect to “hang up” simply by placing the handset on hook.

Extensive searching with Bing and Google disclosed no free, turnkey code for creating a simple autodialer by executing the Hayes ,,ATDT+1###-###-####;H command to dial the number and hang up.

Creating the No-Frills Dialer with David M. Hitchner's modCOMM Library

Access 2010’s VBA 7 doesn’t have a feature that emulates VB 6’s MSComm control, but David M. Hitchner has thoughtfully posted a VB module containing a collection of routines to perform serial port I/O without using the Microsoft Comm Control component here. His file contains a CommIO.bas and SampleCode.txt files. Rename CommIO.bas to CommIO.txt, open it in Notepad and paste it into a new module.

Important: Be sure to add PtrSafe to CommIO’s Declare statement if you’re running 64-bit Access or either 32-bit or 64-bit Access under Windows 8 or Server 2012, as in:

Declare PtrSafe Function GetCommModemStatus Lib "kernel32" _
    (ByVal hFile As Long, lpModemStat As Long) As Long

Here’s my code for a procedure that accepts arguments for the form name, control name of the text box with the phone number, and name of the dialed party, opens this message box:

imageand sends the COM port number (2) and ATDT command text to a subprocedure based on David’s SampleCode.txt example:

Public Sub AutoDial(strForm As String, strControl As String, strParty As String)
    ' Dial with Hayes-compatible command set
    On Error GoTo ErrorHandler
    Dim Phone As Control
    Dim Party As Control
    Dim strNumber As String
    Dim strDialCmd As String
    Dim strMsg As String
    Dim varRetVal As Variant
    Set Phone = Forms(strForm).Controls(strControl)
    Set Party = Forms(strForm).Controls(strParty)
    varRetVal = Application.SysCmd(acSysCmdClearStatus)
    If Len(Phone.Value) = 12 Then
        If Left(Phone.Value, 3) = "510" Then
            ' Local call
            strNumber = Mid(Phone.Value, 5)
            ' 1-AAA-PPP-NNN
            strNumber = "1-" & Phone.Value
        End If
        strMsg = "Click OK to dial " & strNumber
        If Len(Party.Value) > 0 Then
            strMsg = strMsg & " for " & Party.Value
        End If
        strMsg = strMsg & " and pick up handset within 10 seconds."
        strMsg = strMsg & vbCrLf & vbCrLf & "Or click Cancel to abort the call."
        strDialCmd = ",,ATDT" & strNumber & ";H"
        varRetVal = Application.SysCmd(acSysCmdSetStatus, "Dialing " & strDialCmd)
        If MsgBox(strMsg, vbOKCancel + vbQuestion, "Pacific Park Plaza Autodialer") = vbOK Then
            ModemDialer 2, strDialCmd & vbCrLf
        End If
        MsgBox "Phone number not valid.", vbOKOnly + vbExclamation, "Autodialer Error"
    End If
    Set Phone = Nothing

    Exit Sub
   MsgBox Err.Description, vbExclamation, "Unexpected Autodialer Error"
   Resume ExitPoint
End Sub

You must specify the modem’s COM port to correspond with Control Panel’s Phone and Modem tool in the first argument of the above ModemDialer 2, strDialCmd & vbCrLf instruction:


Here’s the modified procedure for invoking CommIO functions:

Sub ModemDialer(intPortID As Integer, strData As String)
    ' Truncated version of David M. Hitchner's SampleCode.txt from
    ' Requires installation of David M. Hitchner's modCOMM library downloaded from
    On Error GoTo ErrorHandler
    ' intPortID = 1, 2, 3, 4 for COM1 - COM4
    ' strData = Phone number with area code removed for local numbers and
    ' +1 prepended for out-of-area numbers
    Dim lngStatus As Long
    Dim lngSize As Long
    Dim strError  As String
    Dim varRetVal As Variant

    ' Initialize Communications
    lngStatus = CommOpen(intPortID, "COM" & CStr(intPortID), _
        "baud=9600 parity=N data=8 stop=1")
    ' Write data to serial port.
    lngSize = Len(strData)
    lngStatus = CommWrite(intPortID, strData)
    If lngStatus = lngSize Then
        varRetVal = Application.SysCmd(acSysCmdSetStatus, strData)
        varRetVal = Application.SysCmd(acSysCmdSetStatus, "Error: " & strData)
    End If
    ' 10 seconds to pick up phone; see procedure below
    Pause 10
    varRetVal = Application.SysCmd(acSysCmdClearStatus)
    Call CommClose(intPortID)
    Exit Sub

    MsgBox Err.Description, vbInformation + vbOKOnly, "Error Opening COM Port"
    Resume ExitPoint
End Sub

Saturday, April 13, 2013

Received Author Copies of Chinese Translation of Access 2010 In Depth

On April 12, 2013, I received two copies of the Chinese translation of Access 2010 In Depth from QUE Publishing:


Here are the details from the Chinese publisher’s Web site:


And Microsoft Translator’s version (with related book titles removed):



I especially like the [Beauty] prefix, but it’s undeserved. Sea freight from China is a bit slow, but I’m surprised it took the copies more than six months to arrive.

Sunday, March 17, 2013

Creating a Working DSNless ODBC Connection String with SQL Server Native Client v11.0 for SQL Server 2012

OakLeafLogoMVP100pxWhen completing a Microsoft Access 2010 Resident Information Management System (RIMS) project for the Home Owners Association of an East Bay high-rise condominium, the last step in preparing the runtime installation package was generating a DSNLess connection to SQL Server 2012 Express running under SQL Server 2012 Standard Edition. I used a System DSN with the SQL Native Client v11 driver during testing on my client’s new Active Directory network.

imageThe SQL Server chapters of my Special Edition Using Microsoft Access (since the Access 2005 edition) and Microsoft Access In Depth books for QUE Publishing have included VBA source code for a ChangeServer class module, which generates DSNLess connection strings from System Data Source Names (DSNs) for the classic SQL Server Driver (SQLSVR32.dll, v6.01.7601.17154 for SQL Server 2012). SQL Server Native Client (SQLNCLI11.dll, v2011.110.3000.0 for SQL Server 2012) is now the preferred ODBC driver for all client applications, including Microsoft Access.

imageI had customized the ChangeServer module for the SQL Native Client (SQLNCli) for applications using Access 2007 and 2010 front ends with SQL Server 2005, 2008 and 2008 R2. When I wrote code to change the SERVER clause of the connection string to DRIVER={SQL Native Client 11.0};, and ran the Client.accdr file with the new ODBC;DRIVER={SQL Native Client 11.0};SERVER=OL-WIN7PRO23\SQLEXPRESS;DATABASE=RIMS;TABLE=dbo.Owners; Trusted_Connection=YES connection string, I received the following malformed error message:


Previous SQL Server versions hadn’t objected to similar connection strings, so after unsuccessfully searching for the issue with Bing and Google, I started a How to Create a Working DSNless SQL Server 2012 Connection String? thread in the SQL Server Data Access forum.

Dan Guzman, SQL Server MVP, offered the following suggestion:


Dan was right, but changing the the connection string to ODBC;DRIVER={SQL Native Client 11.0};SERVER=OL-WIN7PRO23\SQLEXPRESS;DATABASE=RIMS; TABLE=dbo.Owners;Trusted_Connection=YES threw a “missing field” error, which indicated the table was missing a primary key field. (It wasn’t.)

It turns out that specifying the table name with a TABLE= clause no longer works. You must execute a tdfToAppend.SourceTableName = "dbo.TableName" instruction.

imageYou can download the updated ChangeServer class module from my SkyDrive account by clicking here.