Encrypt Your Data

Assigning permissions to tables does nothing to protect your sensitive data from anyone with a word processor. If you find Access security too cumbersome to use, Dima Mnushkin provides you with a simple solution for encrypting sensitive data. Along the way, you’ll be introduced to the costs and benefits of using Access encryption.

I don’t know what most people think about Access security, but I find it a royal pain in the neck. If all I want to do is restrict access to a form or module, I have to implement Access security and start popping aspirins. Ideally, I’d be able to secure a completed database once and not worry about it ever again. That never happens for me. Somehow there’s always one more form to tweak, bug to fix, report column to widen. Once I make that change, I have to adjust my permissions once again.

Largely because of my bad experiences with applying Access security, I was absolutely thrilled to find out that Access 97 could create MDE databases. What a godsend! No more messing around with permissions and database ownership–just choose Tools | Database Utilities | Make MDE, and presto! My database would be secure from tampering by adventurous users who think your form name should be more descriptive. But after some additional investigation, I was disappointed to find that all of my tables’ data was still completely open to anyone with a copy of Access. It looked like assigning permissions was still the only way of ensuring that unauthorized personnel couldn’t get at sensitive data. My next step was to find out what the Encrypt/Decrypt Database would do for me.

Access encryption

Some developers believe that implementing Access security will keep people out of sensitive information like salaries or passwords. This is simply not true. As a simple experiment, create an Access database with a one-field table. Enter the value “this is a test” into that field, set a database password, and close the database. Open the database with your favorite word processor and do a search for “this is a test.” You should find it easily. Now imagine that had been your boss’s name and the field right next to it contained his salary. Now imagine looking for a new job.

What this little experiment has demonstrated is that data within an Access database is easily accessible to anyone with a word processor, regardless of what permissions you’ve assigned to your objects or passwords you’ve set on the database. To make your database truly secure, it’s necessary to assign appropriate permissions and/or passwords and encrypt it. For this purpose, Access 97 provides the Encrypt/Decrypt Database function under the Tools | Security menu. I couldn’t find out precisely what type of encryption algorithm Access uses to protect each database, but it appears to be a form of symmetric encryption, likely DES.

The problem with database encryption is that it must be used in combination with regular Access security. An encrypted database without any database security can be decrypted by anyone with a copy of Access. Additionally, an encrypted database will run slower because of the extra overhead of encrypting and decrypting information all the time. Since Access isn’t the gazelle of development tools, slowing it down seems foolish.

Encrypting a whole database is often overkill. The average database doesn’t contain very much confidential information. Even a sensitive table like Employee has, at most, only one or two fields that must be protected. Thus the question becomes: Why encrypt (and therefore slow down) my entire database and enforce cumbersome Access security when all I want is to protect the values of two fields?

Encryption functions

A few months back, I was asked to protect employee passwords in an Access database. My client wanted the passwords hidden by more than just the “Password” input mask on the table field. The rest of the database hardly needed more protection than distributing it as an MDE. Under the circumstances, I was loath to implement both Access security and encryption (and I was all out of aspirin).

The only solution I could come up with was to implement my own encryption function that would reside in the MDE-protected module. I was faced with the difficult decision of selecting an encryption algorithm that was both easy to implement and tough to crack. Since I didn’t know much about encryption, I had to do some research on the Internet. I was overwhelmed by how much stuff was out there (see the sidebar “Encryption” for a brief synopsis of what I found).

Microsoft actually provides an API that performs encryption/decryption using the CryptEncrypt function. However, rather than re-using that perfectly good Microsoft code that had been well debugged and that included advanced encryption techniques, I decided to write my own. I was pretty sure I could write a reasonably secure function in less time than it would take to figure out how to use the API. As you can imagine, it took longer than I expected, but I learned a lot in the process.

The algorithm

One goal of my encryption function was to produce a different encrypted result for a given text value each time the function was run. In other words, when passed data, my function would produce an encrypted version of the data. If the process was repeated, a different version of the encrypted data would be produced. If you don’t do this, it’s possible for a hacker to take some known data, encrypt it, and compare the results to your encrypted data. Where your encrypted data and the hacker’s encrypted data match, the hacker knows what’s in your database. Another goal was to prevent users from getting a sense of the encrypted information by seeing clear word separation that could result from spaces being encoded to the same value each time.

I chose to implement a simple symmetric encryption algorithm that uses a single, developer-defined key to encrypt and decrypt information. In this algorithm, each byte in the data would have a byte added to it and so produce an encrypted byte. The byte to be added would be drawn from the developer-defined key. My function would draw the bytes one by one from the provided key. Once the last value in a key is used, the function will cycle back to the beginning of the key and continue encrypting until it runs out of data. This would ensure that even a common character, like a space, would rarely look like another.

