Click here to Skip to main content
14,545,215 members

Working with MS Access Database in .NET Core

Rate this:
5.00 (2 votes)
Please Sign up or sign in to vote.
5.00 (2 votes)
13 Jun 2019CPOL
Since .NET Core does not support OleDB, the ODBC based approach is simple and is promising for interacting with MS Access database. Tips and tricks in using MS Access DB within a .NET Core app are provided.

Introduction

Interacting with MS Access Database (DB) has been a bit of challenge with .NET Core as OleDB is not supported. In order to be able to work with the program to read/write Microsoft Access Database using .NET Core for processing data stored in MS Access DB, we have the option to use EntityFramework or ODBC. While the Entity Framework option is there, but it would be overkill for certain programming needs where the scope is basic read/write for database access and a lightweight approach is desired. The ODBC based approach is simple and promising. In this article, I will provide some tips to programmatic process to be able to read/write into MS Access DB on a .NET Core application using C# and Visual Studio 2017. These tips are based on my recent engagement with working a simple program where I needed to aggregate data from a large set of MS Access DB from a simulation process running in multiple virtual machines in Azure cloud.

Here are my tips for this to work:

  1. The AccessDatabaseEngine_X64.exe needed to be installed in the machine. The error message that is thrown due to the missing correct driver (for example, when the machine has a 32-bit version of AccessDatabaseEngine) is so vague to understand the issue.
  2. The machine does not need the MS Access software installed as long as the database engine mentioned above is present.
  3. Use Odbc from 'System.Data.Odbc'.
  4. See example code. The code snippet shows a method named 'ProcessFile(..)' demonstrating 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.
  5. Note that when you had 32-bit of AccessDatabaseEngine and you updated the access database engine in tip 1 from 32-bit version to 64-bit version, then any application that uses 32-bit version will not be able to connect to Access DB. In that case, you have to switch back to a 32-bit version to run the existing application and to 64-bit version of the database engine to run the .NET Core application. I have not had success running both versions in the same machine.
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);
            }
        }
        
        // Method looks into a folder from appsetting and 
        // return all the subfolders to look into
        static string[] GetInputPaths()
        {
            List<string> paths;
            var dataPath = ConfigurationManager.AppSettings["datapath"];
            string[] subDirectory =
                Directory.GetDirectories(dataPath, "b*", 
                                   searchOption: SearchOption.TopDirectoryOnly);
            return subDirectory;
        }
        
        // 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.
        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);
                }
            }
        }
    }
}

The core of the code above lies in the ProcessFile method block. The OdbcConnection is established with an access driver at runtime to a file path. The example in the code intends to get a list of the table by fetching Schema and write the name of the table to console.

Additional References

History

  • 13th June, 2019: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Benktesh Sharma
Engineer
United States United States
Benktesh is a senior software engineer at Sony where he is a software developer/architect. His main focus when coding is quality, simplicity, and scalability and he is experienced in a broad range of subjects (software design, software development process, project management, testing, UI, multithreading, database, mobile development, and more).

Outside of professional work, there are always some side projects he is working on such as simulation modeling of financial, energy and emissions data and writing articles both technical and non-technical (see his blog at https://benktesh.blogspot.com/). While not coding, he is either reading books fiction and/or non-fiction, watching tv shows, trading stocks, and discussing climate change, global warming, emission trading, and traveling to meet people, place, and culture.

Comments and Discussions

 
QuestionCompact Access Database File Pin
Member 1408726014-Jun-19 1:14
MemberMember 1408726014-Jun-19 1:14 
AnswerRe: Compact Access Database File Pin
pkfox14-Jun-19 23:32
professionalpkfox14-Jun-19 23:32 
AnswerRe: Compact Access Database File Pin
Benktesh Sharma9-Mar-20 5:27
MemberBenktesh Sharma9-Mar-20 5:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Tip/Trick
Posted 13 Jun 2019

Tagged as

Stats

16.8K views
6 bookmarked