Connecting to MS Access from C#

This is a famous problem faced by developers which connecting to MS Access database from c# code using OLEDB. The problem is when using LIKE to filter records. E.g. when you try EmpName LIKE ‘*John*’,  the C# dataset will not get filled with any record. While if you run your whole query with that LIKE statement directly inside MS Access, it will return the desire result, i.e. LIKE will work as expected. This is confusing because the wild card operator in MS Access is ‘*’  as documented on MSDN.

There are two possible causes:

  1. The SQL written using C# and .net objects doesn’t understand ‘*’ characters
  2. The connection string that you use may not be appropriate and may not allow this kind of wild characters

The first cause is most likely while some people have also found the second cause to be the problem.

The solution is very simple – as long as you know what it is :). To solve this issue, just replace the ‘*’ by ‘%’ and it should work.
Also make sure the connection string you use looks like this: Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\[db]. mdb
where [db] is your actual file name

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s