A tougher challenge was to produce a different encrypted result each time the same value was passed through the function. To achieve this, it was necessary to begin at a randomly chosen position within the key each time. This would ensure that “baby” would encrypt to “_©”´È” the first time, “Ö™m£Ì” the second time, and so on. The more bytes in the key, the tougher it becomes to guess the key because there’s less repetition in the encrypted results.

While this isn’t an especially difficult algorithm to implement, it does pose a problem when decrypting the string. In order to decrypt the data, I have to know what starting position within the key was used to encrypt the data in the first place.

To solve this final problem, I had to store the starting position that was used to encrypt the data in the encrypted result (you might have noticed that the encrypted values for “baby” were one character longer than “baby”). Just to make things more interesting, I also encrypted the starting position (using the first key value) and inserted it into the middle of the result. With the function incorporated into my code and the appropriate data in the tables encrypted, my database was ready to be made into an MDE and distributed to my users.

The encryption function

You can find the encryption function in the sample database that I’ve included in the accompanying Download file. Here, I’ll walk you through some of the more interesting parts.

The first step in the encryption process is to establish the encryption key. It doesn’t matter what it is as long as it’s not longer than 255 letters and not empty. Here’s an example:

When the function is encrypting data, the routine begins by generating a random number between 0 and the length of the encryption key. This number is the start position in the key:

Once I’ve selected the start position, I encrypt it by adding the first byte in the encryption key to it:

The core of the routine is a loop that finds the next position in both the encryption key and the data. If the next position for the encryption key is past its end, the routine cycles back to position 1. The routine uses the Mid function to extract the characters at the current positions in the data and the key, converts them to numbers with the Asc function, and then adds them together to get the encrypted result.

If the result is a number greater than 255 (the largest value that can be converted into a character), I subtract 255 from it to get a number that I can convert to a character. The Chr function converts the encrypted result into a character, which I add to the end of the encrypted result. During this process, I check to see whether I’m halfway through the string and add the key’s start position:

Not surprisingly, the decryption process is similar to the encryption process. The first step is to extract the start position for the encryption key:

The next step is to subtract the value of the encryption key from the value of the encrypted data. If this gives a result less than zero, it indicates that I had subtracted 255 during the encryption process. I compensate by adding 255 before converting the result to a character:

The only other trick is to jump over the middle character because it contains the start position for the encryption key.

The function accepts two values: the string to encrypt as its first parameter and a Boolean value as its second parameter. If the second parameter is True, the function encrypts the data; if the second parameter is False, the function decrypts the data passed to it. You can call this function from a variety of places. The first line in the following code accepts a text box on the form and stores the result in a field in a Recordset. The second line reverses the process:

Known limitations

If you do use this function, you should be aware that it currently doesn’t handle numeric encryption. If numbers such as a salary must be encrypted, you must first convert them to strings and then store the encrypted result as a string. Also, it’s currently impossible to have a key longer than 255 bytes because the position is stored in a single byte whose largest possible value is 255.

Although Access encryption, in combination with Access security, allows for a truly secured database, it’s not always the best solution to protecting sensitive data. When deciding what method to use, consider how many fields need to be protected, the complexity introduced by adding Access security, and the performance hit taken by encrypting the database. Implementing a simple encryption function and distributing the database as an MDE will often be the most efficient way to ensure a reasonable level of security.

Your download file is called DECRYPT.ZIP 


Sidebar: Encryption

Encryption goes back a long way. It could be argued that the need to break encrypted messages was the single biggest push for the development of modern-day computers. The English began computing at Bletchley Park during World War II when they fired up their Turing Bombes and cracked the “unbreakable” German Enigma cipher. These machines were good examples of parallel processing, as each Bombe handled only a small portion of the total possible Enigma settings.

We’ve come a long way since those mechanical relays and whirling spools. Public-key encryption has revolutionized electronic communications by providing a remarkably secure method for the average person to communicate with anyone they wish. The constant advances in computing power and code breaking power are now only defeated by regular increases in encryption key size.

The dictionary describes encryption as “the activity of converting from plain text into code.” Encrypting data is the process of systematically modifying data to make the result illegible. Only the recipient of your message has the code or algorithm to decode the gobbledy-gook and restore the original message.

Symmetric encryption

Until recently, there was really only one way to encrypt information. You took whatever message you wanted to encrypt, used a secret key to systematically modify the message contents, and then sent the result off to the recipient. The recipient would also possess your secret key and use it to decode your message. This method is called symmetric encryption (or secret-key encryption) because both parties must have the same secret key or cipher. That’s also the weakest link in the security chain because getting the cipher to the second party without interception is often the most difficult part.

