VS2022: ODBC Driver Manager Error: Data source name not found and no default driver specified

Question

I've created a simple project in VS2022 and created a form which simply tries to open an MS Access database to retrieve data from one of the tables. Here is the code:

private void SurveyYearsForm_Load(object sender, EventArgs e)
{
    string connectionString = @Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\....\watch.accdb;

    using (OdbcConnection connection = new OdbcConnection(connectionString))
    {
       connection. Open();
       // Do work here.
    }
}

It fails on connection. Open() with the Exception "System.Data.Odbc.OdbcException: 'ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified'"

I've created a Data Source and in Server Explorer it successfully creates a connection and I can retrieve data from any of the tables. So I've gone to the Connection String property for the connection and copied the value into the code above but that generates an error so the code must be doing something subtly different to the VS IDE.

The MS Access database was created with Microsoft® Access® 2021 MSO (Version 2401 Build 16.0.17231.20194) 64-bit.

I've looked at the ODBC data Source Administrator (64 bit) and that contains an entry for ACEODBC.DLL version 16.00.17231.20036 so the version number differs but is that material in this case?

I guess some component isn't installed correctly or missing but it's odd (to me at least) that the IDE works.

Any assistance would be gratefully received!

Answer 1

Ok, first up is the issue of the project build as x32 bits, or x64 bits.

Since the Access data engine is going to be x32 bits (in most cases), then you have to force your project build to match the given bit size of the Access data engine.

That is this setting:

So, your project bit size build has to match the Access data engine bit size you installed. Keep in mind that vs2022 is the FIRST version of VS that is x64 bits. Thus, if you use ANY CPU and hit f5, then your project runs as a in-process x64 bit process. In previous versions of VS, hitting F5 with ANY CPU would result in a x32 bit running version of your software.

As noted, you are free to use the ODBC provider, or the oleDB provider. Most would suggest using oleDB providers when working with Access databases, and you do have somewhat better support for some Access database features.

However, ODBC is also a fine choice.

