Microsoft Access Tips & Tricks: Opening Files From Within Access

One of the important things I need to do from within my EBooks database is opening and reading files once I have figured out what I want to read. There are at least three ways to do this from within Microsoft Access, and in this post, I will examine all three ways to do it. Even though I use this capability only to read EBooks on my computer, other uses for this capability are easy to think of. For instance, you might want to catalog your videos or photographs in an Access database and have the ability to open them when needed from inside the database itself. In general, any files you can open on the computer you can open from inside Microsoft Access using the techniques in this post. With a couple of the techniques posted here, you can also run any program installed on your computer from within Access.

If you are interested, you can find my earlier posts on finding the median, the mode, the geometric and harmonic means, ranking every row in a query, selecting random rows out of a table, calculating running sums and averages, calculating running differences, creating histograms, calculating probability masses out of given data, calculating cumulative distributions out of given data, finding percentile scores, percentile values, calculating distinct counts, full outer joins, parameter queries, crosstab queries, working with system objects, listing table fields,finding unmatched rows, calculating statistics with grouping, job-candidate matching, job-candidate matching with skill levels, great circle distances, great circle initial headings, using Excel functions in Access, using the windows file-picker, using the Access date-picker, setting tab indexes quickly and correctly, pre-filling forms based on previous entries, highlighting form controls, conditional formatting, performing date manipulations, counting letters, words, sentences and paragraphs, calculating age, propercasing text entries, flattening a table (using SQL), flattening a table (using VBA), cascading comboboxes, and parsing file names.

There are three main techniques that can be used to open files from inside Microsoft Access. The first of these techniques involves the use of the Shell() function. The Shell() function can not only be used to open files, but can also be used to run any program installed on the computer. For instance, you can not only use Shell() to open a text file in Notepad, but you can also simply open Notepad with no file loaded. This can be useful because you might require the functionality to allow the user to enter notes into some application like Notepad, and save it on the computer. Or you can have Shell() open the Calculator program so that the user can perform some calculations and enter the result into some form in Microsoft Access.

The syntax of the Shell() function is quite simple. It requires a string argument that denotes the program to be opened, and an optional argument that specifies the window style. You can refer to the Access help files for the syntax, and also the possible values for window style.

To open the calculator program, for instance, you would use the following lines of code:

Dim RetVal as Integer
RetVal = Shell("C:\Windows\calc.exe", vbNormalFocus)

If the Shell() function successfully executes the named file, it returns the task ID of the started program.

How do you use Shell() to open files from within Access? In this previous post on parsing file names, I have provided the code that will enable you to find the file extension of a given file. The program that should be used for opening a file depends on the file extension in most cases. So, one needs to use a series of if ... else if ... switches to check the value of the extension, and open the file using the appropriate program. A snippet of such code is provided below:

Public Sub OpenUsingShell(MyFileName as String)
Dim RetVal as Integer
Dim Extension as String

Extension = ExtractExtension(MyFileName)
If (Ucase(Extension) = "TXT") then
 RetVal = Shell("C:\Windows\notepad.exe " & MyFileName, vbNormalFocus)
ElseIf (Ucase(Extension) = "DOC") then
 'Code for invoking Microsoft Word to open the file goes here

'Other checks for other file extensions go here and so on

End If
End Sub

As you can see, the use of Shell() is somewhat inconvenient because you need to specify the program you want to invoke to open each file that you want to open. So, your code can grow quite large if you are dealing with lots of different types of files that require lots of different programs to open. However, it gives you, the programmer, complete control over what program you want to invoke to open any given document or other file on your computer. Even if a file type is registered in the operating system to be opened by one particular programmer, you can invoke some other program to open it.

You can use different programs to open the exact same type of file depending on any number of criteria such as the file path, file name, etc. Also depending on how the file opening is invoked, you can open the file in different programs (one for read-only access, one for editing, etc.). That makes Shell() very powerful even though it can be a little inconvenient to use. You can also use Shell() without a file name to simply run any program that is installed on your computer (as illustrated in the example using calc.exe above).

The next technique that I will explain is a lot easier to use than Shell(). This is the Application.Followhyperlink() function. Using Application.Followhyperlink() is simplicity itself. Consider the snippet of code below:

Public Sub OpenUsingHyperlink(MyFileName as String)
Application.FollowHyperLink(MyFileName)
End Sub

If the computer knows how to handle the type of file that is passed to Application.FollowHyperLink(), it will simply open the file using the default method for handling such a file. That is all there is to it.

However, there are some disadvantages to using this method. First and foremost, Application.FollowHyperLink() is provided to open links over the internet, not necessarily open local files on the computer. Because of this, the computer treats any file opened using this method as a download from the internet, and therefore, inherently unsafe. Many types of files, when opened using this method, will trigger a security warning that you have to dismiss before the file will open up. This is not a warning produced by Access (it is produced by the operating system), so using DoCmd.SetWarnings False before Application.Followhyperlink() will not get rid of these warnings.

Moreover, the default application used to open files in this way can be different from what you would open the file in by double-clicking on it on your computer. That is because the computer thinks you are opening the file off the internet, so it opens the file in the default application for that file as seen by your internet browser. So, for instance, image files may open up in your browser instead of in your favorite image-editing or viewing software because, as far as the browser is concerned, an image on the internet is to be handled by the browser itself rather than being passed off to your image editing program.

Also, if the file you pass to Application.FollowHyperLInk() does not exist, or if there is no program associated with the file type in your browser, there is no warning of any sort. The command simply fails silently, without producing either the desired result or an error message. So, you have to be careful when using the method, in spite of its simplicity. Until recently, this was my usual method of opening files from within Access, but I have now switched to the third method detailed below.

The third method involves the use of the ShellExecute API. It is a very powerful method that has all the advantages of both the methods described above, but none of the disadvantages. However, with power comes complexity. You can read about some of the parameters that the program takes, as well as return values that it produces at this link. The code snippet below should also help clarify things a bit.

This is the code behind a form in my EBooks database application that allows me to browse through the books in the catalog. In the form, the field BookLocation contains the file name corresponding to a given book along with a relative path to the file. There is a button on the form called Read that, when clicked, invokes the ShellExecute API to open the file and allows me to read the selected book. The ShellExecute API works only with full paths, so the subroutine below converts the relative path to a full path, and then calls the ShellExecute function.