Currently, the best known implementation of this type of encryption is the Data Encryption Standard (DES). DES was adopted by the U.S. Department of Defense in 1977 as the standard for encrypting confidential information. Several versions of DES have been implemented, but the most common uses a 56-bit key (72 quadrillion possible values) applied to 64-bit blocks of data. It’s only recently that computing power has advanced to the point of cracking DES in a reasonable amount of time. By the year 2000, a new encryption standard called Advanced Encryption Standard (AES) is expected to be announced, promising large enough key sizes to remain unbreakable for another 20 to 30 years.

One interesting application of DES-style encryption is the ability to verify the integrity of encrypted information. Due to the encryption algorithm used, modifying a single bit anywhere in the encrypted data will prevent proper decryption of that information. This lets you verify that no one has tampered with the contents of the message since it was sent to you. Unfortunately, it does nothing to assure you that the right person sent the message! If your decrypted communication says to “push the big red button,” what guarantee do you have that the cipher wasn’t intercepted by the enemy, who is now sending bogus commands?

Asymmetric (public-key) encryption

To get around the problem of managing secret keys that’s built into symmetric encryption, Whitfield Diffie and Martin Hellman introduced the concept of public-key cryptography in 1976. This approach is based on each individual having two related keys. One key is a secret one known only to you, the person receiving the message; the other is a public key that’s given to anyone who might want to send you a message. If someone wants to transfer confidential information to you, they use your public key to encrypt their message.

Unlike symmetric encryption, a message encoded with your public key can only be decoded with your private key. You can’t use the public key to decrypt a message that was encrypted with the public key. With this method, there’s never a need to transfer your secret key to the recipient of a message. The weakest aspect of symmetric encryption is avoided. It’s because public key encryption uses two different keys to encode and decode messages that it can be referred to as asymmetric encryption.

Digital signatures

Now when the command to “push the big red button” comes, there’s still a problem. Who was it that sent the message? Anyone has access to your public key, so anyone can send you an encrypted message. How do you verify that the message was sent by “M”? The solution to this problem is called a digital signature. When M wants to send you a message, M first encrypts the message (or a small portion of it) with his private key before encrypting it with your public key.

When you receive the message and decrypt it using your private key, you get another encrypted message. To decrypt this one, you must use the public key of the person who sent you the message. Since this message must have been encrypted with M’s private key, which no one else in the world has access to, you can be reasonably certain that M sent the message.

Certificates and certificate authorities

What happens if you don’t know M’s public key? Who publishes public keys for everyone to see? How can you be certain that the public key published for M is really M’s and not someone else’s? To solve these problems, certificate authorities (CAs) have come into existence. These are organizations that certify the identity of a public key holder, in effect guaranteeing that the public key listed really does belong to M. If you trust the CA, then you can trust any public keys it lists.

To publish your public key with a CA, you have to supply personal information that can be used to verify your identity. Information such as SSN, tax number, e-mail address, and so on might be required before a CA will certify you and publish your public key. There’s no need to worry about your information being intercepted en route to the CA because you can encrypt it with the CA’s public key before transmitting it.

Posted in Other Topics | Leave a comment

Dual Control

In this article, Keith Bombard shows you how to implement dual control in Access to make critical operations more secure by ensuring that more than one person must approve a task.

This sample used workgroup files for getting usernames.  These are now not available in 2007 ACCDB format files. So invent a different username system and the code will work.

TEN years ago, I was a vice president in a big bank and wore a nice suit every day. Those days in the suits are long gone (and so is the bank, because itfailed!), and today I wear jeans, but I took a number of good lessons away from my experience in the financial world. One was the need for dual control. Dual control means that two people are responsible. Someone else, along with yourself, has to sign the check; two bankers have to open the vault; two managers must approve any raises, and so on. It’s a common practice in the financial world. What isn’t common practice is finding an Access database system that incorporates a dual-control approval mechanism.

In this article, I’ll refer to the user (the person who wants to perform a critical function and needs the approval of the manager) and the manager (who supplies the Approval Code and will authorize the process for the user). You’ll need to establish a user table in your application (if you haven’t already) and another table to store a list of the approved requests (auditors like to track everything). These two tables should reside on the network as attached tables. In the sample application in this month’s Source Code file (available at www.smartaccessnewsletter.com), you’ll find a sample database with two tables in it that you can review.

The process

Start by determining which Access process requires a dual-control approval. You’ll wrap the code for this process in an If…Then statement that uses a VBA function called Approve to return a True or False value. The Approve function returns a True value when the user obtains an Approval Code from his or her manager, allowing the critical process to be executed. Here’s a typical example:

What kind of process might require an approval? Deleting records from critical data forms is one example. To delete records, I usually use a delete button code behind the click event to delete records. I then nest my code inside the Approve block, thereby placing the entire delete operation under dual control.

