Thursday, December 1, 2016

Import Access 97 MDB Data Using Access 2016

Some people may still be using an Access 97 MDB database and then realize they can no longer open their database when they upgrade to Access 2013 or 2016. According to this MS article, support for Access 97 MDBs was removed in Access 2013. The recommended method for upgrading an Access 97 MDB database to a ACCDB file is to use an earlier version of Access like Access 2007 or 2010. However, if this is not an option for you, i.e. you don't have a copy of Access 2007 or 2010 or do not know anyone with a copy of Access 2007 or 2010, then there may be another solution.

CAVEAT: As the title of this article implies, the following solution only applies to migrating the data from the MDB into a ACCDB.

I came across this solution while trying to help a member at UtterAccess. I was thinking one might be able to trick Access by using Excel to import the old data first before importing it again into Access. Unfortunately, I guess the process of doing so was not as simple as it may seem.

In looking around Microsoft Answers forum threads, I found a post by Kevin M. as quoted below (I hope he doesn't mind me reposting it here):

I have "laundered data" through excel 2013 as follows
open excel and select the "data" tab.
select "from Access" and then copy the path to the data sourse from under the "connection" tab.
Then select the "provider " tab and then select "Microsoft Jet 4.0 OLE db provider"
Then select "Next"  (dont select "OK") and paste the path into the database name field (or browse to it) Then select "OK"
This should get an Import Data dialog box and you can click ok to paste the data into excel
good luck

As verified by the UA member mentioned earlier, although this trick was originally offered for Access 2013 users, it still works with Access 2016.

If anyone has found other ways to accomplish the same thing, I would invite you to share it here by posting a comment.

Thank you!

Sunday, November 27, 2016

Form Data Validation

If you ever need to make sure the user entered valid information on the form before committing the data into the table, then it is considered "best practice" to perform data validation routines using the Form's BeforeUpdate event. In addition, it is common practice to use the Tag property of form controls to indicate which data needs to be validated.

For example, let us say you have a form for a loan application and a complete section must be filled out for a co-borrower information when the customer is applying for a joint loan. Using the Tag property approach, you can enter "co-borrower" in the Tag property of each Textbox representing co-borrower information and then use the following code in the Form's BeforeUpdate event:

If Me.LoanType = "joint" Then
   For Each ctl In Me.Controls
      If ctl.Tag = "co-borrower" Then
         If IsNull(ctl.Value) Then
            Cancel = True
            MsgBox "Please complete co-borrower information.", vbInformation, "Required"
            Exit For
         End If
      End If
End If

As already mentioned, this approach is common practice and usually applies to probably 99% of situations for data validations. However, someone approached me recently and said this approach of using the Tag property and looping through all the form controls made his form ran very slowly. It turns out his form was very busy with multiple subforms and a lot of unbound controls updated using code to reflect specific information about the current record from other tables in the database.

So, in this case, we want to avoid looping through all form controls, which is a requirement when using the Tag property approach. To do so, we'll need to know the names of the controls to evaluate, so we can simply validate them. One of the main advantages of using the Tag property is it is very easy to add or remove controls to validate without needing to modify the code. If we hard code the names of the controls to validate, we'll need to modify the code each time our requirements change.

As an alternative, we need to somehow store the names of the controls to validate and then go through each of them to check the form's data. There are several options to accomplish this task. For example, we can use a hidden unbound Textbox to store a comma-delimited value of control names to validate, We can then use the Split() function to store the control names in an array for validation.

The approach I will demonstrate will use a hidden unbound Listbox to store the control names. I like using a Listbox because it is very easy to loop through each item in a Listbox when performing our data validation. For this approach, I recommend using a Value List for a Row Source, although using a table will work just fine as well. Assuming the Bound Column of the Listbox is set to the column with the names of the control, here's a modified version of the above code using a Listbox rather than the Tag property:

If Me.LoanType = "joint" Then
   For lngItem = 0 To Me.ListboxName.ListCount - 1
      If IsNull(Me.Controls(Me.ListboxName.ItemData(lngItem))) Then
         Cancel = True
         MsgBox "Please complete co-borrower information.", vbInformation, "Required"
         Exit For
      End If
End If

As you can see, this alternative approach accomplishes the same task as using the Tag property without affecting the speed of a very busy form. Adding or removing controls to validate is also as simple as updating the Listbox without needing to alter the code. This approach can even be expanded by storing all control names in a table along with the name of the form or forms to which the control validation applies. Doing so will allow the same code to validate multiple forms.

Thursday, September 15, 2016

Collect Signatures in Access Demo

If you create database applications, you are probably happy with storing everything as electronic data. However, there may be occasions when you might need to collect hand-written information.

Some Access database developers like to use tablets or smart phones to make their database applications somewhat portable. And if their business requires a hand-written signature from a client, these devices are already equipped with an input device for collecting the signature.

This demo uses an ActiveX control to allow the user to sign a form. The signature information is then stored with their data and displayed on a form using two different techniques.

You may download the demo file at UtterAccess or My Website.

Thursday, August 25, 2016

How to set Compact on Close on BE

The "Compact on Close" setting in the Current Database Options will automatically perform a Compact and Repair (C&R) each time the user closes the database file. Leaving this setting on could keep the file size of the database to a minimum, but it could also result in corruption if the operation is interrupted in the middle of the C&R process. Instead, it is a more common practice to check the file size of the FE and only set the "Compact on Close" option when the file size reaches a predetermined threshold.

Setting the "Compact on Close" option for the FE is a simple matter of executing the following code (as shown in my previous blog):

Application.SetOption "Auto Compact", True

Turning it off would then look like this:

Application.SetOption "Auto Compact", False

However, someone asked me recently how to set the "Compact on Close" setting for the BE from the FE. Obviously, using the above code only works for the FE, so I decided to create the following function. Just pass the filepath to the BE database and an optional True (to set Compact on Close) or False (to unset it) to the function.

Public Function SetAutoCompact(strDB As String, Optional bool As Boolean = True) As Byte
'Sets the Compact on Close flag of an external database

Dim objApp As Object
Dim objAcc As Object

If Dir(strDB) = "" Then
    MsgBox "Cannot find external DB file " & strDB, vbInformation, "Warning"
    SetAutoCompact = 1

    Set objApp = CreateObject("Access.Application")
    Set objAcc = objApp.DBEngine.OpenDatabase(strDB)

    On Error Resume Next
    objAcc.Properties("Auto Compact") = bool
    If Err.Number = 0 Then
        On Error GoTo 0
        objAcc.Properties.Add objAcc.CreateProperty("Auto Compact", dbBoolean, bool)

    End If

    MsgBox "Finished setting 'Compact on Close' for " & strDB, vbInformation, "Done!"
    SetAutoCompact = 0
End If

Set objAcc = Nothing
Set objApp = Nothing

End Function
As previously mentioned, the above function should be used after checking the file size of the BE against a predetermined threshold. Also, compacting the BE over a network connection is not recommended.

As always, any comments are welcome. Thank you.

Thursday, July 28, 2016

JSON Primer

I recently worked on a project, which includes a requirement to query a web service for customer transaction data. I had a choice of requesting either XML or JSON data from the server. I have worked with XML before, but I heard JSON is fast becoming the "preferred" format for web service communications, so I decided to learn about it quickly.


JSON stands for "JavaScript Object Notation." It is a text-based data interchange format much like XML. However, JSON is much simpler than XML with smaller grammar and represents the data structure more directly than XML. Although XML and JSON are somewhat similar, XML is better for representing documents while JSON is better for representing data.


A JSON object is essentially a collection of key/value pairs. The object syntax begins with a left curly bracket ({) and ends with a right curly bracket (}). The key and value pairs are separated by a colon (:), and each set of key/value pair is separated by a comma (,). The values for each key can be an unordered or ordered list of values (array). The list of values starts with a left square bracket ([) and ends with a right square bracket (]). Each value within an array of values is separated by a comma (,). A value can represent any of the following:

  • string
  • number
  • object
  • array
  • true
  • false
  • null
If the value is a string, it must be enclosed in double quotes ("). The key name is also enclosed in double quotes (").


Here are some examples of a JSON object syntax.



{"Menu": [ {
    "Breakfast": [
        { "ItemName":"Eggs",
           "Price":2.75 },
        { "ItemName":"Hashbrown",
           "Price":3.50 }
    ] }, {
    "Lunch": [
        { "ItemName":"Burger",
           "Price":7.25 },
        { "ItemName":"Fries"
           "Price":4.50 }
    ] }

As you can see, white space is ignored when a JSON object is parsed. So, formatting a JSON object helps the developer read the code better. There are several JSON formatters available on the Internet.

To learn more about JSON, please visit the official JSON website at

For parsing JSON objects in VBA, I recommend using VBA-JSON at GitHub.