Category Archives: MS Access Tables

MS Access Database Tables

Is Indexing Boolean Fields Worth It? Performance Insights and Best Practices

Indexing boolean fields in databases is a topic that often generates debate among database administrators and developers. The primary question revolves around whether the performance benefits of indexing these fields outweigh the potential downsides, particularly in terms of write performance and storage efficiency. This article explores the nuances of indexing boolean fields, examining when it is advantageous and when it may be counterproductive.

Understanding Boolean Fields

Boolean fields are data types that can hold one of two possible values: true or false. Given their binary nature, one might assume that indexing them would be straightforward; however, the effectiveness of such indexes can vary significantly based on several factors, including data distribution and query patterns.
 
Continue reading →

Utilizing In-Memory Virtual RecordSets in Microsoft Access

Some time ago, I wrote an article about creating Virtual Queries, which you can find here:

Access - Virtual Query
It doesn’t happen too often, but in certain scenario it may be necessary to build a query based on static values. I don’t know if there is a technical term that already exist, but I’m going with the term ‘Virtual Query’, ‘Static Value Query’ or ‘Hardcoded Value Query’. Say you perform a series of data manipulation/extraction and wish to push these static values out as a query for use in other functions. So how can this be done? A Concrete example: For use in the SQL Code below I am trying to build a query around the following data Well,…

Continue reading →

Today, I’d like to delve into the topic of Virtual Recordsets, commonly known as In-Memory Recordsets.

While working on a personal project, I needed to display a list of files without repeatedly importing this data into Access. Constantly importing would lead to unnecessary data duplication and bloat in the database. Instead, I realized that using a Virtual Recordset would be an ideal solution. This approach allows me to manage and manipulate the list of files dynamically without cluttering my Access database, ensuring that my application remains efficient and responsive.
 
Continue reading →

Microsoft Access Multi-Valued Fields: A Deceptive Feature Best Avoided

Microsoft Access, a popular database management system, introduced Multi-Valued Fields (MVFs) as a feature to simplify the handling of many-to-many relationships. At first glance, MVFs appear to offer a convenient solution for storing multiple values in a single field without the need for complex table structures. However, experienced database professionals, Access MVPs, and seasoned developers strongly advise against their use. This article delves into the reasons why MVFs should be approached with extreme caution and, in most cases, avoided entirely.
 

Understanding Multi-Valued Fields

Before we explore the pitfalls of MVFs, it’s essential to understand what they are and how they work:

  • MVFs allow multiple values to be stored in a single field.
  • They appear to simplify many-to-many relationships without explicit junction tables.
  • Behind the scenes, Access creates a hidden system table to manage the multiple values.

While this may seem like a clever solution, it introduces a host of problems that can severely impact database performance, integrity, and maintainability.
 
Continue reading →

Understanding Data Normalization: Bringing Order to Database Chaos

Data normalization represents a sophisticated approach to database design that transforms chaotic, unstructured information into a streamlined, efficient system. Much like an architect carefully plans a complex building, database professionals use normalization to create robust, intelligent data structures that can adapt and scale with organizational needs.
 

Understanding the Data Dilemma

Imagine a small business tracking customer orders using a single, massive spreadsheet. Every time a customer places an order, their complete details are rewritten: name, address, contact information, along with product specifics and pricing. This approach quickly becomes a nightmare of redundancy, inefficiency, and potential errors. Normalization emerges as the elegant solution to this complex problem. It’s not just a technical process, but a strategic methodology for organizing information in a way that minimizes redundancy, maximizes data integrity, and creates a flexible foundation for future growth.

Consider a scenario where a customer’s telephone number is repeated with every single order. If that customer changes phone numbers, you’d need to update dozens or hundreds of records manually. Normalization eliminates this complexity by storing core information in a single, authoritative location.
 
Continue reading →

How-To Copy a Table’s Structure Only Without The Data

Here’s an interesting question that I was asked today:

I want yo copy only structure, empty table without data?

We immediately think of CopyObject method, but sadly, Microsoft never included the option to include/omit the data. So it always includes the data. Yes, you could use it non the less and then clear the table of the data afterwards, but this will cause unnecessary bloating and require a Compact and Repair to complete the process properly. It remains an option, but there are better approaches, so let’s briefly explore a couple of them.
 
Continue reading →

How-to Create Linked Tables Via VBA

Although we have GUI tools to create Linked tables, at times, it is simply more convenient to do so via VBA and automate the process.

So I thought I’d quickly share my routine for doing so. I’ve made it primarily for 2 usage scenarios, creating linked tables to:

  • Microsoft Access databases
  • SQL Server/Azure SQL databases

Continue reading →

How-to Change a Table Field’s Data Type

I thought I’d share a function a created a while back to help a forum user out who wanted to update the data type of all matching table fields throughout their database.

So, yes, we could manually alter the data type via the Table Designer.

Obviously, we could perform such an operation using SQL using an SQL Statement following the basic syntax of:

ALTER TABLE [YourTableName] ALTER COLUMN [YourColumnName] YourDesiredDataType;

Therefore, a concrete example would be:

ALTER TABLE [Contacts] ALTER COLUMN [Active] INT;

But this will need to be performed manually for every matching field in every database table of interest. The goal was to create a VBA procedure to perform this command on every instance.

Easy enough!

Continue reading →

Recover a Deleted Microsoft Access Table

Have you ever accidentally, or one of your users, deleted a table?

Need to bring it back? Well, it can normally be recovered.

Don’t close the database, don’t compact the database, don’t do anything else.

The first thing to try is a simply undo (Ctrl+z). In many instances this will work to recover the table(s).

If not, then simply copy the following function into your database and run it.

I take no credit for the original code, instead, all I’ve done is modify it to be able to recover multiple tables instead of just the last one.

Enough talk, here’s the code:

Continue reading →

Microsoft Access Compact and Repair

What is Compact and Repair (C&R)?
What does it do exactly?
Why do most experience developers recommend it so much?

Well, these are some of the questions that I am going to try to answer in this article. So let’s take a closer look at Microsoft Access’ Compact and Repair command.

Continue reading →