List Table Fields

I recently needed a way to list all the table fields from an Access database project, so I could export it into Excel and format it for the user's review (just to make sure I covered all the user's requirements). Unfortunately, all I found, looking around for a utility to do it, was a way to print a report showing all the fields. Unless I simply miissed it, I couldn't find one that exports it to Excel for further consumption. So, I decided to create this demo.

This demo is similar to my other "list" demos where you get to browse for the database file and clicking a button will populate a temporary table with all the information gathered from the selected database. You can then export the table's data into Excel, as desired. The only difference with this new version is that it should be able to handle a password-protected file. If the selected file is password protected, the user is presented with a prompt to enter the password.

To download this demo, please click on the image below. As usual, if you have any comments or suggestions for improvements or found any bugs with this demo, please do not hesitate to let me know. I would really appreciate it.

(click on the above image to download the demo file)

Thank you for reading!

List Files in a Folder and its Subfolders

While trying to answer a question in the MSDN Forums, I realized although there are already a lot of examples on how to list all the files in a folder or directory, I thought this was challenging enough for me to give it a try.

The original poster's request was to allow the user to search the folder for files matching a keyword and a list of specific file extensions. Also, the search should include all files inside subfolders.

Many of the examples I have seen use a loop to go through the folder structure and list all the files in them. Since I am a bit fascinated with recursion, I thought I would give it a try using a different approach. As a result of my attempt, I have created this demo.

Click on the above image to download the demo.

To use this demo, the user selects a starting folder path and enter an optional search keyword or list of file extensions to search. The search keyword is used to match the file names, while the list of file extensions will return only the files with matching file extensions.

The demo presents the result of the search using a Listbox. Double-clicking on an item in the Listbox will attempt to open the file for viewing. This version of the demo (v1.0) currently uses a Value List in the Row Source of the Listbox, which limits the number of files allowed to be displayed. The demo can be modified to store the file names in a table to allow the Listbox to display more items. I might do this in future versions of the demo.

In the meantime, I hope you find this demo useful, or at least, find the approach a little interesting.

As usual, I would appreciate any comments or feedback regarding this demo.

Cheers!

Normalization

This month, I decided to go back to basics. I know there are already plenty of articles regarding this subject. But since I am about to give a lecture about this topic to a small group of people this week, I thought I might as well post it here, so I can use it as a quick reference in the future.

What is Normalization?

Normalization is the process of designing a database table structure that organizes the data to minimize data redundancy and facilitate data integrity. The main objective of "normalizing" the data is to isolate the information so modifications to the data can be performed in just one table and then propagate throughout the database by means of related foreign keys.
There are defined "rules" to follow when going through the normalization process. When a database is properly normalized, it is considered to be in "normal form." For example, a database complying with the first rule is said to be in "First Normal Form (1NF)." Although there are several levels or rules for normalizing a database, most developers will attempt to comply only up to the "Third Normal Form (3NF)." 3NF is considered to be the highest level necessary for most database applications in use today.

First Normal Form (1NF)

A database must comply with the following rules to be considered 1NF:

1. Eliminate repeating groups in individual tables
2. Create a separate table for each set of related data
3. Identify each set of related data with a primary key

Example: The following table violates 1NF.

Second Normal Form (2NF)

A database must comply with the following rules to be considered 2NF:

1. All the rules for 1NF
2. Create separate tables for sets of values that apply to multiple records (duplicates)
3. Relate these tables with a foreign key

Example: The following table is in 1NF but violates 2NF:

Third Normal Form (3NF)

A database must comply with the following rules to be considered 3NF:

1. All the rules for 1NF and 2NF
2. Eliminate fields that do not depend on the key

Example: The following table is in 2NF but violates 3NF:

How can I remember all that?

Here are the main ideas to help you remember each normal form:

1. 1NF - Eliminate repeating groups (e.g. Price1, Price2, Price3)
2. 2NF - Eliminate duplicate or redundant data (e.g. multiple records with same values)
3. 3NF - Ensure all fields in the table are dependent on the primary key (e.g. city is dependent on the venue and not on the artist)

How do I fix this table structure?

The following tables show a properly normalized structure:

If you can think of a better example, please let me know. Thank you.

Original post date: January 26, 2016