How to Connect to Access database thru Visual Studio 2022

Nick I 5 Reputation points
2023-07-30T18:58:13.8866667+00:00

I have Windows 11, Access 2021, and Visual Studio 2022 installed -- all 64-bit.

I am trying to connect to Access database from VS. No matter what I try I get error:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

or

The 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine

Many recommend that I need to download the ACE 2016 distributable. But is it needed with MS Access 2021 installed?

Visual Studio
Visual Studio
A family of Microsoft suites of integrated development tools for building applications for Windows, the web and mobile devices.
4,408 questions
Access
Access
A family of Microsoft relational database management systems designed for ease of use.
271 questions
0 comments No comments
{count} votes

5 answers

Sort by: Most helpful
  1. Nick I 5 Reputation points
    2023-08-22T14:38:55.4933333+00:00
    For anyone who lands here, I hope this helps.
    
    My old PC ran Windows 10 64-bit, and 32-bit versions of Access and VS. Any VS project the accessed an Access database would run fine as long as the target CPU was x86.
    If I inadvertently forgot to set the CPU to x86, I would get the error: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine".
    
    On a new PC, I run Windows 11, Access 2021, Visual Studio 2022.  All 64-bit. I attempted to port several projects from my old PC.  VS2022 will convert the project to new format, but I would get the provider not registered error.
    
    To resolve:
    1. Check that you have the provider -- see Tanya's 7/31/2023 note above and run her power shell command.
    
    2. If you want to convert to ODBC, see Castorix31's note above.
    
    3. If you want to continue with OLEDB, there are 3 settings in project properties to change:
    	Set Platform to x64 (Any CPU may work)
    	Set Target CPU to x64
    	Un-check "Prefer 32-bit"  (Important -- the default in VS is checked.)
    	
    	Be sure to change the settings in both Release and Debug configurations.
    
    With the above changes, all my projects accessing Access database compiled and ran successfully.
    
    1 person found this answer helpful.
    0 comments No comments

  2. Tianyu Sun-MSFT 24,896 Reputation points Microsoft Vendor
    2023-07-31T08:25:15.38+00:00

    Hello @Nick I,

    Welcome to Microsoft Q&A forum.

    From Visual Studio side, you may refer to this thread: How to fix: "The Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine".

    You could check if the provider is installed via the following powershell command.(New-Object system.data.oledb.oledbenumerator).GetElements() | select SOURCES_NAME, SOURCES_DESCRIPTION

    If the installed “AccessDatabaseEngine” still does not work, please change the Active Solution Platform from "Any CPU" to "x86".

    Whether it’s needed with MS Access 2021 installed, perhaps you may kindly wait for the confirmation from Access Team.

    Sincerely,

    Tianyu


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  3. Nick I 5 Reputation points
    2023-08-04T16:03:10.4466667+00:00

    Thanks for your suggestion, but no luck.

    I tried to download the 32-bit version of ACE redistributable, but was blocked because I have Microsoft Office 2021 64-bit installed.

    I then downloaded the 64-bit version of ACE redistributable. I still got the Provider not registered error. And worse, I could no longer open the Access 2021 application. -- Error message said that because I had the redistributable installed, I must click on a filename. To me that means I cannot create a new database. So I uninstalled the redistributable..

    0 comments No comments

  4. Castorix31 80,906 Reputation points
    2023-08-05T12:27:56.8166667+00:00

    Maybe you can test other Drivers, like ODBC

    A test (Office 2016, Windows 10 22H2) :

    At beginning :

              using System.Data.Odbc;    
    

    Test with an Access file named "Employees.accdb" containing 1 table named "Employee" (read from schema for the test) with 1 Button + 1 DGV :

                    List<string> listTables = new List<string>();
                    string sPassword = "toto";
                    string sConnectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=E:\\Employees.accdb;PWD=" + sPassword;
                    using (OdbcConnection conn = new OdbcConnection(sConnectionString))
                    {
                        try
                        {
                            conn.Open();
                            DataTable dt = conn.GetSchema("tables");
                            foreach (DataRow dr in dt.AsEnumerable().Where(x => x["TABLE_TYPE"].ToString() == "TABLE"))
                            {
                                listTables.Add(dr["TABLE_NAME"].ToString());
                            }
    
                            if (listTables.Count() > 0)
                            {
                                string sSQL = $"SELECT * FROM {listTables[0]}";
                                DataTable dt2 = new DataTable();
                                using (OdbcDataAdapter oda = new OdbcDataAdapter(sSQL, conn))
                                {
                                    oda.Fill(dt2);
                                    dataGridView1.DataSource = dt2;
                                }                          
                              
                            }
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                    }
    
    

    ODBC_Access

    0 comments No comments

  5. Nick I 5 Reputation points
    2023-08-07T15:05:29.7666667+00:00

    Thanks much. I ran your suggested code with the ODBC provider and it worked.

    I am running Win 11 Pro, VS 2022, and Access 2021 -- all 64-bit. When I first created a new project, your code did not run. After stumbling around for hours, I find that under Build properties, the "Prefer 32-bit" was checked by default. Un-checking it proved successful.

    Unfortunately, this did not work with OleDB provider. I still get the provider not registered error. If I cannot find a solution, I can convert several projects to use ODBC. (I'm not familiar with it, so I have much learning ahead.)

    Thanks again.

    0 comments No comments