Tag Archives: MS Access

Decoding the Cryptic ADODB Virtual RecordSet Error Message

Have you ever encountered an error message that left you scratching your head? Let me share a perplexing experience I had recently while testing a database application.

I was working on a personal project in which I decided to use Virtual Recordsets, upon which I created my most recent article:

Utilizing In-Memory Virtual RecordSets in Microsoft Access
Some time ago, I wrote an article about creating Virtual Queries, which you can find here: 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…

Continue reading →

and during some testing I started to experiencing issues.

The Mysterious Error

Out of nowhere, I started receiving this cryptic error:

Run-time error ‘-2147217887 (80040e21)’:
Multiple-step operation generated errors. Check each status value.Microsoft Access

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 →

The Unexpected Behavior of Mixing ADODB and DAO Recordsets in Access Forms

Software Bug

During a recent project, I encountered an intriguing quirk in Microsoft Access that’s worth sharing. This peculiar behavior occurs when mixing ADODB and DAO recordsets in form events, leading to unexpected results that could easily be mistaken for database corruption.
 

The Setup

I initially created a form with a dynamically built ADODB recordset in the Form_Open event:

Private Sub Form_Open(Cancel As Integer)
    Dim rstVirtual            As Object

    Set rstVirtual = CreateObject("ADODB.Recordset")
    With rstVirtual
        .Fields.Append "ItemId", 20
        .Fields.Append "ItemDescription", 200, 50

        .CursorType = 3    ' adOpenStatic
        .CursorLocation = 3    ' adUseClient
        .LockType = 3    ' adLockOptimistic
        .Open
    End With

    '...
    
    Set Me.Recordset = rstVirtual
End Sub

Continue reading →

Using AI for Development Not Ready for Primetime, Yet

In recent years, the hype surrounding AI has permeated every aspect of our lives, including software development. While initially skeptical due to ethical concerns and copyright issues, curiosity led to exploring AI’s potential in code generation. After months of testing, using multiple programming languages (PHP, VBA, HTML, JavaScript), it’s clear that AI has both strengths and weaknesses in this domain.
 
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 →

Let’s Improve The New Microsoft Access Monaco SQL Editor

Collaborating for Improvement: Addressing the Flaws of the Monaco SQL Editor

It’s time for us to unite and demand that Microsoft take significant steps to improve the current state of the Monaco SQL Editor. While this new feature was marketed as a major upgrade, the reality is far from what users were promised.

I’ve put together a series of suggestions for the Feedback Portal, and I urge you to take just 30 seconds to upvote each one. It’s crucial that Microsoft understands how vital it is to fix and enhance the initial release of the Monaco SQL Editor, which has been riddled with issues since its rollout.
 
Continue reading →