Future-Proofing Access Connection Classes

Microsoft has done a great job to ensure that your old code will still work with the latest updates. After many years, there has finally been an exception to that rule: Microsoft Access. For your utilities that wrap .mdb files, you will have to update your connection class. This little bit of code helps do that.

#region Ole / Access methods

private OleDbConnection _conn;
public string FileLocation = @"C:\MyAccessFile.mdb";

/// <summary>
///     Opens this instance.
/// </summary>
/// <returns>true if it opened</returns>
public bool Open()
{
	if (!File.Exists(FileLocation))
	{
		return false;
	} 
	try
	{
		if (_conn != null && _conn.State == System.Data.ConnectionState.Open) 
		{
			return true;
		} 

		_conn = new OleDbConnection(
			"Provider=Microsoft.Jet.OLEDB.4.0; " +
			"Data Source=" + FileLocation + ";");
		_conn.Open();
		return true;
	}
	catch (Exception)
	{
		try
		{
			_conn = new OleDbConnection(
				"Provide=Microsoft.ACE.OLEDB.12.0; " +
				"Data Source=" + FileLocation + ";");
			_conn.Open();
			return true;
		}
		catch (Exception)
		{
			// ignored
		}
	}
	return false;
}

/// <summary>
///     Runs the command.
/// </summary>
/// <param name="command">The command.</param>
/// <returns>Runs a command</returns>
public int RunCommand(string command)
{
	int returnInt = 0;
	try
	{
		try
		{
			if (!Open()) return -1;

			while(_conn.State == System.Data.ConnectionState.Executing)
			{
				Thread.Sleep(1000);
			}

			IDbCommand cmd =
				new OleDbCommand(command, _conn);
			returnInt = cmd.ExecuteNonQuery();
		}
		catch (Exception)
		{
			returnInt = -1;
		}
	}
	catch (Exception)
	{
		returnInt = -1;
	}
	return returnInt;
}

/// <summary>
///     Closes this instance.
/// </summary>
/// <returns>true if it closed</returns>
public bool Close()
{
	try
	{
		_conn?.Close();
		_conn = null;
		return true;
	}
	catch (Exception ex)
	{
		Console.WriteLine(ex.Message);
	}
	return false;
}
#endregion