How to get Visual Studio 2015 to work with Access 2010 LIKE wildcards

Question

I'm working with an existing Access 2010 database that contains tables and queries ('views').

When I connect the database from Visual Studio 2015, tools like the Database Explorer can see the contents of the Access tables, but for queries that contain LIKE operators with '*' wildcards, it sees just the header titles, with zero records.

I found that if I duplicate those Access-based queries in Visual Studio but replace the LIKE '*' wildcards with '%', those VS-based queries work (they return non-zero records).

This particular Access database is full of queries with '*' and it would be difficult to change them all (eg., to ALIKE).

Is there a way to get Visual Studio tools to work with the '*' in LIKE queries in that database? Perhaps a parameter on the connection string, or a property in VS? Or maybe there is something that can be changed in the database itself (a global parameter)?

Changing the Provider in VS from Microsoft.ACE.OLEDB.4.0 to Microsoft.ACE.OLEDB.12.0 had no effect. The database is apparently 04.00.0000 (Connection properties).

My hope is to create a C# desktop app that can access that database (and its queries that have those '*' wildcards).

For background on the wildcard incompatibility, see for example LIKE query on an Access database via C# always returns COUNT(*) of 0

Thanks for any ideas.

Answer 1

No, there is no such setting. You will have to adopt one way or the other.

Answer 2

The Visual Studio built-in database tools tend to use System.Data.OleDb for their database manipulations. What you are seeing is an unfortunate limitation of the Access OLEDB provider.

For an Access table named [fruits]

id  fruit
--  -----
 1  apple
 2  banana
 3  cherry
 4  apricot

and an Access saved query named [qry_aFruits]

SELECT fruit
FROM fruits
WHERE fruit LIKE "a*";

the following C# code using System.Data.OleDb returns no rows

myConnectionString =
        @"Provider=Microsoft.ACE.OLEDB.12.0;" +
        @"Data Source=C:\Users\Public\Database1.accdb;";
using (var conn = new OleDbConnection(myConnectionString))
{
    conn.Open();
    using (var cmd = new OleDbCommand("SELECT * FROM [qry_aFruits]", conn))
    {
        using (OleDbDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                Console.WriteLine(rdr["fruit"]);
            }
        }
    }
}

However, the same code using System.Data.Odbc returns two rows, as expected:

myConnectionString =
        @"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
        @"Dbq=C:\Users\Public\Database1.accdb;";
using (var conn = new OdbcConnection(myConnectionString))
{
    conn.Open();
    using (var cmd = new OdbcCommand("SELECT * FROM [qry_aFruits]", conn))
    {
        using (OdbcDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                Console.WriteLine(rdr["fruit"]);
            }
        }
    }
}

So if you need to work with a lot of saved Access queries that use * as the LIKE wildcard then you'll probably have to forego the built-in Visual Studio database tools that are built on top of System.Data.OleDb (e.g., Data Sources and TableAdapters) and use ODBC.