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
'8/18/2016
'Source: http://www.accessmvp.com/thedbguy
'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

Else
    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
    
    Else
        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.

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.

Intro

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.

Syntax

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 (").

Examples

Here are some examples of a JSON object syntax.

{"FirstName":"DB","LastName":"Guy","Website":"www.accessmvp.com/thedbguy"}

{"CustNo":"00138","ProductsPurchased":["Milk","Bread","Eggs","Butter"]}

{"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 www.json.org.

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

How to check if ACCDE?

There was a question at MSDN Forums the other day on how to determine if an Access database file was compiled to an ACCDE. I found this question somewhat interesting, because I never had a need to determine whether a database was a true ACCDE file or not.

When I deploy a compiled database, I often rename the file extension to ACCDR to force Access into Runtime Mode. I use the following code to check if the database is running in Runtime Mode and exit the application if it is not.

SysCmd(acSysCmdRuntime)

However, I could not find an equivalent SysCmd constant to check for ACCDE, and I am not sure if there is a property for it as well. So, I came up with the following function, which seems to do the job.

Public Function IsACCDE() As Boolean
On Error GoTo errHandler

Application.VBE.CodePanes(1).Show
IsACCDE = False

errExit:
    Exit Function

errHandler:
    IsACCDE = True
    Resume errExit
    
End Function

I am not sure if it is the best way to perform the ACCDE check, but I just thought I would share it and see if you can tell me if there is a better way. Thank you!

How to Read a Text File

Invariably, at one point or another in your journey to write VBA in Access, there will come a time when you will need to read the contents of a text file into your database. This task may seem challenging at first because it involves an external file, but it doesn't have to be hard.

The OPEN Statement

There are several methods to read a text file into an Access database. VBA has a built-in OPEN statement, which enables input or output access to an external file. Here's an example VBA code to initialize a text file for input:

Open "C:\FolderName\FileName.txt" For Input As #1

The above code creates an object (#1) with a reference to the text file, which then allows its contents to be read in code. For example, the following code reads a line of text from the file into a variable:

Line Input #1, VariableName

After we are done reading its contents, we need to close the connection to the file with the following:

Close #1

Here's an example code snippet to read an entire text file into memory:

Dim intFile As Integer
Dim strFile As String
Dim strText As String
Dim strInput As String

intFile = FreeFile()

strFile = "C:\FolderName\FileName.txt"

Open strFile For Input As #intFile

Do While Not EOF(#intFile)
     Line Input #intfile, strInput
     strText = strText & strInput
Loop

Close #intFile

Please notice the use of the FreeFile() function to retrieve the next available file number rather than assigning a specific file number with the OPEN statement. It avoids possible mistakes of referring to the wrong file in case you are trying to manipulate multiple files at the same time.

The File System Object

My preferred method to read a text file into Access is to use FSO (File System Object). It adds an external reference (overhead) to the application, but I think the process will run a bit faster, and the code will also be a bit cleaner.


For instance, compare the following function, using FSO, to the one above, using the OPEN statement:

Public Function ReadTextFile(strFile As String) As String
'3/10/2016
'http://accessmvp.com/thedbguy

Dim fso As Object
Dim objFile As Object

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists(strFile) Then
     Set objFile = fso.OpenTextFile(strFile, 1) '1=ForReading, 8=ForAppending
     ReadTextFile = objFile.ReadAll
End If

Set objFile = Nothing
Set fso = Nothing

End Function

The above function uses "late binding," so a reference to the File System Object is not necessary. However, you can use "early binding" if you prefer.


I hope you find the above methods useful. Please do not hesitate to let me know which method you prefer and why (or why not the other). Cheers!

Retrieve Greenwich Mean Time (GMT)

I recently had a need to retrieve the current date and time from the Internet to prevent the user from merely changing the system clock to circumvent a security check I was performing.

Turns out, I don't even need a specific web service to provide the current date and time from a web server. Most web servers already include this information when it returns a page requested by a client. All we need to do is examine the response header.

One thing to note is the date and time provided by the web server is set to Greenwich Mean Time (GMT). If you need to know the "local" time, you will have to perform additional manipulation of the result using time zone information.

The following code simply retrieves the GMT from this blog site. If you were able to add any enhancements to this code, I would really love to hear about it. Hope you find it useful.

Public Function GetGMT() As Variant
'4/20/2016
'http://accessmvp.com/thedbguy
'Retrieves the Greenwich Mean Time (GMT) from theDBguy's blog server

On Error GoTo errHandler

Dim objHTTP As Object
Dim strURL As String
Dim strGMT As String

strURL = "http://thedbguy.blogspot.com"

Set objHTTP = CreateObject("Microsoft.XMLHTTP")

With objHTTP
    .Open "GET", strURL, False
    .Send
    strGMT = .getResponseHeader("Date")

End With

If strGMT <> "" Then
    strGMT = Mid(strGMT, 6, 20)
    If IsDate(strGMT) Then
        GetGMT = CDate(strGMT)
    Else
        GetGMT = Null
    End If
End If

errExit:
    Set objHTTP = Nothing
    Exit Function
    
errHandler:
    MsgBox Err.Number & ": " & Err.Description
    Resume errExit
    Resume
    
End Function