KeyedAccess Goes to 11

11 years that is. 11 years of providing copy protection to Access applications and helping developers turn their applications into income. Thank you to all of you for making KeyedAccess one of our most popular products.

And don't forget, KeyedAccess supports all 64-bit versions of Access when you apply the 64-bit patch that is available from Peter's Software for free for registered users. Just ask us for it by e-mail.

Starting an Access Application in a Multi-User Environment

I use Application Starter for all of my installed Access applications at client sites. It cuts technical support calls down to a minimum since Application Starter compacts the back-end database file each day before the application is opened. It also makes it simple to distribute a front-end to any number of different client computers.

The new version includes the ability to create generational backups prior to opening the database.

It's really a must-have for me and I don't know why every serious Access developer is not using it! And it's free - http://www.peterssoftware.com/aps.htm

Introducing Halp

Need to integrate help content into your Access application? We have a new product called "Halp" that can... help.

Halp is an integrated help authoring and display tool for Microsoft Access applications. More information is at http://peterssoftware.com/hlp.htm

Normalization and Cascading Combo Boxes

I hope I helped out this developer over at Yahoo Answers. His question was about cascading combo boxes, but he had yet to normalize his database. Here's the link: http://goo.gl/ShxbB

"The width of a Unicode text column must be an even number of bytes."

Just received the above error message in Access 2010 when clicking on a checkbox. There's a description of the error here,  which conveniently says

If this error message appears during normal run time, it is probable that the database has become corrupted and needs to be compacted or repaired.

Yup. It's a corruption issue. But compacting and repairing the back-end database did no good. The corruption was apparently in the front-end database file. After I compacted and repaired the front-end, the problem went away.

The Amazing Eval Function

The Eval function in VBA allows you to execute a string as if it were a line of code. There are some examples in Access VBA help that are marginally useful, but what I like to use Eval for is executing optional functions. Normally, if you call a function that doesn't exist, you'll get a compile error and your code won't run. But the Eval function is one place where you can call a function that may or may not exist, and trap the error condition that occurs when the function does not exist. Here's an example:


Dim strRtn As String
Dim strFunctionNameAndParms As String

strFunctionNameAndParms = "Date()"    '<== WE'RE GOING TO CALL THIS FUNCTION

On Error Resume Next
strRtn = Eval(strFunctionNameAndParms)
If Err <> 0 Then
    Select Case Err
    Case 2425, 2426
        '* The function is not found
        MsgBox "The function '" & strFunctionNameAndParms & "' was not found."
    Case Else
        '* There was an error calling the function
        MsgBox "Error when calling '" & strFunctionNameAndParms & "': " & Err.Number & " - " & Err.Description
    End Select
    Err.Clear
Else
    MsgBox "Return value from '" & strFunctionNameAndParms & "' is: " & vbCrLf & strRtn
End If
On Error GoTo 0

Now, if the function above ("Date()", in this instance) doesn't exist, there's no compile error, the code keeps right on executing.

You could use this if you, for instance, wanted to optionally include an "Accounts Receivable" module in your   accounting application. The main menu item for AR could use Eval to call the "StartAR()" function that lives in a "basAccountsReceivable" module that you can optionally include in your database. If the module is there, then the function runs and Accounts Receivable opens. If the module is not there, then you could have a message appear... something like "The Accounts Receivable module has not been included in this application build. To purchase this additional module please contact ..."

We Accept Bitcoins

We've started accepting payments in Bitcoins at our site. If you haven't heard of Bitcoin, it is a new digital currency that uses encryption to keep transactions safe over a distributed network. You can covert some money to Bitcoins, then use them as currency where they are accepted. There are some advantages and disadvantages to using Bitcoins, and lots of controversy including some very interesting discussions of whether Bitcoins qualify as actual "money" or not:

Bitcoins Real Money or Bogus?

But one of the amazing things about Bitcoin for me is how secure payments are. You don't need to provide your name, address, phone, card number, security code, etc... as you do when you pay with a credit card. Bitcoins are designed to be used over the internet. You pay, and your "wallet" cryptic keys are identified by the distributed network and your account is credited or debited, and the recipient's account is credited or debited. No worries about someone stealing your credit card number or your identity. You just have to make sure your "wallet.dat" file doesn't get hacked, and you can do that with encryption or by some other similar means.