When executed, the Approve function opens the dialog form frmObtainApproval (see Figure 1), passing a generated Request Code into the form as an OpenArgs value. The Request Code is a randomly generated number between 10000 and 10000000. The user is then prompted to enter the correct Approval Code, which is obtained from his or her manager. (The Get App Code button that appears in Figure 1 is included only as a sample to illustrate the call to the frmFindApprovalCode form. You must delete this button and associated click event code from the form if you want to use the form.)

Figure 1. Requesting an Approval Code.

Figure 2. Producing an Approval Code

Figure 3. The Got Approval message box.

The user must call (usually by phone) the manager to discuss a dual control request. Once agreement has been reached, the manager loads another form, frmFindApprovalCode (see Figure 2), and looks up the user’s most recent Request Code using the Lookup Request combo box on the form. Once selected, the appropriate Approval Code is computed, and the manager gives it to the user.

The user then enters the supplied Approval Code into the Approval Code text box. Behind the scenes, a second Approval Code is computed and compared against the value supplied by the manager. If a match occurs, the Approve function returns True, and all is well (see Figure 3). This process is secure as long as the user who’s requesting the approval doesn’t have access to the frmFindApprovalCode form.

The code

It’s virtually impossible to guess an Approval Code from a Request Code. The basApprove.CodedString function computes the Approval Code as the result of a computation process that uses the Request Code and a hard-coded 36-byte CharStr string variable.

The basApprove CodedString function is outlined in the following code. The function is seeded with a random long integer ReqCode to produce the resulting value:

The CodedString function begins by setting the CharStr constant that’s used to make sure that the resulting values are unique (more on this later):

The routine takes the Request variable that contains the random number passed to the routine and turns it into a string:

The routine’s main loop steps through all characters in the Request variable and computes the resulting KeyVal sum. This result is returned to the calling routine:

In this routine, each character in Request is matched against the characters in the string constant CharStr. When a match is found, a KeyVal value is incremented by multiplying the positions of the character in both strings times the value of the character being examined. KeyVal is increased until all of the characters from the original string are resolved, and then CodedString is finally computed as the product of KeyVal and the length of the original Request string variable, plus 1. To make your Approval Code different from any

other program’s Approval Code for a given Request Code (and hence unique), edit this function to scramble the order of the characters in the CharStr (but don’t remove any characters). This works because the algorithm uses the positioning of CharStr’s characters to compute the Approval Code value. I suggest you do this; otherwise, the auditors might get after you for using a coding routine to which lots of folks have the key.

I invite you to try out the sample database included with the article. Import the objects into your database, establishing the production tables (900 and 901 numbered tables) as network attached tables. Nest your critical functions under the Approve function and give the managers access to the frmFindApproval form. You’ll quickly establish a dual-control mechanism that your auditors will admire.

Posted in Other Topics | Leave a comment

Displaying Hierarchical Data in a TreeView

Thanks to the way that Windows Explorer handles folders and subfolders, your users are used to being able to work with hierarchical data using a TreeView control.  Glenn Lloyd shows how to incorporate a TreeView control into your application.

Relational databases typically use a hierarchical structure for data organization that reflects the real-world entities the database is modeling. Each database has a few top-level or parent tables. Most of the database tables, however, depend either directly or indirectly on these top-level tables. This tree structure provides an easy means to visualize both real-world entity relationships and relational database structure by using a TreeView control. Using a real-world example, this article shows how you can use the TreeView approach to displaying and working with hierarchical data.

Most Windows users have seen the TreeView control in action many times, although they may not know or care what it’s called. For example, the folder list in a Windows Explorer window is a form of the TreeView control (see Figure 1). Other examples include the online Help file tables of contents of many Windows applications, and the Outlook Folder view.

Figure 1

If you intend to use the TreeView control, you’ll need to do some initial planning before you begin. This planning will include determining the overall purpose of the display you’ll be developing, analyzing the data that will be used to populate the tree, deciding whether the tree data needs to be sorted, deciding how to handle events triggered when the user clicks a node, and planning for the maintenance of the control’s underlying data. To help you work through this, I’ll use an example based around the country/province structure of Canada (feel free to substitute the country/state, country/county, or country/region organization structure of your native land).

For this case, the TreeView will display in hierarchical order the political jurisdictions within the country, visually depicting how the various political levels relate to each other, along with the overall structure, and how informal low-level community components fit into the picture.

Constitutionally, Canada has three levels of political responsibility: Federal, Provincial, and Municipal (see Figure 2). Larger municipalities typically have wards or districts that may be further subdivided into neighborhoods or communities.

Figure 2

Using the TreeView to display this structure requires placing each organizational component of the political structure at an appropriate position in the tree. The TreeView itself is set up as a group of nodes. Every node, except for the top-level node, has a parent node to which it belongs as a child, thus forming the levels that my data needs. Every node can have subordinate or child nodes.

In my example, the top-level node is the country: Canada. All other nodes in the tree belong either directly or indirectly to the top-level node. Provinces belong directly to Canada, while municipalities belong directly to provinces and indirectly to Canada.

