How to ‘Hybridize’ your MS Access Database In Office 365 Azure Database

Maybe one of the most useful articles you'll ever read about Access and Office365 integration:

How to ‘Hybridize’ your MS Access Database In Office 365 Azure Database

How I Like to Use the Navigation Pane

First right click on the Navigation Pane bar and show the Search Bar. Then have the Navigation Pane display “All Access Objects”. Now you can enter a search string and the list of Access objects will be filtered to show you just the objects you are interested in. For me, this is the most convenient way to use the Navigation Pane.

 

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!)