Private Declare Function apiShellExecute Lib "shell32.dll" _
   Alias "ShellExecuteA" _
   (ByVal hwnd As Long, _
   ByVal lpOperation As String, _
   ByVal lpFile As String, _
   ByVal lpParameters As String, _
   ByVal lpDirectory As String, _
   ByVal nShowCmd As Long) _
   As Long  'You have to declare the function because it is not a native Microsoft Access function.  Rather it is a Visual Basic function.

Private Sub CmdRead_Click()

   Dim RetVal As Long
   Dim TaskID As Variant
   Dim FullFilePath As String
  
   On Error Resume Next
   If Not IsNull(Me.BookLocation) Then
       FullFilePath = Replace(Me.BookLocation, ".\", CurrentProject.Path & "\")
       RetVal = apiShellExecute(hWndAccessApp, "open", FullFilePath, vbNullString, vbNullString, vbNormalFocus)
       If RetVal = 0 Then  '0 indicates not enough memory
           MsgBox "The operating system is out of memory or resources.", vbExclamation, "File Open Failure"
       ElseIf RetVal = 2 Then  '2 indicates that the file was not found
           MsgBox "The specified file was not found.", vbExclamation, "File Open Failure"
       ElseIf RetVal = 3 Then  '3 indicates that the path was not found
           MsgBox "The specified path was not found.", vbExclamation, "File Open Failure"
       ElseIf RetVal = 11 Then  '11 indicates that the executable is corrupted
           MsgBox "The .exe file is invalid (non-Win32 .exe or error in .exe image).", vbExclamation, "File Open Failure"
       ElseIf RetVal = 31 Then  'If the association is missing, then use Shell to try the OpenWith dialog
           TaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " & FullFilePath, vbNormalFocus)
       ElseIf RetVal <= 32 Then  'the apishellexecute function returns a value greater than 32 on success
           MsgBox "File could not be opened.  Error number " & CStr(RetVal), vbExclamation, "File Open Failure"
       End If
   Else
       MsgBox "No file location entered!", vbExclamation, "Can not open file"
   End If
End Sub 

I have checked for some of the possible error messages that the ShellExecute API may produce (I think these are the most common errors that are likely to occur), but there are several more as you can see at the link I referred to earlier.

What is special about the ShellExecute API function? As I mentioned earlier, it combines the advantages of the two previous method. It gives you convenience because you can call the function as I have done above, using just the file name. The function automatically invokes the program that is registered with the operating system to handle the file type. You do not have to check file extensions and decide which program to invoke. Moreover, the program that is invoked automatically is the one registered with the operating system, not the one associated with the file extension in your browser. So, there are no surprises with images opening up in your browser, etc.

But, you can actually use the function to specify the program to use to open the file also. Simply use syntax as in the Shell() command to pass the program path along with the file path, and the ShellExecute API will open the file in that program. You can use program switches and options also to control precisely how the file is opened. This gives you the ability, for instance, to open the same file in multiple programs depending on which button is clicked in your Microsoft Access form - one for viewing the file, a different one for editing the file, etc.

In addition, you can print the file directly using the ShellExecute API instead of opening it simply by changing the "open" in the arguments passed to the ShellExecute API to "print". Moreover, since the operating system knows that the file being opened is a local file, it will not complain, and put up warning messages. If it knows how to open the file, it will open it immediately. More importantly, if it does not, it will not fail silently. Instead, it will produce an error message (you just have to make sure you check for all return values less than or equal to 32, and produce an appropriate error message for the user of your program).

Hope this post has been helpful in solving any problems you might have had with opening external files from within Access. If you have any problems or concerns with the VBA code in this lesson, please feel free to let me know by posting a comment. If you have other questions on Access that you would like me to address in future lessons, please feel free to let me know through your comments too. Good luck!

The Math Behind Using Digital Roots To Check Your Arithmetic

In my post on digital roots a while back, I provided some examples of using digital roots to perform sniff tests on basic arithmetic operations like addition, subtraction and multiplication. Digital roots turn out to have interesting properties that enable them to be used to check arithmetic operations, and also to solve some brain-teasers that rely on these interesting properties. I did not provide any reasoning or proof as to why digital roots have the properties mentioned in that post.

In this post, I will provide the basic mathematics that explains how and why digital roots have these interesting properties. The properties we will look at are:

• The digital root of a sum is the sum of the digital roots of the numbers being added
• The digital root of a difference is the difference of the digital roots of the numbers in the subtraction
• The digital root of a product is the product of the digital roots of the numbers being multiplied

Let us first define the operation of taking a digital root of a number using the notation D(n). D(n) will represent the digital root of the integer, n. In this mathematical notation, the three properties above can be rewritten as below:

• D(a + b) = D(D(a) + D(b))
• D(a - b) = D(D(a) - D(b))
• D(a * b) = D(D(a) * D(b))

On the right hand side, we include an extra digital root operation because it is possible that the sum, difference or product of the digital roots may not be in the range from 1 to 9, whereas on the left hand side, we will always get an answer that is inside that range. Proving that these properties are always true turns out to be quite easy once we figure out what a digital root is exactly.

So, what exactly is the digital root? From the discussion in the post on digital roots, you probably already know that the digital root of a number is simply the remainder when that number is divided by 9. If the remainder is 0, we use 9 as the digital root instead. Given this definition of the digital root, let us see how this applies to checking the results of addition.

Let us assume that a + b = c.

Let us also assume that a = 9w + x, and b = 9y + z.

Therefore, the digital root of a is x, and the digital root of b is z. Now, c, since it is equal to a + b, must be equal to 9w + 9y + x + z. Since 9w + 9y is fully divisible by 9 without a remainder, the digital root of c must equal D(x + z). This then proves that D(a + b) = D(D(a) + D(b)). That is all there is to this seemingly mysterious property of digital roots!

The case of subtraction is very similar to that of addition. I will not insult the intelligence of my readers by providing the proof here. Instead, let us take on multiplication.

Let us assume that a*b = c

Let us also assume that a = 9w + x, and b = 9y + z.

Therefore, the digital root of a is x, and that of b is z. Now, c, since it is equal to a*b, must be equal to 81wy + 9wz + 9yx + xz. Of the 4 terms above, the first three are divisible by 9 without a remainder. Therefore, the digital root of c is simply D(xz). And that is precisely what the property we are discussing says: D(a*b) = D(D(a)*D(b)).

Now, the astute reader is probably scratching his/her head with a question such as "what is so special about the number 9 that makes all this work as above?" Well, as it turns out, there is absolutely nothing special about the number 9 at all. We could have replaced 9 in the above proofs with 7 or 4 or any other number (1 would probably make little sense), and they would work perfectly fine. Which actually means that we could define the digital root operation with respect to any "base", not just 9.

What we did above was not just D(n), but D9(n). That is, we were talking about digital roots with respect to 9. We could just as well have D3(n), D5(n) or D8(n). For checking the results of addition, subtraction and multiplication, they would all work perfectly fine. The only difference would be the difficulty or ease of calculating these digital roots. Because we use base 10 arithmetic (normally), it turns out to be very easy to calculate D10(n) (which would just be the last digit of a number, or 10 if that last digit was 0), D5(n) (which would be the remainder of dividing the last digit by 5, and 5 if that remainder was 0), and D2(n) (which would be the remainder of dividing the last digit by 2, and 2 if that remainder was 0).

Notice that the traditional definition of digital roots involves reducing the given number to a single digit from 1 to 9, but that is only because the traditional definition of digital roots has always been D9, though most people do not realize this when using digital roots. It is perfectly acceptable for digital roots to have any number of digits, and in fact D10(n) can be any number from 1 to 10, and D1000(n) can be any number between 1 and 1000, inclusive.

It is slightly more difficult to calculate other digital roots, which is why they are not used too often. D9(n) turns out to be easy because it is just the sum of the digits of the number after throwing out 9's. This is once again because we use base 10 arithmetic (if we did all our calculations in hexadecimal numbers, D16(n) and D15(n) would be as easy to calculate as D10(n) and D9(n) are right now with decimal numbers).

So, why do we use D9 to verify the correctness of arithmetic such as addition, subtraction and multiplication instead of using something easier to compute such as D10? The answer lies in the usefulness of the test itself. The basic problem is that since D10 only uses the last digit of a number, the D10 of two numbers is the same even if all the remaining digits were completely wrong.

Thus, for example, let us say we want to check whether 324 + 642 = 456. We know that this is obviously not correct, but D10(324) = 4, D10(642) = 2, and D10(456) = 6. So, checking using digital roots can not rule out 456 as possibly the correct answer to the given problem. D9 overcomes this problem to some extent by making sure that the answer does not depend on just one digit.

Thus, D9(324) = 9, D9(642) = 3 and D9(456) = 6. Thus, we can conclude that 456 is wrong using D9 even though we could not rule it out using D10. But D9 has another problem that becomes apparent when dealing with long numbers: D9, by its very nature, depends only on the digits of the number, not the order of digits in the number.

Thus, let us say we want to see if 4987 + 9384 = 13471. Using D10, we can conclude that since D10(7 + 4) = D10(13471), we can not rule out 13471 as the correct answer. Using D9, we get D9(4987) = 1, D9(9384) = 6, and D9(13471) = 7. Since 6 + 1 = 7, we still can not rule out 13471 based on D9. It turns out that I deliberately interchanged two digits in the correct answer to get 13471. The correct answer is 14371, and checking with D10 and D9 did not reveal the problem. D9 is better at it than D10, but not perfect.

But, as we found out in this post, we are not restricted to using D9 to check the results of our computations, even though that is the most common way in which digital roots are utilized to sniff-test the results of computations. Because of the general properties of digital roots, we can actually use digital roots with respect to any number to check our computations. In fact, under some conditions, it may make sense to check the answers using multiple digital roots.

D10 and D9 could be quick checks because of the ease with which we can compute digital roots with respect to these 10 and 9. But, if time permits, one of the best digital roots for this use is D7. Divisibility by 7 does not depend on the last few digits of a number. Moreover, you can not interchange the digits of a number and retain divisibility by 7. Thus, D7 can spot mistakes that D10 and D9 can miss. In fact, this is one of the main reasons why D7 is used as a check digit in many applications!

Obviously, no digital root is going to be completely foolproof. But the number of false positives when we use D7 is much smaller than when using D10 or D9.

Going back to 4987 + 9384, we see that D7(4987) = 3, and D7(9384) = 4. Now, D7(13471) = 3, so we can tell right away that 13471 is not the correct answer. It turns out that D7(14371) = 7, thus giving us more confidence in that answer than in 13471. Many prime numbers whose multiples do not include any power of 10, such as 7, 11, 13, etc., are good candidates for using with digital roots to verify the correctness of additions, subtractions and multiplications.

What should you use? That is a matter of how much accuracy and confidence we are willing to trade off for convenience. D10 and D9 are easier to calculate than D7 or D11, which are easier to calculate than D13. But with practice, D7 and D11 are actually not that difficult to compute. Given that the task of checking the accuracy of mathematical computations is made a lot more reliable using D7 and/or D11, rather than D9 or D10, it makes sense to use digital roots with respect to 7 or 11 to perform quick checks of mathematical computations.

We could use D10, which is extremely convenient, but we sacrifice a lot in accuracy (too many false positives). Or we could use D9, which is slightly less convenient. We gain a little in accuracy, but it is not perfect. When we switch to D7 or D11, we encounter more inconvenience, but the accuracy takes a big jump up. It is difficult to fool D7 or D11 with the types of mistakes people make when performing computations (such as interchanging digits, missing a carryover somewhere in the middle of a long addition, errors caused by multiple borrowings during subtraction, and so on). This makes them much more useful for checking for computation errors than traditional digital roots.

Hopefully, this post has expanded your horizons as far as digital roots are concerned. In summary, digital roots can be computed with respect to any number, not just 9. Digital roots with respect to 9 (this is the traditional definition of digital roots) have their place when it comes to checking the possible correctness of mathematical computations, but digital roots with respect to other numbers like 7 and 11 may be much more useful in this respect. A little practice in computing D7 and D11 will go a long way in building up both your capacity for mental computation as well as your ability to sniff-test your mental computations much more reliably than traditional digital roots will ever allow you to!

Now, in this post, I have talked about verifying the results of addition, subtraction and multiplication. Most people assume that digital roots are really not very useful in checking the correctness of division. Not so fast! In the next post, I will talk about how to use digital roots for checking the results of division problems. Stay tuned, you will not be disappointed!

Microsoft Access Tips & Tricks: Parsing File Names

In some database applications, one of the inputs required from the user may be a file name, including its path (basically the location of a document of some sort). In this post, I will discuss how such an input can be broken up into various component parts using basic string processing functions. Even if your application does not require the same operations to be performed on file names, you might get some insights into how string functions in Access work, and you can use that insight for other applications that require other forms of string processing.

If you are interested, you can find my earlier posts on finding the median, the mode, the geometric and harmonic means, ranking every row in a query, selecting random rows out of a table, calculating running sums and averages, calculating running differences, creating histograms, calculating probability masses out of given data, calculating cumulative distributions out of given data, finding percentile scores, percentile values, calculating distinct counts, full outer joins, parameter queries, crosstab queries, working with system objects, listing table fields,finding unmatched rows, calculating statistics with grouping, job-candidate matching, job-candidate matching with skill levels, great circle distances, great circle initial headings, using Excel functions in Access, using the windows file-picker, using the Access date-picker, setting tab indexes quickly and correctly, pre-filling forms based on previous entries, highlighting form controls, conditional formatting, performing date manipulations, counting letters, words, sentences and paragraphs, calculating age, propercasing text entries, flattening a table (using SQL), flattening a table (using VBA) , and cascading comboboxes.

I have talked about the use of the Windows File Picker in this earlier post. The code used in that post includes functions such as ExtractPathname(), but I did not provide code for some of these functions in that post. In this post, I will take a full file name (including the path and extension), and provide the code that does the following things:

• Extract the file path
  
• Extract the file name (without the path, but with the extension)
  
• Extract the file name (no path, no extension)
• Extract the file extension (gives you the file type)
• Convert the file path into a relative path if the full path points to a location below the current location of the database file
• Convert a relative path to the absolute file path assuming that the relative path refers to a location below the current location of the database file

Extracting the file path

The directory separation character in Windows (and pretty much any Microsoft operating system since the days of DOS) is the "\" character. In a full file name, everything up to and including the last "\" in the name is the file path, and what follows that is the actual file name (including the extension). Therefore, extracting the file path simply involves locating the last "\" in the full file name, and copying out everything upto and including that "\".

Public Function ExtractPathname(fullFileName As String) As String
  '"C:\Document\DigitalBooks\Fiction\Filename.doc" returns
  '"C:\Document\DigitalBooks\Fiction\"

  ExtractPathname = Left(fullFileName, InStrRev(fullFileName, "\"))

End Function 

Extracting the file name (without the path, including the extension)

In a full file name, everything up to and including the last "\" in the name is the file path, and what follows that is the actual file name (including the extension). Therefore, extracting the file name simply involves locating the last "\" in the full file name, and copying out everything that follows that "\".

Public Function ExtractFilename(fullFileName As String) As String
  '"C:\Document\DigitalBooks\Fiction\Filename.doc" returns "Filename.doc"

  Dim lastBS As Integer

  lastBS = InStrRev(fullFileName, "\")
  ExtractFilename = Right(fullFileName, Len(fullFileName) - lastBS)

End Function 

Extracting the file name (no path, no extension)

We will use the ExtractFileName() function we defined above to first get the file name including the extension. After that, we will remove the extension and return just the file name without the extension. Remember that the extension is simply what follows the last "." in the file name. Remember also, that this is true only on Windows systems. If you are working with file names from other operating systems, you might have to account for differences such as, but not limited to, filenames starting with a "." (such file names are very common on Unix-based operating systems).

Public Function ExtractFilenameNoExt(fullFileName As String) As String
 '"C:\Document\DigitalBooks\Fiction\Filename.doc" returns "Filename"

  Dim lastDot As Integer

  fileName = ExtractFilename(fullFileName)
  lastDot = InStrRev(fileName, ".")
  If lastDot <> 0 Then
      ExtractFilenameNoExt = Left(fileName, lastDot - 1)
  Else
      ExtractFileNameNoExt = fileName
  End If

End Function 

Couple of points to note about the function above: first, notice that we check whether the value of lastDot is 0. If it is, then there is no "." in the file name, and therefore, the file name does not contain an extention. In that case, the file name is actually the file name without the extension. Second, if we do find the ".", the file name without extension is everything up to but NOT including the ".". That is why we have to subtract 1 from lastDot in the call to Left().

Extracting the file extension

Remember, once again, that the extension is simply what follows the last "." in the file name. Remember also, that this is true only on Windows systems. If you are working with file names from other operating systems, you might have to account for differences such as, but not limited to, filenames starting with a "." (such file names are very common on Unix-based operating systems). Moreover, file names might have no extensions, though this is rare on Windows systems (not so rare in other operating systems such as Unix and its derivatives). If there is no "." in the filename with extension, then the file has no extension and the extension should be set to an empty string.

Public Function ExtractExtension(fullFileName As String) As String
  '"C:\Document\DigitalBooks\Fiction\Filename.doc" returns "doc"

  Dim lastDot As Integer

  lastDot = InStrRev(fullFileName, ".")
  If lastDot <> 0 Then
      ExtractExtension = Right(fullFileName, Len(fullFileName) - lastDot)
  Else
      ExtractExtension = ""
  End If

End Function 

Converting the file path into a relative path

This is functionality that is not commonly needed, but can be very useful. I need it so that, inside my ebooks database, I can store the relative paths of the ebooks rather than the full paths. This allows me to take the root folder of my ebooks location (which is also where my ebooks database resides along with multiple folders for my actual ebooks), and move it to any other location on my hard disk, or even to an external disk. As long as the relative paths from the database to the actual ebooks does not change, the locations of the ebooks stored inside the database are valid, and I can get to any of them easily. Otherwise, every time I moved my ebooks collection, I would have to go in and change all the ebook locations in my database. It would be a nightmare.

To be more concrete, let us assume that my ebooks database resides at C:\Document\Digital Books. Under C:\Document\Digital Books, I also have other subfolders such as Fiction, Non Fiction, Comics, Series, Collections, etc., that actually contain my ebooks inside them. If I stored "C:\Document\DigitalBooks\Fiction\Filename.doc" inside my database as the location of a particular ebook, the database would lose the ability to locate that book if I moved my entire collection to another hard disk designated as D:. My database would now be at D:\Document\DigitalBooks, and filename.doc would now be at D:\Document\DigitalBooks\Fiction\Filename.doc. In fact, the database would not be able to locate that book if I decided to move my entire database inside my Documents and Settings folder, resulting in the new path to the ebook becoming "C:\Documents and Settings\Blogannath\My Documents\DigitalBooks\Fiction\Filename.doc".

The relative path from my database to the ebook never changes in any of the above moves. It is always ".\Fiction\Filename.doc". That is the motivation behind storing ebook locations with relative paths rather than absolute paths. In any case, in spite of this long explanation, the actual code for converting absolute paths to relative paths is quite simple as long as the absolute path starts with the database location.

Public Function GetRelativePath(fullFileName as String)

'"C:\Document\DigitalBooks\Fiction\Filename.doc" returns ".\Fiction\Filename.doc"
'if the database resides at "C:\Document\DigitalBooks". 
    GetRelativePath = Replace(fullFileName, currentProject.Path, ".")

End Function 

Converting the relative file path into the absolute path

This operation is necessary to issue commands to the operating system to open the given file when I want to read an ebook. A relative path with respect to the database location is not the relative path with respect to the application that opens the ebook in most cases (the file may need to be opened with Adobe Reader when the ebook is a PDF file, for instance, and Adobe Reader is not installed in the same location as the ebooks database). The conversion from relative path to absolute path is quite straightforward as shown below:

Public Function GetAbsolutePath(fullFileName as String)

'".\Filename.doc" returns "C:\Document\DigitalBooks\Fiction\Filename.doc"
'if the database resides at "C:\Document\DigitalBooks". 
    GetAbsolutePath = Replace(fullFileName, ".\", currentProject.Path & "\")

End Function 

Notice that we use ".\" in the Replace() function call rather than simply ".". This is absolutely necessary because the Replace() function would otherwise replace all dots in the supplied argument with the path where the database is located. This would include the dots in the file name, including the one that separates the file name from its extension, among others! So, we have to search for ".\", and then replace that with the concatenation of currentProject.Path with "\". The currentProject.path does not contain a trailing "\", so we have to supply it ourselves.

Hope this post has been helpful in solving any problems you might have had with parsing file names in Access. If you have any problems or concerns with the VBA code in this lesson, please feel free to let me know by posting a comment. If you have other questions on Access that you would like me to address in future lessons, please feel free to let me know through your comments too. Good luck!

Microsoft Access Tips & Tricks: Cascading Comboboxes

Comboboxes and listboxes are very useful in forms because they make data entry easier. They also reduce errors in data entry because you can set up the combobox or listbox to reject items that are not already in the list. I particularly like comboboxes because you don't have to take your hands off the keyboard to make an entry in a combobox. As you type, if the appropriate entry exists, you can hit Tab to choose it and move on to the next control in the form.

If you are interested, you can find my earlier posts on finding the median, the mode, the geometric and harmonic means, ranking every row in a query, selecting random rows out of a table, calculating running sums and averages, calculating running differences, creating histograms, calculating probability masses out of given data, calculating cumulative distributions out of given data, finding percentile scores, percentile values, calculating distinct counts, full outer joins, parameter queries, crosstab queries, working with system objects, listing table fields,finding unmatched rows, calculating statistics with grouping, job-candidate matching, job-candidate matching with skill levels, great circle distances, great circle initial headings, using Excel functions in Access, using the windows file-picker, using the Access date-picker, setting tab indexes quickly and correctly, pre-filling forms based on previous entries, highlighting form controls, conditional formatting, performing date manipulations, counting letters, words, sentences and paragraphs, calculating age, propercasing text entries, flattening a table (using SQL), and flattening a table (using VBA).

In this post, I will talk about a method for making different listboxes or comboboxes on a form dependent on one another (usually in a sequence), making the data entry process even smoother and faster. In this scheme, we might have several comboboxes on the form that collect different information that fit in a hierarchical order. Then, as each of the comboboxes are filled in, the contents of the other comboboxes are adjusted to show only relevant information to the user of the form.

To take a concrete example, take the example of a form that collects information about the type of vehicle a person drives. You might have one combobox for the make of the vehicle, another for the model of the vehicle, another for the trim level of the vehicle, and so on. It makes sense for the person to be able to choose from all possible makers when they make a choice in the Vehicle Make combobox, but after they have done that, there is no point in showing them a huge list of models in the Vehicle Model combobox, where most of the models are not made by the vehicle maker that has already been entered. Instead, we can filter the Vehicle Model combobox to only show models that the chosen maker makes. And the same principle applies to the trim level once the make and model have been chosen.

Another such example would be the entry of addresses. You can either set it up so that once the person enters a street name, only cities with that street name are available in the city combobox, and then only countries with that city name are shown in the country combobox. Or, you can set up the form so that the respondent fills in the country first, which is used to filter the city list, and then a selection in the city list enables filtering of the street list.

Since choices in one combobox or listbox "cascade" forward to other comboboxes or listboxes on the form, this kind of setup is called cascading comboboxes or cascading listboxes. In this post, I will examine how to set this up using comboboxes, but the same thing can be done using listboxes. What matters is that the information being collected in the two (or more) controls be hierarchical in some sense so that a selection in one control limits the possible choices in the next control.

I will deal with the example of vehicle makes and models to make the example code I present below, more concrete. You can take the same principle and apply it to other situations that you encounter (such as addresses, store inventories, survey responses, or any other instance where information can be categorized hierarchically, and there is a fixed set of responses that make sense for any given level based on the entries for the higher or lower levels in the hierarchy). In general, it makes sense to move from the highest level in the hierarchy to the lower levels because the number of possible entries at the lower levels are usually exponentially higher than in the higher levels (there are probably 10 times as many vehicle models as there are makes, for example), but there is not reason why you can not start from any level in the hierarchy and move in any direction.

What makes all this possible is a table of possible choices that contains fields for each level of the hierarchy. For instance, take the example of vehicles. You might have a table as shown below:

ID	Make	Model	Trim
1	Toyota	Corolla	LX
2	Toyota	Celica	GT
3	Honda	Accord	EX
4	Honda	Civic	LX
5	Ford	Mustang	GT
6	Ford	Mustang	V6
7	Chevrolet	Aveo	LS

Note that there is a field for the vehicle make, vehicle model and vehicle trim level. You can create a similar table for addresses (number, street name, city, country), store inventories (category, sub-category, make, model, item), etc. The important thing to remember is that each level in the hierarchy must have its own field in the table. This table will be the row source for all the comboboxes or listboxes that are part of that hierarchy on the form. The data that the users enter should ideally also go into separate fields in the data entry table (remember that all fields in database tables are supposed to contain atomic data), but, under some circumstances, it may make sense to manipulate the data to some extent and store the manipulated data in the data entry table.

Now, for the setup of the form. There are two ways to go about it. You can make all the comboboxes visible and enabled to begin with. Set up the tab stops so that the user of the form will go through them in the logical order you think makes the most sense. Then set the row source of the first of these comboboxes to the distinct data values in the appropriate field of the row source table. So, in the vehicle information case we have been using as an example, the row source of this the combobox would be set to:

select distinct Make from vehiclesTable order by Make 

Leave the row source of the other comboboxes empty when you design the form. You will fill them when appropriate, at runtime, using VBA. The question is when is it appropriate to fill them? It is appropriate to fill the row source for the second combobox as soon as the user makes a selection in the first combobox. It is appropriate to fill the row source for the third combobox when the user makes a selection in the second combobox, etc.

So, in the afterUpdate() event of the first combobox (which, let us assume, is named cboVehicleMake), you would have the following code:

Private Sub cboVehicleMake_AfterUpdate()

  On Error Resume Next

  cboVehicleModel.RowSource = "Select distinct Model " & _
           "FROM vehiclesTable " & _
           "WHERE Make = '" & cboVehicleMake & "' " & _
           "ORDER BY Model"

End Sub 

Obviously, the second combobox is named cboVehicleModel in this case. You have to change the name to whatever the combobox is called in your form. Notice the single quotes inside the double quotes in the code above. Since the vehicle make is a character string, it needs to be enclosed in single quotes in the WHERE clause of the SQL statement that is being created as a string to serve as the rowsource of the second combobox. If this was a different application, and the selection in the first combobox was a number, the single quotes would not be necessary. If it was a date, then the single quotes would be replaced by #'s. You get the idea.

And the rowsource for the third combobox would be set in the afterUpdate() event of the second combobox as shown below:

Private Sub cboVehicleModel_AfterUpdate()

  On Error Resume Next

  cboVehicleTrim.RowSource = "Select distinct Trim " & _
           "FROM vehiclesTable " & _
           "WHERE Make = '" & cboVehicleMake & "' AND " & _
           "Model = '" & cboVehicleModel & "' " & _
           "ORDER BY Trim"

End Sub 

Notice how the rowsource of the third combobox filters the vehicles table by the information in both the first and second comboboxes. In most cases, this makes sense in a hierarchical system. If you want to filter only by the selection in the second combobox, you can change the code above as appropriate.

In the second setup, you design the form so that only the first combobox is visible (or you make all the comboboxes visible, but make only the first one enabled). For this example, we will assume that we have made cboVehicleMake visible and made the cboVehicleModel and cboVehicleTrim invisible. We will also set the rowsource of cboVehicleMake as in the first method:

select distinct Make from vehiclesTable order by Make 

We will leave the rowsources of the other two comboboxes empty. They will be set at runtime using VBA.

The afterUpdate() event of the first combobox would now look as below:

Private Sub cboVehicleMake_AfterUpdate()

  On Error Resume Next

  cboVehicleModel.Visible = TRUE
  cboVehicleModel.RowSource = "Select distinct Model " & _
           "FROM vehiclesTable " & _
           "WHERE Make = '" & cboVehicleMake & "' " & _
           "ORDER BY Model"
  cboVehicleModel.setFocus

End Sub 

Notice the two extra lines of code where we turn the visibility of the second combobox on, and then set the focus to that combobox after giving it a rowsource. It is trivial to extend this scheme to create the afterUpdate() event for the second combobox so that it makes the third combobox visible, sets its rowsource and then places focus on that combobox. If you have chosen to enable and disable comboboxes rather than play with the visibility of the controls, then you have to use "Enabled" rather than "Visible" in the appropriate lines of code.

To make sure that the second and third comboboxes become invisible (or disabled) once the user moves to a new record, you can put the following code in the form's Current() as well as OnOpen() events:

Private Sub Form_Current()

  On Error Resume Next

  cboVehicleMake.setFocus
  cboVehicleModel.Visible = FALSE
  cboVehicleTrim.Visible = FALSE

End Sub 

Private Sub Form_Open(Cancel as Integer)   
  On Error Resume Next

  cboVehicleMake.setFocus
  cboVehicleModel.Visible = FALSE
  cboVehicleTrim.Visible = FALSE

End Sub 

Instead of setting the focus on cboVehicleMake, you can set the focus to some other control. But it is important to make sure that the focus is set to some control other than cboVehicleModel and cboVehicleTrim because it is not possible to turn off visibility of controls which have focus. And, you can obviously use Enabled instead of Visible in the above code.

I like the second scheme slightly better than the first one because there is no chance of the user directly going to a lower level combobox without first filling in a value for the upper level combobox, using the mouse. You can add code on the gotFocus() events of the lower level comboboxes to check if the upper level comboboxes have a value, and if not, you can have the focus automatically moved to the upper level combobox if the user tries to click on the lower level combobox, but it is definitely more work.

Hope this post has been helpful in solving any problems you might have had with designing and/or using cascading comboboxes in Access. The VBA code in this post has been tested in Access 2003 and should work without any problems in all versions of Access from Access 97 on up. If you have any problems or concerns, please feel free to let me know by posting a comment. If you have other questions on Access that you would like me to address in future posts, please feel free to let me know through your comments too. Good luck!

Taming The Windows 7 Start Menu

Setting up a new computer is a bit of a chore for me (a not altogether disagreeable chore, but a chore nonetheless) because I like things a particular way, and I am not willing to settle easily for a less than perfect (from my viewpoint) setup. I had practiced the art of setting up my Windows XP environment on 3 or 4 computers over the years, so I could now do it in a couple of hours, with my eyes blindfolded!

Unfortunately, it was not quite such smooth sailing with my new Windows 7 computer. It took me a while to wade through the enormously higher number of customization options in Windows 7 to get just the look I wanted for my desktop, my taskbar, my notification tray, my desktop gadgets, etc., etc. And then there was the huge number of options to deal with in setting up my power profile, calibrating the display to my satisfaction, and seemingly a million other tiny details.

I now have a decent understanding of where things are and what options do what in Windows 7. It took a couple of books and several google searches, but things are now clear in my mind, so that setting up my next Windows 7 laptop setup will probably take a lot less time. But, fully a month after I got my laptop, I couldn't seem to get my hands around the Start Menu until this last weekend. I thought I would give others struggling with this monstrosity a hand, based on my experiences in finally getting it under control.

Just like in Windows XP, the Windows 7 Start Menu has a property sheet that can be reached by right-clicking on the Start button and clicking on "Properties". However, just like in Windows XP, this brings up just a small property sheet that allows you to customize the appearance of things that show up when you click on the Start button, before you go into the "All Programs" part of the Start Menu. The property sheet does not help you in any way with what shows up under "All Programs".

My main problem was with the "All Programs" part of the Start Menu. To understand why this was a problem, I have to explain a little bit how I set up my computer. In Windows XP, I always created just a single user with administrator rights on the computer. I used the computer as the administrator all the time, installing programs from this account, and using them from the same account. It was supposed to be bad from a security standpoint, but in Windows XP, limited user accounts were so cumbersome to use that the thought of creating one for myself and using that on a day to day basis never crossed my mind.

Things are supposed to be different starting with Windows Vista and certainly with Windows 7. Users are supposed to create standard accounts for themselves and use an account with administrator privileges only to do administrator-type stuff, such installing programs or changing system settings. UAC (user account control) was a bit of a pain in the neck with Windows Vista (this is pure hearsay since I never owned a computer with that operating system, and hope never to!), but Microsoft learned its lessons from that fiasco, and made it much easier to live with in Windows 7. So far, so good.

I followed the experts' recommendations, and created an administrator account (which I did not bother customizing extensively since I was going to be using it very seldom), and a standard account for my everyday use. The problem was that, every time I wanted to install a program, I would have to provide the installation program administrator access by typing in the administrator password when prompted. I did a few installations this way, but typing in the administrator password repeatedly got a bit tedious, so I decided I would do all my remaining program installations while logged into the administrator account. Seemed like a perfectly logical way to do things as far as I was concerned. After all, it is not as if I was installing programs on a daily basis. I was going to install the programs I needed, pretty much all at once, then log out of the administrator account and start using the computer under my standard account after that.

I did precisely that, and initially, I was quite happy with the results. A few programs specifically asked me whether I would like to make the program accessible to all the users on the computer, and I responded yes. The other programs never asked me anything and I did not give it much thought at that time.

I started using my computer under my standard account, and initially, did not notice anything amiss. But after a while, I started noticing that some programs were not under the "All Programs" in the Start Menu. I was about 90% sure I had installed the programs, but I thought I had perhaps missed a couple. So, I went to the administrator account and reinstalled them. Sometimes, I even found the program already installed under Program Files or Program Files (x86), but I thought the installation had somehow messed up and not created the appropriate Start Menu items. But reinstalling the programs did not seem to help in terms of making the programs accessible using the Start Menu in my standard account.

That was also when I noticed that some of these "missing" programs were, in fact, accessible using the Start Menu of the Administrator account. That was the a-ha moment that got me started down my path of discovery. It turns out that these programs had, without warning or notifying me, made themselves accessible only to the user installing them. You see, in Windows XP, your Start Menu is located under C:\Documents and Settings\USERNAME\Start Menu\Programs or under C:\Documents and Settings\All Users\Start Menu\Programs. Since there was only one account on the machine, it did not matter where each program installed its Start Menu shortcuts. If they were installed under either of these locations, they always appeared in the USERNAME's Start Menu.

In my Windows XP machines, just to be sure, I would normally go in occassionally and copy over any items under my user account's Start Menu to the All Users location so that I could go to a single place to customize the look of my Start Menu if I wanted to. I did not want program shortcuts hiding in various places all over the system.

Unfortunately, under Windows 7, many of the programs that I had installed from the administrator account had put their Start Menu shortcuts only in the administrator account's Start Menu, and not in the common Start Menu. Now, things were making a lot more sense. All that was left was to hunt down the different Start Menu's and do what I did in my Windows XP machines: move all the shortcuts to the common Start Menu rather than leaving them on the individual user Start Menu's.

If you are having problems with your Start Menu similar to what I was facing, or if you just want to clean it up anyways by regrouping shortcuts (like I sometimes do), read on. But before you proceed, make sure you have had a good night's sleep since you don't want to mess up when you are working in the bowels of the computer's file system (which is where the Start Menu's are). All of the places I am going to introduce you to now are hidden locations in the file system, and for good reason. You could end up mangling or entirely getting rid of your Start Menu programs if you do something without thinking about it, and Microsoft does not want people mucking around in these areas unless they know what they are doing. Given that caveat, let us get started.

First, you want to be logged into the administrator account on your computer. Most of the folders we are going to be touching need administrator access to make changes to, so it makes sense to be logged in as the administrator (this makes it doubly important to be sure about what you are doing because you are not going to be limited by your user privileges from making drastic or unwanted changes).

Next, since these folders are mostly hidden folders, you have to go into Windows Explorer and turn on the ability to view hidden files and folders. To do this, open up Windows Explorer, click on Tools->Folder Options. Move to the View tab, and double-click on "Hidden Files and Folders" if it is not expanded. Once expanded, you will find two options under it: either enable viewing of hidden files and folders or disable such viewing. Choose the option that says "Show hidden files, folders and drives", then hit OK to close out the options screen.

In Windows 7, the common Start Menu that will show up for all users is located under the following folder: C:\Program Data\Microsoft\Windows\Start Menu\Programs. This place will already be populated with a bunch of folders and shortcuts because most programs that are reasonably well-behaved do realize that they may need to be used by all users of the computer, so they install their Start Menu shortcuts here. Programs that ask you whether you want them accessible to all users also put their shortcuts here if you answered yes.

Each user also has a Start Menu folder. To find an individual user's Start Menu location, you have to navigate to C:\Users\USERNAME\AppData\Roaming\Microsoft\Windows\Start Menu\Programs. You will probably find a few folders and shortcuts under these locations also. These are the programs that appear only in the Start Menu of the individual users and not in the Start Menu of other users. I found a bunch of folders and program shortcuts in the administrator account's Start Menu location. These were not present in the common Start Menu location, causing the problem where my standard account couldn't access these programs from the Start Menu. I also found a few folders and shortcuts in my standard account's Start Menu location. These were programs I had installed when I was logged in as the standard user. In all cases, I consider these programs to be ill-behaved: they don't warn you about what they are doing even though what they are doing seems a little counterintuitive to me, and they don't provide any option to override their behavior, whether you consider it counterintuitive or not.

In any case, once you have all these locations, it is an easy matter to change things around however you want them. In my case, I just moved all the folders and short cuts from the individual user Start Menu's to the common Start Menu. Problem solved because I am the only user of my computer, and I do want all my program short cuts to be accessible from the Start Menu whether I am logged into my standard account or my administrator account.

If you have other needs, you can change things around. If you have multiple users on the machine, and you want some programs not to be accessible to some users from the Start Menu, you can easily move the shortcuts corresponding to such programs from the common Start Menu to individual user Start Menu's for instance. Or you can move some of the shortcuts of a program to some users' Start Menu's and move some others to a different user's Start Menu (for instance, the link to uninstall a program can be moved to the administrator account's Start Menu, while other links that allow use of the program can be moved to all other users' Start Menu's). You get the idea. Your imagination is the only limit when it comes to reorganizing the Start Menu of any given user.

Things to keep in mind when you do all of this: If the same folder and/or program shortcuts appear on both your user Start Menu as well as the common Start Menu, then when you click on the Start Menu and go to All Programs, you will find that folder and shortcuts duplicated. Windows 7 does not intelligently combine folders or shortcuts that it finds in these two locations. It just picks them up independently and shows them to you.

So, initially, when I just copied over folders and shortcuts from the administrator account's Start Menu to the common Start Menu, the administrator account's Start Menu had a bunch of duplicate folders and shortcuts. You have to move them (not copy them) to the common Start Menu to get rid of the duplicates.

Moreover, if you want a program to be accessible to some users but not other users, then you have to remove the folder and shortcuts corresponding to that program from the common Start Menu (if it is present there), and make copies of it in each user's Start Menu for every user who needs to access the program from the Start Menu.

Essentially, this is how Windows 7 seems to construct your Start Menu program list: take whatever is in the common Start Menu location, and add to it everything that is present in your own Start Menu location. Do not bother throwing out or otherwise processing duplicates, just show the final result, sorted alphabetically. Duplicates occur right next to each other, so they are easy to spot. And if you do spot duplicates, you know what exactly is going on now.

Even more important, remember that all these changes in your Start Menu folders and shortcuts only affects the ability of users to access programs directly from the Start Menu. It does not in any way affect their ability to just go to the installation folder of any program on the computer and run any program they want, whenever they want (unless you put security restrictions on the installation folders or executable files individually). Removing the Start Menu, even entirely, does not in any way affect anybody's ability to run any programs on the computer. The Start Menu is simply a convenient place to collect all the program shortcuts and organize them so that they are easier to access.

Moreover, the Start Menu should only contain shortcuts to the programs, not actual programs, so deleting something from the Start Menu does not uninstall the program or delete the program installation. If some primitive program that you install does not create Start Menu shortcuts at all, navigate to the program's intallation folder, copy the executable (right-click, select Copy), and paste a shortcut in the appropriate Start Menu location (right-click, select Paste Shortcut), and rename the shortcut as appropriate. I am sure you already knew that, didn't you?!

As mentioned earlier, be very careful when you are doing these changes to the various Start Menu's. I accidentally deleted the entire common Start Menu at one point, and ended up with a highly truncated Start Menu that consisted of just the items on my account's Start Menu. I almost froze in sheer panic as I considered how long it would take to recreate the full Start Menu by locating every program I had installed and recreating shortcuts to all of them in a newly created common Start Menu.

Luckily, I remembered to use Recuva to undelete what I had deleted, and saved my sanity! By the way, the "Programs" at the end of the path of the Start Menu's seems to be optional. My common Start Menu is now located at C:\Program Data\Microsoft\Windows\Start Menu\, and it works perfectly fine! It was not deliberate, I just forgot to create the folder "Programs" when I undeleted all the Start Menu folders and shortcuts. And since it worked, I did not bother creating the "Programs" folder after the fact and moving everything all over again. I was quite satisfied with screwing things up once!

All this trouble results from the fact that Windows has changed its security architecture to allow for the use of non-administrator accounts for everyday use of the computer, but most programs have not adapted to this new state of affairs. There still seem to be several ill-behaved programs out there that install their Start Menu shortcuts only for the user who installed the program. So, if you are like me, and install the program from an administrator account with the intention of using it as a standard user, you are going to find it missing from the standard user's Start Menu. Bad programming, plain and simple.

Ideally, every program should give the installer the option as to where the program's Start Menu shortcuts should be located. One should be able to select with checkmarks, all the users who should be able to access the program from the Start Menu. If "All Users" is checked off, the program should create its Start Menu shortcuts in the common Start Menu. If only a subset of users is selected, it should install a copy of its shortcuts in each selected user's Start Menu location. But until software makers adjust to the new security paradigm in Windows 7, don't look for this feature in the programs you install. Be prepared to clean up after your program installations by making manual changes to the Start Menu folders and shortcuts. At least, now you know exactly where to go and what to do. Good luck!

Oh, and before I forget, let me add one more important point to keep in mind: if you change the location of the Start Menu shortcuts created by a program for any reason (customizing the users to which the program is available, making it available to all users rather than just to the user installing it, etc.), you probably have to manually take care of cleaning up the Start Menu after you uninstall such a program. The program will, in all probability, only know to clean up Start Menu items from where it normally installs them by default, so if you moved those shortcuts around or copied them elsewhere, it is up to you to locate them and get rid of them after the program is taken off your computer!