Once you’ve analyzed the underlying data and have determined the appropriate parent and child nodes, you can create a form and add a TreeView control to it, then populate the control with its initial data set. Additional steps will be required if the user is to be allowed to add new nodes or remove unneeded nodes.

Unlike the simpler controls used on everyday forms, TreeView controls can’t be bound to a data source. Instead, you must maintain the data using VBA code. In my example, the data to populate the tree will be hard-coded into a VBA procedure to simplify the code. However, the data could just as easily be drawn from one or more tables.

Creating a TreeView control

To add a TreeView to a form in Design view, begin by clicking on the More Controls button in the lower right-hand corner of the Toolbox to display a scrollable list of the various ActiveX controls installed on your computer. Scroll down through the More Controls list to find Microsoft TreeView Control (version 5 or 6). If you don’t find the TreeView control on the list, it may not be installed on your computer or it may not be properly registered in Windows (see the sidebar on installing the TreeView for how you can get a copy of the control). Once I’d added the control to my form, I sized it to be about 2.5″ wide by 3″ high and named the control TViewCanada. The result can be seen in Figure 3. So far the control doesn’t do anything spectacular; it merely occupies space on the form. Switching to Form view will show the new control as a blank rectangle.

Figure 3

The TreeView control has a set of custom properties that determine the control’s general appearance and behavior. For example, setting the Line style property to 1- tvwRootLines will display the expansion/collapse icon to the left of top-level nodes. Selecting the Sorted checkbox will cause top-level nodes to appear in alphabetical order. You can access the custom properties by selecting the control and clicking the Build button by the custom properties line on the Other tab on the control’s property sheet.

Now that the TreeView control exists and can be displayed, a little code is needed to manage the data it will display. The TreeView’s list is stored as a collection of items that belong to the object’s Nodes collection. Four methods and one property are available for managing and retrieving TreeView data:

  • Add–Add nodes to the collection.
  • Count–Determine the number of nodes in the collection.
  • Clear–Delete all nodes from the collection.
  • Item–Retrieve a specific item by its index value.
  • Remove–Remove a specific node from the collection.

In most applications, the Add and Clear methods are used most frequently. The other methods are used as needed. The Add method has six possible parameters. At a minimum, the index and text values must be specified. Except for top-level nodes (which have no parent by definition), you must specify the Relative and Relationship values. You may also specify two images that will be displayed in front of the node text, Image and Selected Image.

In the order they appear in the Add method’s argument list, here’s a brief description of each of the parameters to be passed to the method:

  • Relative–The node to which the new node will be related.
  • Relationship–Whether the new node is the child or sibling of the relative node. Not required for the first node (the root node) in the tree. Table 1 lists all the values.
  • Key–The value by which the node may be looked up and referred to.
  • Text–The text the control will display for this node.
  • Image–The image that will appear in front of the node.
  • Selected Image–The image that will appear in front of the node if it’s selected.

Table 1. Constants used with the Add method.

Constant Description
tvwFirst Add as the first node at the level of the relative.
tvwLast Add as the last node at the level of the relative.
tvwNext Add after immediately following a specified node.
tvwPrevious Add after immediately preceding a specified node.
tvwChild Add as a child to the specified node.

The code to add the top node (“Canada”) and the remaining provinces would look like this:

With the first two levels populated, the form with its TreeView control will look as it appears in Figure 4. As you can see, loading the control from a recordset wouldn’t be hard to do (in fact, looping through a recordset of all of the provinces would use less code than my example, where I needed 12 lines of code to add all the provinces and territories).

Figure 4

Part of TreeView’s functionality comes from its ability to display multi-level trees. All that’s needed is some additional code to add child nodes to the second level nodes that the control now has. If you pass the constant twChild as the second parameter to the Add method along with the Key value of an existing node, the new node will be the child of an existing node. Here’s the code to add some cities to the province of British Columbia:

You can now open the form in Form view to see the changes. The third level nodes will be represented by small plus signs inside a square and clicking one of the squares will expand the node. Each click on a second level node toggles any node with child nodes between expanded and collapsed.

Of course, populating the tree with anything more than a trivial amount of data using hard-coded values would be an exercise in frustration. The sample database includes a table of tree node information and a form, frmTview2, that populates the tree using an ADO recordset.

User interaction with TreeView

Selecting a node by clicking its text will trigger the Tree_NodeClick event. Unlike standard form controls, the property sheet for a TreeView object doesn’t list events specific to the control. Nevertheless, the form’s code can respond to the Tree_NodeClick event.

The skeleton for an event handler for the NodeClick event, complete with error handling, looks like this:

The form’s code can access six properties for the node: Text, Parent, Children, Index, Key, and Tag. My skeleton procedure includes an error handler because attempting to access a top-level node’s parent property will cause a runtime error.