Is it the future of money? We'll see. For now it's a nice secure alternative to credit card payments.

GetKeyState

You can use the GetKeyState API to find out if the user is holding down a particular key. http://msdn.microsoft.com/en-us/library/windows/desktop/ms646301(v=vs.85).aspx I've used this function to take me directly into the VBA code behind a command button while a form is in form view. This is very hand for debugging, but takes some setup. I check for the Ctl-Alt key combination in the OnMouseUp event property of the command button. My OnMouseUp event property looks like this:

=xg_CtrlAltMouseUp()

In the declarations section of a standard module I put:

Const VK_LSHIFT = &HA0
Const VK_RSHIFT = &HA1
Const VK_LCONTROL = &HA2
Const VK_RCONTROL = &HA3
Const VK_LMENU = &HA4
Const VK_RMENU = &HA5
 
#If vba7 Then
    Declare PtrSafe Function GetKeyState Lib "user32" Alias "GetKeyState" (ByVal nVirtKey As Long) As Integer
#Else
    Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#End If

And the function looks like this:

Function xg_CtrlAltMouseUp() As Integer
'******************************************************************************
'* Peter De Baets, author
'* 11/2/2012
'*
'******************************************************************************
Dim Marker As Integer
Dim Rtn As Integer

On Error GoTo Err_Section
Marker = 1

'Return values
'0 if the key is neither down nor toggled,
'-127 if the key is down but not toggled,
'1 if the key is toggled but up, and
'-128 if the key is both toggled and down.
If GetKeyState(VK_LCONTROL) < 0 And GetKeyState(VK_LMENU) < 0 Then
    DoCmd.OpenModule "Form_" & Screen.ActiveForm.Name, _
        Screen.ActiveControl.Name & "_Click"

Else
End If

Exit_Section:
    On Error Resume Next
    On Error GoTo 0
    Exit Function
Err_Section:
    Select Case Err
    Case Else
        Beep
        MsgBox "Error in xg_CtrlAltMouseUp (" & Marker & "), _
        object " & Err.Source & ": " & Err.Number & _
        " - " & Err.Description
    End Select
    Err.Clear
    Resume Exit_Section
End Function

Access 2010: Replacing 32-bit API declarations with 64-bit

Here's a site which lists all the 64-bit API declarations for use in your Access 2010 64-bit application: http://www.excelfin.ru/attachments/article/163/ptrsafe_declarations.txt

Introduction to Access Programming

I get a lot of questions from people who are new to Access development and have never programmed before. Here's an introduction to the subject:

Introduction to Access Programming

(Disclaimer: Please realize that getting involved in programming can be life-changing - not necessarily for the better!)

Map Network Drives with VBScript

When setting up that Access database on a local area network, you'll likely need to map network drives on each end user's PC. This means you'll be creating a psuedo-drive "Z:" or whatnot, which opens a folder on the server where your Access application back-end resides.

As new computers are added to the network, you'll need to map the network drives on the new computers. This process can be eased by running a VBScript to automatically do the drive mapping. Below is an example script that I use to do this. You can modify the "Actions" to add or remove mapped network drives, in case you have multiple drives mapped, or need to do some mapping clean up.

Just save this code in a file with a .vbs extension, modify it for your needs, then double-click it from Windows Explorer to run it.

' Map network drives 

' Usage
'    Set the size of the ADrive, ARemoteShare, and AAction arrays to
'    be the number of drive mapping actions you want to take. Modify
'    the "Actions" in the "Fill Actions Array" section below, save, 
'    then double-click on this file from Windows Explorer to run it.
'
' "Actions" can be "Remove" (removes/disconnects a mapped network 
' drive), or "Add" (adds a mapped network drive, replacing whatever 
' mapping exists for the same drive letter).
'
' For multiple "Actions", modify the array sizes below, then fill
' the array entries as shown in the "Fill Actions Array" section below.
'
' This script will remove any existing drive map to the same drive letter
' including persistent or remembered connections (Q303209)
'
' from: http://ss64.com/vb/syntax-drivemap.html
' modified by Peter De Baets 12/28/2011

Option Explicit
Dim objNetwork, objDrives, objReg, i, objDrive, fileSys
Dim strReplaceDrive, strLocalDrive, strRemoteShare, strShareConnected, strMessage
Dim bolFoundExisting, bolFoundRemembered
Const HKCU = &H80000001
Dim J
'******************************************
'* Set the size of these arrays to the number
'* of drive mapping actions you want to take.
'*
Dim ADrive(2)
Dim ARemoteShare(2) 
Dim AAction(2)
'*
'****************************************** 


