Interacting with Microsoft Access Database in .Net Core Application

I had to work with a program to read/write Microsoft Access Database and I was using .Net Core 2.2 application for processing a large number of files. I later found out that Oledb is no longer supported in the .Net Core world, and then I had to explore additional options. While the Entity Framework option is there, but it would be overkill for the programming needs for the project. I looked into the ODBC based approach and it showed the promise.  After some issues and errors, I was able to successfully connect/interact with the access db.

The AccessDatabaseEngine_X64.exe needed to be installed in the machine. The error message that is thrown due to the missing correct driver (I had 32 bit version before) was vague so it was not immediately evident, but eventually, it worked out.

In this article, I am going to discuss the steps to take in order to connect to access db from within a .Net Core 2.2 application written in C# using Visual Studio 2017.

The content of app.config is fairly simple as shown below which stores a key value for datapath:

	<configuration>
	<appsettings>
	<add key="datapath" value="C:\temp\">
	</add></appsettings>
	</configuration>

view raw aggregate_app.xml hosted with ❤ by GitHub

The following code snippet shows a method named 'ProcessFile(..)' demonstrating demonstrates the use of interaction with Access. The method processes the *.accdb file in the folder path, an input to the method itself. The method looks into the folder path for an access db file. If the file is found, a connection is made and names of the tables (user tables only) are written in the console. In case of error, it writes the error message.

	using System;
	using System.Collections.Generic;
	using System.Configuration;
	using System.IO;
	using System.Linq;
	using System.Data.Odbc;
	namespace Aggregate
	{
	class Program
	{
	static void Main(string[] args)
	{
	var subDirectory = GetInputPaths();
	foreach (var dir in subDirectory)
	{
	ProcessFile(dir);
	}
	}
	/// <summary>
	/// Method looks into a folder from appsetting and return all the subfolders to look into
	/// </summary>
	/// <returns></returns>
	static string[] GetInputPaths()
	{
	List<string> paths;
	var dataPath = ConfigurationManager.AppSettings["datapath"];
	string[] subDirectory =
	Directory.GetDirectories(dataPath, "b*", searchOption: SearchOption.TopDirectoryOnly);
	return subDirectory;
	}
	/// <summary>
	/// Method processes the *.accdb file in the folderpath
	/// The method looks into the folder path for an accessdb file. If the file is found,
	/// makes a connection and writes the name of user tables in the console.
	/// In case of error, it writes the error message.
	/// </summary>
	/// <param name="folderPath">Path to directory of the folder</param>
	static void ProcessFile(string folderPath)
	{
	//the file pattern is *output.accdb
	var file = Directory.GetFiles(@folderPath, "*output.accdb").FirstOrDefault();
	if (File.Exists(file))
	{
	string connetionString = null;
	connetionString = @"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + file;
	OdbcConnection odbcConnection = new OdbcConnection(connetionString);
	try
	{
	odbcConnection.Open();
	List<string> tableNames = new List<string>();
	var schema = odbcConnection.GetSchema("Tables");
	foreach (System.Data.DataRow row in schema.Rows)
	{
	var tableName = row["TABLE_NAME"].ToString();
	//Exclude the system tables
	if (!tableName.StartsWith("MSys"))
	{
	tableNames.Add(tableName);
	}
	}
	foreach (var tableName in tableNames)
	{
	Console.WriteLine(tableName);
	}
	odbcConnection.Close();
	}
	catch (Exception ex)
	{
	Console.WriteLine(ex.Message);
	}
	}
	}
	}
	}

view raw aggregate.cs hosted with ❤ by GitHub