In addition to accessing node properties, you may need to “walk” through a tree’s nodes. This can be done using a simple recursive procedure. This example reads all of the nodes in a tree so that you can retrieve all the data in the tree:

To use the routine, just call it and pass the node that you want processed (the routine will process the node and all of its children before it stops):

With this information in hand, you can add a TreeView to your forms and let your users start processing their data in a way that they’re already familiar with.

Posted in Other Topics | Leave a comment

Choosing Directories: Harder Than It Sounds

This month,  Ken Getz takes on but one question: allowing a user to choose a directory from a list of directories. He supplies the answer for both formats: Access 2.0 and Access 95/97. The 16-bit demonstration database is QA970416.MDB, and the 32-bit version is QA970432.MDB. The 32-bit database is in Access 95 format, but can easily be converted for Access 97.

As part of my application, I need to be able to allow users to select a path in which to save files. Currently, they have to type the full path, and then I verify that the path is valid. They all complain that it’s too difficult to remember the paths on their computers, and I agree. I tried using the Windows common File Open dialog box, but this doesn’t have an option to allow me to show only paths (and not files), as far as I can tell. Please help! This is driving me nuts.

You’re right — there’s nothing you can do to make the common dialog box show you only drives and paths. You could, of course, write code using the Dir function to fill a list box with all the directories, but it doesn’t help if you want to allow users to select drives as well. In addition, this method isn’t as fast as you might like.

It turns out that the Windows API can again come to your rescue. If you read the February 1997 edition of this column (“Sorting Arrays of Strings”), you’ll see that I previously used the Windows API CreateWindowEx function to create a list box that will sort items for you. You can also use the same technique to create a list box that fills itself with directory, drive, or file information. I’ll use that technique here.

In case you missed the previous column, the CreateWindowEx API function allows you to create a control of any built-in Windows type. You can make it visible or invisible, set its parent, fill it with data, and retrieve data from it. By specifying the LBS_SORT flag when you create a list box, you can cause it to sort its data alphabetically. Once you’ve created the list box, you can send messages to it (using the SendMessage API function) to add or retrieve specific items.

In addition to the techniques shown in that issue, you can use the SendMessage function to cause the list box to fill itself with a list of drives, directories, and files. Because you’ve created the list box with the LBS_SORT flag, it’ll automatically sort the items for you as well. Can you just place this newly created list box on an Access form and use it to allow users to select a drive or directory? Not quite. Although you could show the list box on a form (simply by setting its parent to be the form’s hWnd property when you create the list box using CreateWindowEx), there’s no reasonable way to react to any user events associated with the list box. That is, it would just show you the list of items, but it wouldn’t be able to do anything once you’d clicked on a specific item.

If you want to use a list box created with CreateWindowEx, you’ll want to use it invisibly, and pull all its items into a local array. Once you have an array full of drive and path names, you can use Access’ list-filling callback function mechanism to fill a real Access list box with the items the hidden list box found for you. For more information on using list-filling callback functions, see my column in the December 1995 issue (“How to Fill Lists, Sleep on the Job, and Tell When a Subform is Really a Form”). To demonstrate this functionality, try frmTestGetPath in the sample database. This form, shown in Figure 1, pops up frmGetPath, waits for you to choose a path, and then displays the selected location. The “chooser” form, frmGetPath, starts out at the current location and allows you to navigate to any other drive and directory.

Figure 1

The example includes several technologies that I’m not going to cover in detail in this answer — they’ve been covered previously, and aren’t the point of this answer. For example, as mentioned earlier, this solution uses a list-filling callback function to get the items from an array into the list box on frmGetPath. It also uses a common technique for displaying pop-up forms and retrieving information: A wrapper function, GetPath, in basFillDirList, opens frmGetPath as a pop-up form and waits for you to either close the form (using the Cancel button) or hide the form (using the OK button). This technique makes it possible to open a form, retrieve some information from a user, and gather that information once the user has finished with the form. [This technique was discussed in an article by Mike Gilbert in the February 1995 issue. — Ed.] In addition, the 32-bit version of the sample provides a public property of frmGetPath, the Path property, that makes it easy to retrieve the selected path. Because Access 2.0 doesn’t support user-defined properties on forms, the 16-bit version of the sample database requires its GetPath function to investigate the value of the txtFullPath text box on frmGetPath directly. Finally, to fully understand this solution, you’ll need to investigate the CreateWindowEx API function, discussed in my February 1997 column. If you’re missing that issue, you can also go back to the same source I did for this information: I used the MSDN CD to look up how Windows creates windows, and used the WIN32API.TXT file that comes with Visual Basic (it’s available free from a number of sources and is included in the accompanying Download file).

