Microsoft Access error cannot find .mdb but database is .accdb

Question

I'm doing a sample program accessing a Microsoft Access database. It is a .accdb file. The name of the Database is ACRONYM_DB.accdb and has one data table called ACRONYM. My code is below :

string currentLoc = System.AppDomain.CurrentDomain.BaseDirectory.ToString();

ObservableCollection<Acronym.Acronym> acrOC = new ObservableCollection<Acronym.Acronym>();
string ConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + currentLoc + "\\Assets\\ACRONYM_DB.accdb;Jet OLEDB:Database Password=password";

OleDbConnection MyConn = new OleDbConnection(ConnStr);
MyConn.Open();
OleDbCommand myCommand = MyConn.CreateCommand();
myCommand.CommandText = "SELECT * FROM ACRONYM_DB.ACRONYM WHERE ACRONYM_NAME=" + acrName;
OleDbDataReader myReader = myCommand.ExecuteReader();

On the Execute reader line I'm gettting the error :

System.Data.OleDb.OleDbException was unhandled by user code
  HResult=-2147467259
  Message=Could not find file 'C:\Users\Mark\Desktop\release\ACRONYM_DB.mdb'.
  Source=Microsoft Office Access Database Engine
  ErrorCode=-2147467259
  StackTrace:
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.OleDb.OleDbCommand.ExecuteReader()
       at AcronymFinder.Model.Database.AcronymDatabase.HistoricalDef(String acrName) in c:\Users\Mark\Documents\Visual Studio 2013\Projects\AcronymFinder\AcronymFinder\Model\Database\AcronymDatabase.cs:line 28
       at AcronymFinder.ViewModel.MainViewModel.set_SelectedAcronym(Acronym value) in c:\Users\Mark\Documents\Visual Studio 2013\Projects\AcronymFinder\AcronymFinder\ViewModel\MainViewModel.cs:line 315
  InnerException: 

I know the error has got to be with the query I'm using but what is it I need to do differently? Also I am using a 64 bit version of Access 2013.

Answer 1

Do not include the database name in the SELECT statement:

myCommand.CommandText = "SELECT * FROM ACRONYM WHERE ACRONYM_NAME=" + acrName;

That change should stop the db engine from complaining that it can't find ACRONYM_DB.mdb.

However the revised statement could still fail, with a different error, if your ACRONYM_NAME field is text datatype. If it is text, you could avoid a missing parameter value complaint by including quotes before and after the value of acrName:

myCommand.CommandText = "SELECT * FROM ACRONYM WHERE ACRONYM_NAME='" + acrName + "'";

But, really, a parameter query would be a better approach because you would be protected against SQL injection and you wouldn't need to bother about quotes if acrName is a text value.