Say UNC, API Calls, {CAPSLOCK}, and Time

<< Click to Display Table of Contents >>

Navigation:  Smart Access 1996-2006 > Oct-1998 >

Say UNC, API Calls, {CAPSLOCK}, and Time

Russell Sinclair            
 
In this month's "Access Answers," Russell looks at some problems with storing Word documents in a database, turning off the CapsLock key, and calculating the time between events.
 
I recently created a database that needs to store information about Word documents located on a server. Rather than embedding the file in the database, I decided to just save the path to the file in the table and allow users to open the file using the Shell command. When users create a new record, they often use the relative path to the file (the mapped drive letter) rather than the UNC path. Unfortunately, on our network, different users don't necessarily have the same drive mapping. Is there a way I can still allow my users to save a file using the relative path name but store the UNC path in my database?
 
This problem brings up a common question that Access programmers have: "When referencing files from within a database, do you save the path name to the file as a string field, or do you save the file as an OLE Object field?" The answer is, "It depends." It really depends on how much space you want your Access database to take up, how many files you're dealing with, and whether you can cope with the slower performance resulting from OLE embedded files.
 
I personally have never used an OLE field in any of my databases. I don't like the lack of control I have over them and the amount of space they can take up. For me, it's always been better to store the relative path to a file. The relative path name for a network drive is the one that looks like a drive letter (for instance, "F:"). Unfortunately, the same network drive can be one user's F: drive and another user's K: drive. So what do you do if a user saves the path information using the relative path to a file from their machine and it's not the same for all users?
 