The code examples and screen shots in this answer are taken from the 32-bit version of the sample database. The 16-bit solution is quite similar, but details in the code were changed to accommodate the different features of the product. The explanation presented here still applies to the 16-bit version, but the code won’t quite match what you see here.

The GetDirArray function, in basFillDirList, is the key to this solution. This function, shown in Listing 1, allows you to pass in an array to be filled in and a starting path (optional in Access 95/97 where it defaults to the current directory). Once it has checked its parameters to make sure they make sense, it starts by creating the list box:

For information on all the various parameters, you’ll need to consult a Windows API reference, but the only one of concern to you is the LBS_SORT flag used in the fourth parameter. This indicates to Windows that this list box is to alphabetically sort all its entries. Because you’ll be filling this list box with directory names, it’ll sort those names for you. Because this list box is invisible, you don’t care about its parent or its location. (These are some of the other settings you can control using the parameters for CreateWindowEx.)

Listing 1. The GetDirArray function.

CreateWindowEx returns a window handle (a unique identifier) for the newly created control, and you’ll use that window handle with the SendMessage API function to tell the control to show directories and drives, but not files. If the window handle is 0, the only disallowed value, the code just exits. If it’s a legal value, the code continues and calls a version of SendMessage that has been aliased to allow it to send a string in its final parameter, named SendStringMessage:

In this line of code, the LB_DIR constant tells the list box control to fill itself with files, drives, and directories. The next parameter indicates which of these items the list box should contain. The DDL_DIRECTORY constant tells it to load directory names, DDL_DRIVES tells it to include drives, and the DDL_EXCLUSIVE tells it to load only the items previously noted. Without this final constant, the list box would also have loaded its default set of values: all the files in the selected directory. The final parameter indicates which path and set of child directories to load. (The FixPath function ensures that its parameter ends with a trailing “\” character. This function is called throughout this sample. In this case, it’s using the varPath value passed as a parameter to the function, indicating the directory in which to search.) The call to SendStringMessage returns the index of the final item it added to the list box. Because the list box item indexes are zero-based, the value is one less than the number of items in the list box.

The next step is to copy the items out of the filled list box into a local array. This makes it possible for the GetDirArray function to return an array filled with the items in the list box and to destroy the list box once it’s done. Certainly no other portion of your application should need to know how to retrieve the items from the hidden list box, so it’s best to isolate that technology in this routine. If the previous call to SendStringMessage didn’t place any items in the list box, intCount will be 0 and the code stops there. If intCount is greater than 0, however, it executes the following code:

This code resizes the array passed into GetDirArray to be large enough to contain all the items in the list box, and then “puffs up” the string buffer so it can hold up to 260 characters. To retrieve the items from the list box, the code then calls the SendStringMessage function once again, using the LB_GETTEXT message to cause the list box to return its items, one at a time. By specifying the index of the item you want to retrieve in the function call, SendStringMessage will return each item. All that’s left, then, is to use the Left$ function to extract just the portion of the text before the first null character.

Once you’ve executed those steps, you have an array full of all the drives on the machine, and all the directories under the selected directory. Just so you know, the list box returns directory names surrounded by square brackets (such as “[ADTC]”) and drive names in bracket/hyphen pairs (such as “[-c-]”). It’s up to your code to extract the portion you need, and this sample includes the FixEntry function in frmGetPath’s module to extract just the part you need. More on that later.

The sample form, frmGetPath, centers all its activity around the text box that displays the current path, txtFullPath. The form’s Load event procedure starts out by storing away the current path (so it can restore it when the form closes), and checking to see if code that loaded the form passed in a value in the OpenArgs parameter. If so, it uses that as the initial directory. If not, it uses the current directory. Either way, it sets the value of txtFullPath and then sets the RowSourceType property of the main list box on the form. By setting the property value, the form avoids problems that would otherwise have occurred when the function called by the list box to fill itself tried to retrieve a value from txtFullPath and found none there; setting the property value also causes it to requery the list box once there’s a value in txtFullPath. Here’s the Load event procedure:

The FillDirList function, used by the list box on frmGetPath to provide its list of values, is a standard list-filling callback function. It’ll be called by Access every time the program needs to get information about or provide a value for the list box. When you first set the RowSourceType property for the list box (in the form’s Load event), Access will come through this procedure with intCode set to acLBInitialize, calling the GetDirArray function discussed earlier. This function returns the number of items it placed into the array passed to it, and FillDirList stores that value away for later, in the intCount variable. Here’s FillDirList:

The other interesting chunk of code in FillDirList occurs in the acLBGetValue case. Access sends the acLBGetValue code when it needs to retrieve an item to display, and in this case it gets the item it needs directly from the array you filled in the acLBInitialize case. This explains why the array must be declared using the Static keyword: otherwise, it wouldn’t contain any data when Access tried to get the values to display.

