Tag Archives: MS Access 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 →

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 →

Microsoft Access Database Bloating Main Causes

I recently released a YouTube video regarding the Compact and Repair command.

My objective with that video was to only summarily touch upon the command, how to run it and explain the 2 main benefits. I was aiming for a video for new users, and introduction to the subject.

I received a negative comment stating that I didn’t explain the causes of bloating …, I didn’t delve into the details of the issue enough.  Thus, I thought that today I’d start trying to cover some of the more known root causes of database boating and will most probably create a follow-up in the future to discuss this further.

Continue reading →

Constraints in Microsoft Access

Handcuffs

Were you aware that Microsoft Access ‘supports‘ applying constraints? Here’s what you need to know!

Things of Note Regarding Microsoft Access Constraints

Before diving into examples, I think it is best to talk about some of the particularities of Access constraints.

Continue reading →

OLE Object Data Type – Just Don’t Ever Do It!

Package (Open Box)

I know I’ve made posts in the past regarding not using attachments

Adding Attachments to an Access Database
When Access 2007 came out Microsoft introduced a new Attachment data type to allow users to insert attachments within their databases. I urge you to NEVER use Attachment data types for a variety of reasons, but mainly: Leads to database bloating Can impact performance You need code to interact and extract the attachments from the database Instead, the tried and tested approach of simply storing the file path and filename (with the extension) remains the preferred technique to use.

but in case it wasn’t clear in that post let me extend the advice to include ANY embedding of files within a database by ANY means.  Thus, please, please, please, NEVER use the OLE Object data type to save files within a database!

It always seems like a brilliant plan at the time, but I PROMISE you that it will nip you in the ass down the road!

Continue reading →