Running a saved MS Access request from C #

Question

A very strange behavior was found in the MS Access database when starting queries from C #. It seems that if the request in MS Access contains an “how” operator, it will not return its result to Oledb. If anyone knows how to solve this, I will be very grateful. C # code:

DataTable GetAccessData(string FileName, string Password) { Query = "select * from [qry_1]"; DataTable DT = access2dt ( FileName,Password,Query); MessageBox.Show(Query); if (DT == null || DT.Rows.Count <= 1) throw new Exception("Data not found"); else return DT; } public static DataTable access2dt(string filename, string password, string query) { string conString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Jet OLEDB:Database Password=" + password; OleDbConnection mycon; mycon = new OleDbConnection(conString); if (mycon.State == ConnectionState.Closed) { mycon.Open(); } try { OleDbCommand accessCommand = new OleDbCommand(); System.Data.DataTable dt = new System.Data.DataTable(); accessCommand = new OleDbCommand(query, mycon); using (OleDbDataReader myReader = accessCommand.ExecuteReader()) { DataTable myTable = new DataTable(); myTable.Load(myReader); return myTable; } } finally { mycon.Close(); } } 

And the query [qry_1] stored in the MS Access database is as follows:

 SELECT tbl_MAPPING_GICM_AoA.[Type of expense] FROM tbl_USGAAP_ALL_TRANS, tbl_MAPPING_GICM_AoA WHERE tbl_USGAAP_ALL_TRANS.[Account]=[tbl_MAPPING_GICM_AoA].[Natural Account] AND tbl_USGAAP_ALL_TRANS.[Created By] NOT IN (SELECT [Created by / User ID] FROM tbl_GICM_AoA_EXCL_FACTOR1) AND tbl_USGAAP_ALL_TRANS.[Je Source] NOT IN (SELECT [JE SOURCE] FROM tbl_GICM_AoA_EXCL_FACTOR2) AND tbl_USGAAP_ALL_TRANS.[Je Category] NOT IN (SELECT [JE CATEGORY] FROM tbl_GICM_AoA_EXCL_FACTOR3) AND (tbl_USGAAP_ALL_TRANS.[Account] LIKE '4*' OR tbl_USGAAP_ALL_TRANS.[Account] LIKE '5*' OR tbl_USGAAP_ALL_TRANS.[Account] LIKE '6*' ) 

Therefore, when I run it in Access, it works fine, but when I call this request from C #, I get an exception "Data not found". If I delete the last line (with LIKE) in the request, everything starts working

Answer 1

Try replacing the wildcard character. Access uses '*', but using OLEDB you will need to use "%" as a wildcard.