The frmGetPath form contains two other interesting procedures: FixEntry and the double-click event procedure for the list box, lstPath_dblClick. The first, FixEntry, takes the selected value from the list box and, along with the current path, fills txtFullPath with the selected new path. If the selected value was a drive letter in the form “[-a-]”, the code pulls out the drive letter, tacks on a colon (:), and exits. FixEntry is shown here:


If the selected value was a path in the form “[newpath]”, FixEntry pulls out the directory name, tacks it onto the end of the current path, and returns that. If, for example, the current directory is this:

and the user selects “..”, this will be the return value from FixEntry:

The problem, then, is that you can end up with weird current paths such as this because the list box uses the “..” to allow you to move up a directory level:

Although this is a perfectly legal path, it looks odd. To resolve that path back to the correct, normalized display, the code uses the GetFullName function, found in basFillDirList and shown here:

The lstPath_DlbClick event procedure has three jobs:GetFullName uses the GetFullPathName API function to retrieve a normalized version of any path you send it. This API function takes into account the current directory if necessary, and returns the path you send it, but with a drive letter, and no “..” or “.” values in the path. Using the GetFullName function guarantees that the display of the path is always “clean.” (The 16-bit example can’t call the GetFullPathName function — it exists only in 32-bit Windows — so it must use a slower, “brute-force” technique. It simply stores the current directory, changes to the new location, and then uses the CurDir function to do the normalization. Finally, it switches back to the original location. Slow, but effective.)

  • Fill in the value of txtFullPath.
  • Change the current location to the selected drive and path.
  • Requery the list box.

There’s nothing unusual about the code, but it has to happen in order for the list box to be able to navigate correctly:

So that’s it! This example uses the CreateWindowEx function to create a list box every time you move to a new location and fills the list box with the local directories and all the drives. It then copies the items from the hidden list box into an array for later use. The pop-up form’s list box takes the items from the array and displays them. Once you make a choice by double-clicking on the list box, it moves to the new location and starts all over again.

Windows 95 issues

The 32-bit version of this solution works beautifully under Windows NT. Unfortunately, I discovered a problem running it under Windows 95: long paths are not handled properly. In fact, if the starting directory contains a long path, you might be unable to navigate correctly on that drive. I made several attempts to get around these issues, but was unable to find a fix. Thus, if you plan to run the 32-bit version under Windows 95, you might wish to use an alternate solution.

There are other alternatives to solving this problem, of course. As I mentioned earlier, you can perform all this work using the Dir function (not in Access 2.0, however; this won’t work with Dir there). But that’s slow, and requires more code. Another alternative is to use the same tools the Access Setup Wizard uses. The Setup Wizard uses the SWU2016 (or SWU7032) DLL, and you can call functions in that DLL as well. Microsoft Access MVP Steve Thompson worked out the 16-bit version, and he and I collaborated on the 32-bit version (see SDIR16.ZIP and SDIR32.ZIP, which are included the accompanying Download file). This method still requires a bit of code (which is worked out for you in the examples), but to take advantage of these packaged solutions you must own a copy of the Access Developer’s Toolkit that corresponds to your version of Access. Because this technique depends on a DLL that’s only shipped with the Developer’s Toolkit, you won’t be able to use SDIR16 or SDIR32 unless you own a copy.

Whichever technique you use, you should now be able to present users with a list of directories and drives, allow them to navigate to the path they want to use, and return that path back to your application. This is the second solution I’ve come up with in the past few months that uses the CreateWindowEx function to create a standard Windows control to use as a crutch to work around an Access limitation. I wonder how many more uses there are for this technique?


SideBar  – Directories and Files Keyword Summary

Action VBA Commands
Change directory or folder. ChDir
Change the drive. ChDrive
Copy a file. FileCopy
Make directory or folder. MkDir
Remove directory or folder. RmDir
Rename a file, directory, or folder. Name
Return current path. CurDir
Return file date/time stamp. FileDateTime
Return file, directory, label attributes. GetAttr
Return file length. FileLen
Return file name or volume label. Dir
Set attribute information for a file. SetAttr

SideBar 2 – Copying all Files in A Directory

This command can work, test the command by using Run on the Windows menu and typing cmd

Posted in Other Topics | Leave a comment

Access Interfaces: Building Explorer-style Forms

This month, Rebecca Riordan looks at the surprisingly simple process of implementing an Explorer-style form architecture using the TreeView component that’s part of the Windows XP Common Control Library.

While I admit I’ve never done a formal survey, I suspect that the vast majority of forms implemented in Microsoft Access conform to one of two architectures: Either the forms display single records from a recordset, or they display records from multiple recordsets in a one-to-many relationship, typically with the many-side records displayed at the bottom of the form. Microsoft Access makes these basic form layouts easy to implement, and they’re certainly appropriate for the majority of situations. Continue reading

Posted in Other Topics | Leave a comment