'******************************************
'* Fill Actions Array 
'* (make your changes here)
'*
' Example
'ADrive(1) = "X:"
'ARemoteShare(1) = "\\Servername\Share"
'AAction(1) = "Add"

' Action #1
ADrive(1) = "X:"
ARemoteShare(1) = "\\MyServer\MyShare"
AAction(1) = "Remove"

' Action #2
ADrive(2) = "Z:"
ARemoteShare(2) = "\\MyServer\MyShare"
AAction(2) = "Add"
'*
'******************************************


Set filesys = CreateObject("Scripting.FileSystemObject") 
for j = 1 to ubound(AAction)
  ' Check parameters passed make sense
  If Right(ADrive(j), 1) <> ":" OR Left(ARemoteShare(j), 2) <> "\\" Then
    wscript.echo "INvalid Action #" & j & " //NoLogo"
    WScript.Quit(1)
  End If
  if AAction(j) = "Add" then
    wscript.echo " - Mapping: " + ADrive(j) + " to " + ARemoteShare(j)
  Else
    wscript.echo " - Disconnecting: " + ADrive(j) + " from " + ARemoteShare(j)
  End If
  Set objNetwork = WScript.CreateObject("WScript.Network")
  ' Loop through the network drive connections and disconnect any that match strLocalDrive
  bolFoundExisting = False
  Set objDrives = objNetwork.EnumNetworkDrives
  If objDrives.Count > 0 Then
    For i = 0 To objDrives.Count-1 Step 2
      If objDrives.Item(i) = ADrive(j) Then
     if AAction(j) = "Remove" Then
    set objDrive = fileSys.GetDrive(objDrives.Item(i))
       if objDrive.ShareName = ARemoteShare(j) then
            strShareConnected = objDrives.Item(i+1)
            objNetwork.RemoveNetworkDrive ADrive(j), True, True
            i=objDrives.Count-1
            bolFoundExisting = True
    Else
      wscript.echo " - Drive " + ADrive(j) + " connected to " + ARemoteShare(j) + " not found. Continuing..."
    End if
  Else
       'wscript.echo "  sharename=" + objDrive.ShareName
          strShareConnected = objDrives.Item(i+1)
          objNetwork.RemoveNetworkDrive ADrive(j), True, True
          i=objDrives.Count-1
          bolFoundExisting = True
  End if
      End If
    Next
  End If

  ' If there's a remembered location (persistent mapping) delete the associated HKCU registry key
  If bolFoundExisting <> True Then
    Set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
    objReg.GetStringValue HKCU, "Network\" & Left(ADrive(j), 1), "RemotePath", strShareConnected
    If strShareConnected <> "" Then
      objReg.DeleteKey HKCU, "Network\" & Left(ADrive(j), 1)
      Set objReg = Nothing
      bolFoundRemembered = True
    End If
  End If

  if AAction(j) = "Add" then
    'Now actually do the drive map (persistent)
    Err.Clear
    On Error Resume Next
    objNetwork.MapNetworkDrive ADrive(j), ARemoteShare(j), True
  End If
Next 

'Error traps
If Err <> 0 Then
  Select Case Err.Number
    Case -2147023694
      'Persistent connection so try a second time
      On Error Goto 0
      objNetwork.RemoveNetworkDrive ADrive(j), True, True
      objNetwork.MapNetworkDrive ADrive(j), ARemoteShare(j), True
      WScript.Echo "Second attempt to " & AAction(j) & " map drive " & ADrive(j) & " to/from " & ARemoteShare(j)
    Case Else
      On Error GoTo 0
      WScript.Echo " - ERROR: Failed to " & AAction(j) & " map drive " & ADrive(j) & " to/from " & ARemoteShare(j)
  End Select
  Err.Clear
End If

Set objNetwork = Nothing
wscript.echo "Done. "

How to Set Trusted Locations for a Runtime Application

A user at AccessForums.net asked how to set up trusted locations when the end user doesn't have Access installed. You need to have a bit of a comfort level with regedit, then it is easy:

http://www.accessforums.net/security/how-set-macro-security-distributed-app-17714.html#post81250