Account Security Problems

Fix Microsoft Access DoCmd.OpenQuery Login Validation Failed

Bushra Parveen Sep 25, 2026 869 views

Question details

The user wants to securely validate user credentials in Microsoft Access 2016 but encounters failures when attempting to pass a SQL string to the DoCmd.OpenQuery method.

How to Securely Validate Microsoft Access User Logins When DoCmd.OpenQuery Fails

Product: Microsoft Access 2016
Device & OS: not provided
Scenario: Building a custom user login form and trying to authenticate usernames and passwords against a backend database table.
Observed behavior: DoCmd.OpenQuery fails to execute the provided SQL string because it strictly requires the name of a saved query and attempts to open it in Datasheet view, rather than silently executing raw SQL for validation.

Before you start

Before modifying your database login code, ensure you have backed up your Access database file (.accdb or .mdb). Familiarize yourself with basic VBA programming and security concepts like SQL injection to safely handle user credentials.

Solution 1Recommended

Validate User Logins Using the DCount Function

Use the DCount function to count matching records instead of opening a query, ensuring you escape single quotes to prevent basic errors.

DoCmd.OpenQuery is designed to open a saved query in Datasheet view, making it unsuitable for executing inline SQL validation. Instead, DCount allows you to look up records matching specific criteria securely if properly escaped.

Open the VBA Editor

Open your Access Database in Design View, select your login form, and open the associated VBA code module.

Replace DoCmd.OpenQuery

Remove the DoCmd.OpenQuery call and instead declare a variable to hold the record count using the DCount function.

Escape User Inputs

Format the criteria string to escape apostrophes. For example: recordCount = DCount("*", "tblMembership", "FirstName = '" & Replace(varFirstName,"'","''") & "'").

Verify Login

Check if the returned recordCount is greater than zero. If it is, the user credentials are valid and you can grant access.

Validate User Logins Using the DCount Function

Avoid Plain Text Passwords: Never store plain-text passwords in your database. Always compare properly salted password hashes to maintain account security.

Solution 2

Implement Parameterized Queries via DAO

Utilize VBA and DAO QueryDefs to pass user inputs as parameters, completely neutralizing SQL injection risks.

Free Microsoft Office alternative

Looking for a Secure and Free Office Suite? Try WPS Office

While database tools like Microsoft Access require complex VBA coding for basic security like login validation, you might also need a lightweight, cost-effective solution for your daily document, spreadsheet, and presentation needs. WPS Office provides exceptional functionality and compatibility without the hefty subscription fees.

1. Visit the Official Website: Go to the official WPS Office website to find the free download link.
2. Download the Installer: Click the 'Free Download' button to get the correct version for your Windows, Mac, or Linux operating system.
3. Install and Edit: Run the installer, open WPS Office, and immediately start working on your existing Microsoft Office files securely.

Fully compatible with Microsoft Word, Excel, and PowerPoint file formats (.docx, .xlsx, .pptx).Built-in PDF editing tools for secure document sharing and form validation.Free to use with a lightweight, user-friendly interface.Seamless migration from Microsoft Office with no steep learning curve.

Frequently Asked Questions

Why does DoCmd.OpenQuery give a type mismatch or failure with my SQL string?

DoCmd.OpenQuery strictly accepts the name of a pre-saved query in your Access database, not a raw SQL string. It is designed to open query results in a UI window (Datasheet view), not to execute backend logic or validation natively.

How can I prevent SQL injection in Microsoft Access VBA?

Avoid concatenating user input directly into your SQL strings. Instead, use DAO Parameterized queries where inputs are treated as literal values, or thoroughly escape single quotes using the Replace() function before executing.

Is it safe to store user passwords in an Access table?

No, storing plain-text passwords is a major security vulnerability. You should hash the passwords with a unique salt before storing them, and validate logins by comparing the hash of the entered password against the stored hash.

Microsoft Office Security Issues