The answer is to use the drive's UNC (Universal Naming Convention -- a standard for referring to network resources like drives and printers). The UNC information doesn't change from machine to machine, so long as the computers are on the same network (LAN or WAN, it shouldn't matter). However, when you type in the name of a file or use the Common Dialog to select one, the filename isn't necessarily in the UNC format. Fortunately, you can use a Windows API call to solve this problem.
 
What you really need to do is get the UNC mapping for the drive the user is referring to, not for the file being saved (since the file might not exist yet). Once you have that information, you can extrapolate the rest of the information from the file's path name as supplied by the user. The API call that allows you to do this is called WNetGetConnection and is found in MPR.DLL (the Win32 Network Interface DLL). You declare the API call by adding the following code to the Declarations section of a module:
 

Declare Function apiWNetGetConnection Lib _

 "mpr.dll" Alias "WNetGetConnectionA" _

 (ByVal strLocalName As String, _

  ByVal strRemoteName As String, _

  ByRef rlngRemoteNameLen As Long) As Long

 

The declaration of the call specifies what information it needs to be passed to it for it to do its job:

strLocalName: The drive letter followed by a colon (for example, "F:")

strRemoteName: A variable that WNetGetConnection is to load with the UNC path for the drive letter

rlngRemoteNameLen: The length of the name that's loaded to the variable strRemoteName.

 
I've written a VBA function called GetUNC that avoids the problems often involved in using API calls. Instead of calling WNetGetConnection directly, you can copy the GetUNC function (Access 97 only) to your program and call it from your code. For those of you who have some experience with API calls, you'll know why I did this. For those of you who haven't used API calls before, the GetUNC function is a very good example of why you should always wrap API calls in your own custom function.
 
To begin with, the first parameter to the API call requires that only a drive letter and a colon be passed to it. If you try to send the whole path to a file, the function will return an error. Second, the parameter strRemoteName that receives the UNC of the drive in question must be filled with spaces before you make the call. And it must have enough spaces to hold the largest string that the API call could return (in this case, that's the maximum length of a network path under Win32 -- 260 characters). API functions can't dynamically resize a VBA string to fit the necessary information in the same way that VBA can. Third, DLLs, like MPR.DLL, written in C usually return string values with a null character terminating the returned value. Not surprisingly, then, the value returned in strRemoteName is a null-terminated string that must be converted to a simple string for you to use in your program. I do this for you in GetUNC by using the supporting TrimNull function, which I also wrote. Finally, since WNetGetConnection returns only the UNC for the drive, I still need to add the remainder of the file's path name to the UNC for the drive. Using the wrapping function saves me from having to remember all of this each time.
 
Once you've copied it into your program, you can use my wrapping function like this:
 

strUNCPath=GetUNC(strMappedPath)

 
 
Once you've retrieved the UNC path, you can easily store it in a field -- along with the relative drive mapping, if you like -- and supply it to the Shell function to activate the document.
 
Although Access doesn't necessarily pay attention to the case in which data is entered, I'd prefer it if, for some of my databases, users entered text in all uppercase. I've tried using SendKeys "{CAPLOCK}" to turn on the CapsLock key, but it doesn't seem to work. Is there a way to force the users to enter information in all uppercase?
 
Many people will tell you that you shouldn't be trying to turn on the CapsLock key when you can simply reformat the string after it's been entered. The UCase function, for instance, will turn all of the characters in a string into uppercase. However, there might be more to your situation than I'm aware of, and besides, your question allows me to discuss some interesting features of the SendKeys function along with another API call.
 
The reason using SendKeys doesn't work in turning on the CapsLock key is because it isn't supposed to. The SendKeys function resets all keys to their original value after the function has completed. If you try the follwoing code, the CapsLock light will flash for a moment and the text "ABCDEF" will be typed in:
 

SendKeys "{CAPSLOCK}abcdef"

 
 
However, if you try this code, the CapsLock light will flash for a moment and the result will be "abcdef".
 

SendKeys "{CAPSLOCK}"

SendKeys "abcdef"

 
 
To quote the infamous programmer's response, "It's not a bug, it's a feature." So how can you force the CapsLock key to turn on and stay on?
 
The Windows API provides a very useful function for this: SetKeyboardState. This API function takes a variable of type KeyboardBytes (defined in the Windows API) as its only parameter. The KeyboardBytes type is an array of 256 bytes, with each member of the array defining the state of a key on the keyboard. Depending on the keyboard and the language used, not all elements will map to keys. The API defines a number of constants that indicate which element of the array is to be used for each key on the keyboard. Some of these constants are duplicated as constants defined in VBA. Each relevant constant in VBA has a name that starts with vbKey followed by the name of the key. For the CapsLock button, the constant is vbKeyCapital.
 
In order to avoid changing the current state of the keyboard along with the key you need to change, there's an accompanying API call -- GetKeyboardState -- that will load a variable of type KeyboardBytes with the current state of the keyboard. Once again, I've created a wrapping function, this one called SetKeyState (Access 97 only). The function is quite simple and requires only that you pass it the name of the key you want to change and the value you want it changed to. The function loads the current settings of the keyboard to variable kbKeyboard, changes the supplied key to the supplied state in kbKeyboard, and then makes the API call to force the change in the keyboard. To turn the CapsLock key on, call the function as follows:
 

SetKeyState vbKeyCapital, True

 
 
If you want to turn another toggle key on, such as the NumLock key, just supply the function with the VBA constant for the key in question. Don't try to use the function to turn a non-toggle key, such as a letter, on or off. It doesn't do that.
 
You should be aware of a couple of the function's characteristics. Turning a key on won't prevent users from turning it off again. You can, however, check the state of the key every once in a while and correct the setting if the user has changed it, although this is extremely inefficient. Also, the Access status bar won't update to show the correct status of the button. However, the light on your keyboard should turn on (or off) properly. I also recommend that you declare global variables to hold the state of the key in question before you change it and then reset the key to its original state when the user leaves your database or moves the focus to another application.
 
We have business requirements that define turnaround times between the time a product is ordered and the time the customer should receive it. However, these turnaround times are based on the number of business hours between when the product is ordered and when it's due. For example, suppose a customer places a rush order. The product is due in four business hours, and the order is placed at 3:00 p.m. Therefore, the customer should receive the product at 11:00 a.m. the next business day (based on a 9-to-5 workday). Is there a function in Access that will perform the necessary calculations so that we know what time the product is due?
 
When I first received this question from an Access user in my own company, I had a strong feeling that I had seen the question asked and answered many times in the Access Internet newsgroups. I proceeded to the DejaNews site and executed a number of searches looking for words like business, date, work, hours, and the like. No matter what I searched on, the results were always geared toward calculating the number of business days between one day and the next. After much frustration, I decided that what I'd seen in the past was exactly what the search was returning, and I wouldn't find the answer to the question on the Web. I was forced to work out the answer on my own.
 
As a programmer, I'm often handed technical requirements that require the use of some very complex mathematics. Usually, the formulas are already defined and I just have to convert it into code. In this case, I found that I had to turn to the math skills I hadn't truly used in years. At first, I thought the solution would be quite simple.
 
My first solution to the problem involved dividing the number of hours specified in the turnaround by the number of hours in a workday to get the number of elapsed days. I thought that I could then use the DateAdd function to add the number of days to the initial date. This sounds reasonably logical, doesn't it? Well, it isn't. For instance, if the number of days to add contains a decimal value, the DateAdd function rounds the number to the nearest whole number before processing the function. It became obvious that I would have to determine the number of whole days and use the remainder to calculate the number of hours. This cascades down through all the processing: I had to get the number of whole hours, and then use the remainder to calculate the number of whole minutes, and so on. When I tested the function, it still didn't work.
 
The first problem was that the days I was working with weren't all weekdays. I had to correct the function to allow me to get the number of hours in a week. Once I had this number, I could then continue the logic down the line to retrieve all of the values for each time increment: weeks, days, hours, and minutes. I didn't use seconds, but if you follow the logic of the function, you can easily add it in yourself.
 
The second problem I ran into was that the function didn't properly recognize the end of the day. This is because it would add hours to the time supplied and not carry the remainder over to the next day. The solution to this was to figure out how many working hours there were between the beginning of the week (end of the week for negative values) and the time supplied. The result could be added to the number of hours in the turnaround period. In other words, I was now calculating the time from the start of the week rather than just from the start of the turnaround. In my resulting function, the start of the turnaround can be a positive number (hours from the beginning of the week) or a negative number (hours to the end of the week). Rather than print all the code here (you can find Access 2.0 and Access 97 versions in the accompanying Download file), I've included a summary of the logic I used for the function in Table 1.
 
Table 1. The logic for calculating business dates using elapsed hours.

Step

Description

1

Determine the start/end of the week

2

Find the number of whole weeks to add

3

Determine the remainder of whole days to add

4

Determine the remainder of whole hours to add

5

Determine the remainder of whole minutes to add

6

Use the DateAdd function to add the number of whole weeks

7

Use the DateAdd function to add the number of whole days

8

Use the DateAdd function to add the number of whole hours

9

Use the DateAdd function to add the number of whole minutes

 
 
The function doesn't take holidays into account. To add holidays, you'd need to create a table of valid holiday dates for your company and use a SQL query to count the number of holiday days between the original date you supplied and the resulting date from the function. You could then add the number of holidays in days to the end date, using the DateAdd function. You can find my solution in basWorkHours in the sample database. Typical code to call the function would be:
 

datReturn = WorkingHoursAdd(Now(),4,9,17)

 
The first parameter is the date to add to, the second is the number of hours to add, and the last two parameters are the start hour and end hour of the business day (in 24 clock notation).
 
 

 
Your download file is called SA9810AA.ZIP  in the file SA1999-10down.zip

This is found in theon this page
 
 
Sidebar: Foundation Concepts
 
An API (Application Programming Interface) is a way of calling the functions that make up another program. Using a Windows API call allows you to access functions built into Windows that aren't available through VBA. You must declare all API calls before using them by using the Declare statement, specifying which library the code for the function is stored in. VBA's Shell function lets you start another program (like Word) and pass it the name of a file to open. An OLE Object field is a field in a table that stores data that requires a specific program to interpret it (an OLE Object field could hold a Word document, for instance). The VBA SendKeys function mimics someone typing at the keyboard and lets you perform activities that can only be done by some typing.

 

Other Related Articles