So, next up would be to create a connection string for the whole project (so you don't have to type it over and over in code).

I should also note that while you can use the VS connection wizards to build a valid connection string, the test connection button will NEVER work in vs2022 when running Access x32 bits. And this use case was reversed for versions prior to vs2022.

So, in vs2022, if you running Access x64, then the test connection button will work.

If you are running Access x32, then the test connection will NOT work, however, when you run your code, such connections should work just fine (assuming you forced the project to run as x32 bits, and don't use ANY CPU).

So, I'm going to recommend the oleDB provider, and the reason is the connection builders will work FAR better then ODBC. As noted, ODBC has much merit here, and is a still a good choice, since as noted, you can change that connection string to SQL server, or MySQL or whatever, and you don't have to change the database provider.

So, let's setup an oleDB connection to the Access database.

And might as well use project settings, and thus we don't have to type or see or have the messy connection string in code.

So, open up your project settings.

Hence this:

Note that you VERY much want to check which version of the database engine you are using (you want to use ACE, since that is required to open accDB files, and also that ACE has a x64 bit version available, and JET does not).

So, when building the connection, make 100%, 200%, 300% sure you set or look at which data engine was chosen here (ACE or JET).

Hence hit the advanced tab after doing above, and check that you chosen ACE:

Now, for a test, say a blank new form, drop in a button, drop in a DataGridView.

And thus, now our code becomes this:

    private void button1_Click(object sender, EventArgs e)
    {
        DataTable dt = new DataTable();

        string strSQL =
            @"SELECT ID, FirstName, LastName, City, HotelName, Description
            FROM tblHotelsA
            ORDER BY HotelName";

        using (OleDbConnection conn = new OleDbConnection(Properties.Settings.Default.AccessDB))
        {
            using (OleDbCommand cmd = new OleDbCommand(strSQL, conn))
            {
                conn.Open();
                dt.Load(cmd.ExecuteReader());
            }
        }
        dataGridView1.DataSource = dt;
    }

And the result is this:

So, in summary:

The test connection for access x32 data engine will NEVER work in vs2022, but hitting F5 to run the project should work. This assumes you set ANY CPU to x32 bits (x86).

However, you can also consider installing Access x64 bits and then force your project to x64 bits. The added bonus is that the test connection button will work in vs2022, but I can't say this is all that large of an issue.

If you goal is ODBC, then I can post a working Access connection for ODBC.

Answer 2

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified occurs when one uses an OleDb connection string with ODBC.

The following is incorrect:

string connectionString = @Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\....\watch.accdb;

when using OdbcConnection. See Connection Strings for more information.

---

The following shows the steps necessary to be able to retrieve data from an Access database:

First ensure that either MS Access or MS Access Runtime is installed. See here for more information.

If the necessary components aren't installed one will receive an error message.

OLEDB:

If one uses the following connection string:

Provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\Temp\Database1.accdb;Persist Security Info=False;

and Microsoft.ACE.OLEDB.12.0 doesn't exist or the bitness (32-bit/64-bit) of one's application doesn't match the bitness of the MS Access components, then one receives the following error message: System.InvalidOperationException: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

To see installed versions of Microsoft ACE OLEDB, one can check the registry.

Search for: HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Microsoft.ACE.OLEDB.xx.x (ex: Microsoft.ACE.OLEDB.15.0). Expand it to see the CLSID subkey. Write down the (Default) value for CLSID (ex: {3BE786A1-0366-4F5C-9434-25CF162E475E}).

Next, search HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID for the CLSID obtained in the previous step (ex: {3BE786A1-0366-4F5C-9434-25CF162E475E}). If your OS is 64-bit, then also search HKEY_LOCAL_MACHINE\WOW6432Node\Classes\CLSID.

Once the subkey has been located, it should contain a subkey named InprocServer32, expand it to see the (Default) value.

If the subkey exists under HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID, then a 64-bit version is installed.

However, if the subkey exists under HKEY_LOCAL_MACHINE\WOW6432Node\Classes\CLSID, then a 32-bit version is installed.

Note: Per Machine software installations store their information under HKEY_LOCAL_MACHINE (HKLM) whereas, Per User software installations store their information under HKEY_USERS (HKU) - HKEY_CURRENT_USER (HKCU) is the information stored in HKEY_USERS (HKU) for the currently logged in user.

Alternatively, run the following in PowerShell 7 (tested with PowerShell v7.4.1)

 ForEach ($obj in Get-ChildItem -Path HKLM:\Software\Classes | Where-Object {$_.Name -match "Microsoft.ACE.OLEDB."} | Where-Object {$_.PSChildName -notmatch "Microsoft.ACE.OLEDBErrors*"}) { $clsid = Get-ItemProperty -Path ($obj.PSPath + "\CLSID") -Name "(default)"; if (Test-Path ('HKLM:\Software\Classes\CLSID\' + $clsid."(default)")) { Get-ItemProperty -Path ("HKLM:\Software\Classes\CLSID\" + $clsid."(default)" + "\InprocServer32") | Select @{N='Microsoft ACE OLEDB Provider';E={$obj.PSChildName}}, @{N='Location';E={$_."(default)"}} | Format-List -Property *} else {Get-ItemProperty -Path ("HKLM:\Software\WOW6432Node\Classes\CLSID\" + $clsid."(default)" + "\InprocServer32") | Select @{N='Microsoft ACE OLEDB Provider';E={$obj.PSChildName}}, @{N='Location';E={$_."(default)"}} | Format-List -Property *}}

For example, if one has Microsoft Access 2013 Runtime, installed:

If 64-bit version is installed, one sees:

Microsoft ACE OLEDB Provider : Microsoft.ACE.OLEDB.12.0
Location                     : C:\Program Files\Common Files\Microsoft Shared\OFFICE15\ACEOLEDB.DLL


Microsoft ACE OLEDB Provider : Microsoft.ACE.OLEDB.15.0
Location                     : C:\Program Files\Common Files\Microsoft Shared\OFFICE15\ACEOLEDB.DLL

If 32-bit version is installed, one sees:

Microsoft ACE OLEDB Provider : Microsoft.ACE.OLEDB.12.0
Location                     : C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE15\ACEOLEDB.DLL


Microsoft ACE OLEDB Provider : Microsoft.ACE.OLEDB.15.0
Location                     : C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE15\ACEOLEDB.DLL

However, if no versions of Microsoft.ACE.OLEDB are found, then nothing is returned.

For more information about why the bitness (32-bit/64-bit) of MS Access components must match the bitness (32-bit/64-bit) of one's application see The Component Object Model and IntPtr.Size.

ODBC:

If one uses the following connection string:

Driver={{Microsoft Access Driver (*.mdb, *.accdb)}};Dbq=C:\Temp\Database1.accdb;Uid=Admin;Pwd=;

and Microsoft Access Driver doesn't exist or the bitness (32-bit/64-bit) of one's application doesn't match the bitness of the MS Access components, then one receives the following error message: System.Data.Odbc.OdbcException (0x80131937): ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Note: Since installing MS Access or MS Access runtime installs both ODBC components and OLEDB components, running the PowerShell command to check for OLEDB should also be indicative (but not necessarily definitive) of whether or not the necessary MS Access components are installed for ODBC.

---

The following shows how to retrieve data from an Access database:

Option 1 (OleDb)

private DataTable GetDataOleDb(string accessFilename)
{
    
    string connectionString = $"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = {accessFilename};Persist Security Info=False;";

    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        using (OleDbDataAdapter da = new OleDbDataAdapter("select * from Table1", connection))
        {
            //create new instance
            DataTable dt = new DataTable();

            //get data
            int numRows = da.Fill(dt);

            return dt;
        }
    }
}

Usage:

using (OpenFileDialog ofd = new OpenFileDialog())
{
    ofd.Filter = "Access Database (*.accdb)|*.accdb";

    if (ofd.ShowDialog() == DialogResult.OK)
    {
        DataTable dt = GetDataOleDb(ofd.FileName);

        foreach (DataRow dr in dt.Rows)
        {
            foreach(DataColumn col in dt.Columns)
            {
                Debug.WriteLine($"{col.ColumnName}: {dr[col]?.ToString()}");
            }
        }
    }
}

---

Option 2 (ODBC)

private DataTable GetDataOdbc(string accessFilename)
{
    string connectionString  = $"Driver={{Microsoft Access Driver (*.mdb, *.accdb)}};Dbq={accessFilename};Uid=Admin;Pwd=;";
    
    using (OdbcConnection connection = new OdbcConnection(connectionString))
    {
        using (OdbcDataAdapter da = new OdbcDataAdapter("select * from Table1", connection))
        {
            //create new instance
            DataTable dt = new DataTable();

            //get data
            int numRows = da.Fill(dt);

            return dt;
        }
    }
}

Usage:

using (OpenFileDialog ofd = new OpenFileDialog())
{
    ofd.Filter = "Access Database (*.accdb)|*.accdb";

    if (ofd.ShowDialog() == DialogResult.OK)
    {
        DataTable dt = GetDataOdbc(ofd.FileName);

        foreach (DataRow dr in dt.Rows)
        {
            foreach(DataColumn col in dt.Columns)
            {
                Debug.WriteLine($"{col.ColumnName}: {dr[col]?.ToString()}");
            }
        }
    }
}

---

Option 3 (ODBC DSN)

Note: See Managing Data Sources for information about creating a DSN.

private DataTable GetDataOdbcDSN()
{
    string connectionString = $"DSN=TestDsn;Uid=Admin;Pwd=;";

    using (OdbcConnection connection = new OdbcConnection(connectionString))
    {
        using (OdbcDataAdapter da = new OdbcDataAdapter("select * from Table1", connection))
        {
            //create new instance
            DataTable dt = new DataTable();

            //get data
            int numRows = da.Fill(dt);

            return dt;
        }
    }
}

Usage:

DataTable dt = GetDataOdbcDSN();

foreach (DataRow dr in dt.Rows)
{
    foreach(DataColumn col in dt.Columns)
    {
        Debug.WriteLine($"{col.ColumnName}: {dr[col]?.ToString()}");
    }
}

---

Resources:

• OleDb
• ODBC
• Connection Strings
• ADO.NET Overview
• Access Runtime Info
• String Interpolation

Answer 3

First many, many thanks for the detailed responses! Apologies for my confusion re ODBC vs OleDb! (I think I was getting so hacked off trying to get to the bottom of this that in one of my many attempts to resolve this I get the connection string completely wrong).

I managed to get the app to work in the end by doing two things:

1. Being consistent in using OleDb (I'm very unlikely to use anything other than MS Access in my particular scenario)
2. Changing the Platform to x64 from "Any CPU" which was the key. I don't understand why I had to do that but, hey, it works.

Just in case it's useful here are the steps I went through to fix this:

1. In VS2022 I created a new Windows Form App (.NET Framework) project and added a form and then added an OpenFileDialog control and Button
2. Added the OleDb code suggested above.
3. Ran the app but that resulted in the error "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine". Progress of sorts as it was a different error.
4. To try and solve this I installed the Microsoft Access Database Engine 2010 Redistributable (AccessDatabaseEngine_X64.exe). However I'm not convinced this was essential as it looks like Microsoft.ACE.OLEDB.16.0 was already present. Determined by running the Power Shell command : (New-Object system.data.oledb.oledbenumerator).GetElements() | select SOURCES_NAME, SOURCES_DESCRIPTION This showed both versions 12 and 16 of the Microsoft.ACE.OLEDB
5. Ran the code again but this resulted in the same error.
6. Changed to x64 and then the code runs successfully.

For completeness I also tried using the ODBC code and that also runs successfully.

Again many thanks for the help.