I'm trying to write a C# program that will migrate a large list of Ms Access Dbs to a Ms Sql server. I currently have no problem connecting to the Ms Sql server but Access has been a brick wall.

I've tried with .Net Core using Odbc but I can't seem to solve the error "Data source name not found and no default driver." regardless what I put in so I've switched to a new project using the .Net Framework thinking that older might mean I have a better chance.

It seems that my system has 'Microsoft.Jet.OLEDB.4.0' but this does not recognize my Access Db which is in a format of '*.accdb'. Using the internet I found out that I should try to switch to 'Microsoft.ACE.OLEDB.16.0' but sadly 'this provider is not registered on the local machine.'.

I do not have admin rights so my attempt to install AccessDatabaseEngine as was suggested was fruitless.

What would you recommend I try to be able to get C# to open an connection to the Ms Access databases.

UPDATE: I've written a script that I ran in 86x and 64x that should help me connect. Could someone help improve this script so that I can quickly identify how to connect to an access database (*.accdb) from any machine.

string[] filePaths = Directory.GetFiles(pathToAccessDbs);
List<string> providers = new List<string>();

var oleEnum = new OleDbEnumerator();
var elems = oleEnum.GetElements();
if (elems != null && elems.Rows != null)
    foreach (System.Data.DataRow row in elems.Rows)
        if (!row.IsNull("SOURCES_NAME") && row["SOURCES_NAME"] is string)
        {
            Console.WriteLine(row["SOURCES_NAME"]);
            providers.Add(row["SOURCES_NAME"].ToString());
            providers.Add("{" + row["SOURCES_NAME"].ToString() + "}");

        }
RegistryKey reg = null;
Console.WriteLine("**********CurrentUser");
try
{
    reg = (Registry.CurrentUser).OpenSubKey("Software");
    reg = reg.OpenSubKey("ODBC");
    reg = reg.OpenSubKey("ODBC.INI");
    reg = reg.OpenSubKey("ODBC Data Sources");
    if (reg != null)
    {

        Console.WriteLine("--Value");
        foreach (string item in reg.GetValueNames())
        {
            Console.WriteLine(item);
            providers.Add(item);
            providers.Add("{" + item + "}");
            var val = reg.GetValue(item);
            Console.WriteLine(val);
            providers.Add(val.ToString());
            providers.Add("{" + val.ToString() + "}");
        }
        Console.WriteLine("--Subkeys");
        foreach (string item in reg.GetSubKeyNames())
        {
            Console.WriteLine(item);
            providers.Add(item);
            providers.Add("{" + item.ToString() + "}");
        }
    }
}
catch { }

Console.WriteLine("**********LocalMachine");
try
{
    reg = (Registry.LocalMachine).OpenSubKey("Software");
    reg = reg.OpenSubKey("ODBC");
    reg = reg.OpenSubKey("ODBC.INI");
    reg = reg.OpenSubKey("ODBC Data Sources");
    if (reg != null)
    {

        Console.WriteLine("--Value");
        foreach (string item in reg.GetValueNames())
        {
            Console.WriteLine(item);
            providers.Add(item);
            providers.Add("{" + item + "}");
            var val = reg.GetValue(item);
            Console.WriteLine(val);
            providers.Add(val.ToString());
            providers.Add("{" + val.ToString() + "}");
        }
        Console.WriteLine("--Subkeys");
        foreach (string item in reg.GetSubKeyNames())
        {
            Console.WriteLine(item);
            providers.Add(item);
            providers.Add("{" + item.ToString() + "}");
        }
    }
}
catch { }

Console.WriteLine("**********CurrentConfig");
try
{
    reg = (Registry.CurrentConfig).OpenSubKey("Software");
    reg = reg.OpenSubKey("ODBC");
    reg = reg.OpenSubKey("ODBC.INI");
    reg = reg.OpenSubKey("ODBC Data Sources");
    if (reg != null)
    {

        Console.WriteLine("--Value");
        foreach (string item in reg.GetValueNames())
        {
            Console.WriteLine(item);
            providers.Add(item);
            providers.Add("{" + item + "}");
            var val = reg.GetValue(item);
            Console.WriteLine(val);
            providers.Add(val.ToString());
            providers.Add("{" + val.ToString() + "}");
        }
        Console.WriteLine("--Subkeys");
        foreach (string item in reg.GetSubKeyNames())
        {
            Console.WriteLine(item);
            providers.Add(item);
            providers.Add("{" + item.ToString() + "}");
        }
    }
}
catch { }

Console.WriteLine("**********Users");
try
{
    reg = (Registry.Users).OpenSubKey("Software");
    reg = reg.OpenSubKey("ODBC");
    reg = reg.OpenSubKey("ODBC.INI");
    reg = reg.OpenSubKey("ODBC Data Sources");
    if (reg != null)
    {

        Console.WriteLine("--Value");
        foreach (string item in reg.GetValueNames())
        {
            Console.WriteLine(item);
            providers.Add(item);
            providers.Add("{" + item + "}");
            var val = reg.GetValue(item);
            Console.WriteLine(val);
            providers.Add(val.ToString());
            providers.Add("{" + val.ToString() + "}");
        }
        Console.WriteLine("--Subkeys");
        foreach (string item in reg.GetSubKeyNames())
        {
            Console.WriteLine(item);
            providers.Add(item);
            providers.Add("{" + item.ToString() + "}");
        }
    }
}
catch { }

List<string> returnConnectionStrings = new List<string>();
foreach (string filePath in filePaths)
{
    foreach (string pr in providers)
    {

        Console.WriteLine("///////////////////// " + pr);
        string connectiontest = @"Provider=" + pr
            + "; Data Source Name=" + pr
            + "; Driver=" + pr
            + "; Default Driver=" + pr
            + "; Dsn=" + pr 
            + "; Data Source Name=" + pr
            + "; Data Source=" + filePath
            + "; Dbq=" + filePath
            + "; Jet OLEDB:Database Password=" + vbaPass
            + "; Uid=Admin; Pwd=" + vbaPass
            + "; User Id=Admin; Password=" + vbaPass;
        OleDbCommand cmd = null;
        OdbcCommand cmdOdbc = null;
        try
        {

            OleDbConnection connection = new OleDbConnection(connectiontest);
            connection.Open();
            cmd = connection.CreateCommand();

        }
        catch (Exception e)
        {
            Console.WriteLine((e.Message));
        }
        try
        {

            OdbcConnection connectionOdbc = new OdbcConnection(connectiontest);
            connectionOdbc.Open();
            cmdOdbc = connectionOdbc.CreateCommand();

        }
        catch (Exception e)
        {
            Console.WriteLine((e.Message));
        }

        Console.WriteLine("-----------------------------------------");
        if (cmd is null && cmdOdbc is null) continue;
        Console.WriteLine("-----------------------------------------");
        Console.WriteLine("-----------------------------------------");
        Console.WriteLine("-----------------------------------------");
        try
        {
            if(cmd != null)
            {

                cmd.CommandText = "Select * FROM Branches";
                var a = cmd.ExecuteScalar();
                Console.WriteLine(a);
                Console.WriteLine("This works: " + pr);
            } 
        } catch (Exception e)
        {
            Console.WriteLine((e.Message));
        }
        try
        {
            if (cmdOdbc != null)
            {

                cmdOdbc.CommandText = "Select * FROM Branches";
                var a = cmdOdbc.ExecuteScalar();
                Console.WriteLine(a);
                Console.WriteLine("This Works: " + pr);

            }
        }
        catch (Exception e)
        {
            Console.WriteLine((e.Message));
        }
    }
    string connectionString = @"Provider=ADsDSOObject;Data Source=" + filePath;

    returnConnectionStrings.Add(connectionString);
}

Output:

SQLOLEDB
MSDMine
MSDataShape
SQLNCLI11
ADsDSOObject
MSOLEDBSQL
MSOLEDBSQL Enumerator
MSDMine Enumerator
SQLNCLI11 Enumerator
Windows Search Data Source
MSOLAP
MSOLAP
MSDASQL
MSDASQL Enumerator
MSOLAP
MSOLAP
Microsoft.Jet.OLEDB.4.0
SQLOLEDB Enumerator
MSDAOSP
MSDAORA
**********CurrentUser
--Value
dBASE Files
Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)
Excel Files
Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)
MS Access Database
Microsoft Access Driver (*.mdb, *.accdb)
Visio Database Samples
Microsoft Access Driver (*.mdb, *.accdb)
--Subkeys
**********LocalMachine
**********CurrentConfig
**********Users
///////////////////// SQLOLEDB
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Invalid connection string attribute
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {SQLOLEDB}
The '{SQLOLEDB}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// MSDMine
Unspecified error
Error Code = 0x80040E21, External Code = 0x00000000:.
Errors in the OLE DB provider. The Extended Properties property is set to a value that is not supported.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {MSDMine}
The '{MSDMine}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// MSDataShape
Data provider could not be initialized.
[Microsoft][ODBC Driver Manager] Data source name too long
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {MSDataShape}
The '{MSDataShape}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// SQLNCLI11
Login timeout expired
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Invalid connection string attribute
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {SQLNCLI11}
The '{SQLNCLI11}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// ADsDSOObject
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
-----------------------------------------
-----------------------------------------
-----------------------------------------
'ADsDSOObject' failed with no error message available, result code: DB_E_ERRORSINCOMMAND(0x80040E14).
///////////////////// {ADsDSOObject}
The '{ADsDSOObject}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// MSOLEDBSQL
Login timeout expired
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Invalid connection string attribute
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {MSOLEDBSQL}
The '{MSOLEDBSQL}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// MSOLEDBSQL Enumerator
The 'MSOLEDBSQL Enumerator' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {MSOLEDBSQL Enumerator}
The '{MSOLEDBSQL Enumerator}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// MSDMine Enumerator
The 'MSDMine Enumerator' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {MSDMine Enumerator}
The '{MSDMine Enumerator}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// SQLNCLI11 Enumerator
The 'SQLNCLI11 Enumerator' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {SQLNCLI11 Enumerator}
The '{SQLNCLI11 Enumerator}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// Windows Search Data Source
The 'Windows Search Data Source' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {Windows Search Data Source}
The '{Windows Search Data Source}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// MSOLAP
Unspecified error
Error Code = 0x80040E21, External Code = 0x00000000:.
Errors in the OLE DB provider. The Extended Properties property is set to a value that is not supported.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {MSOLAP}
The '{MSOLAP}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// MSOLAP
Unspecified error
Error Code = 0x80040E21, External Code = 0x00000000:.
Errors in the OLE DB provider. The Extended Properties property is set to a value that is not supported.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {MSOLAP}
The '{MSOLAP}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// MSDASQL
The .Net Framework Data Provider for OLEDB (System.Data.OleDb) does not support the Microsoft OLE DB Provider for ODBC Drivers (MSDASQL). Use the .Net Framework Data Provider for ODBC (System.Data.Odbc).
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {MSDASQL}
The '{MSDASQL}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// MSDASQL Enumerator
No such interface supported
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {MSDASQL Enumerator}
The '{MSDASQL Enumerator}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// MSOLAP
Unspecified error
Error Code = 0x80040E21, External Code = 0x00000000:.
Errors in the OLE DB provider. The Extended Properties property is set to a value that is not supported.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {MSOLAP}
The '{MSOLAP}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// MSOLAP
Unspecified error
Error Code = 0x80040E21, External Code = 0x00000000:.
Errors in the OLE DB provider. The Extended Properties property is set to a value that is not supported.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {MSOLAP}
The '{MSOLAP}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// Microsoft.Jet.OLEDB.4.0
Cannot start your application. The workgroup information file is missing or opened exclusively by another user.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {Microsoft.Jet.OLEDB.4.0}
The '{Microsoft.Jet.OLEDB.4.0}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// SQLOLEDB Enumerator
No such interface supported
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {SQLOLEDB Enumerator}
The '{SQLOLEDB Enumerator}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// MSDAOSP
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {MSDAOSP}
The '{MSDAOSP}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// MSDAORA
Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.

Provider is unable to function until these components are installed.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {MSDAORA}
The '{MSDAORA}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// dBASE Files
The 'dBASE Files' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {dBASE Files}
The '{dBASE Files}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)
The 'Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)}
The '{Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// Excel Files
The 'Excel Files' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {Excel Files}
The '{Excel Files}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)
The 'Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}
The '{Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// MS Access Database
The 'MS Access Database' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {MS Access Database}
The '{MS Access Database}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// Microsoft Access Driver (*.mdb, *.accdb)
The 'Microsoft Access Driver (*.mdb, *.accdb)' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {Microsoft Access Driver (*.mdb, *.accdb)}
The '{Microsoft Access Driver (*.mdb, *.accdb)}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// Visio Database Samples
The 'Visio Database Samples' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {Visio Database Samples}
The '{Visio Database Samples}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// Microsoft Access Driver (*.mdb, *.accdb)
The 'Microsoft Access Driver (*.mdb, *.accdb)' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------
///////////////////// {Microsoft Access Driver (*.mdb, *.accdb)}
The '{Microsoft Access Driver (*.mdb, *.accdb)}' provider is not registered on the local machine.
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
-----------------------------------------

For a comment:

To use the ACE data engine? Well, since office/access 2013, installing Access does not expose a working copy of ACE. (prior to 2013, a simple install of Access would install + expose a working copy of ACE for use with say 3rd party applications. (VB6, FoxPro, c++, .net).

Now, installing Access does NOT expose a working copy of ACE data engine for 3rd parties.

So, simply install the ACE data engine from here:

https://www.microsoft.com/en-us/download/details.aspx?id=54920

And, make sure you install the correct bit size version (X86 for x32 bits). Once done then you can connect to an access (accDB) file. Note for "mdb" files, you don't have to install anything, since the "JET" engine has been default installed as part of windows for 20+ years.

Once ACE (the newer version of JET) as been installed, then using ODBC providers, or oleDB providers in net should work just fine. However, you should force the .net project as x86 in place of "any cpu".

And if you looking to migrate data from Access databases to sql server? Little reason to "roll your own" with .net, I would strong suggest you use SSMA (sql server migration assistant for Access). This can be found here:

The above will not only migrate data, but setup existing indexes, keep existing PK, and even upload (keep) the table relations intact. If you have just a few simple tables, then you can even use SQL manger to import from Access. But, if you want to save a truckload of work, and not have to re-create the PK, not have to re-create indexes, and not have to re-create existing table relationships? Then you save huge amounts of work by using the Sql migration assistant to sql server. SSMAA will also add a "timestamp" (rowversion) column automatically for you to each table. So, I high reocmmend using this tool. (I also recommended you add the TS column to each table - .net will use this for concurrency issues, and so will Access if you using Access as a front end to sql server).

You find this here: https://www.microsoft.com/en-us/download/details.aspx?id=54255

The tool has a "bit" of a learning curve, but it really is the best tool for this job.

Again, make sure you use x86 or x64 bit version -- there are two downloads. And once again, that utility will not work unless you installed a exposed copy of ACE.

Edit - possible suggestion is to use access code.

Consider using Access. The “reason” is that access append (sql) queries have the “special” ability to work between two different connections at the SAME time!. You can’t do this with most .net providers. In fact, you can't do this with most systems.

This is especially the case if the table name in those “50” databases is the same. And if the schema in the 50 databases is is the same.

So, from a blank new access database?

We setup ONE linked table to the sql server table. (use the exernal data tab – link to the one sql server table).

You can rename that linked table to anything you want. Lets assume

tblOutput

Now, setup one more linked table to the first Access database (1 of 50).

We do this, since now we can fire up the access query builder. Drop in the source table, and THEN choose append query from the ribbon. Select the linked sql server tble.

The reason WHY we do this is then you can use a GUI builder to map the columns. So, you have a from column, and a to column. This is done by selecting the field(s) from a combo box in the query builder.

So far? We not written one line of code. You can now run that append query, and we just sent the data to sql server. So table 1 is done.

Now, if the 49 other databases are the same?

Then we simply write a small loop to link to each of the databases, and then execute our append query.

The only question is if the table name in each of the 50 is the same name, or different?

Assuming the same table name, and same structure for all 50?

Then our vba code would be like this:

Sub Main()

  ' process all mdb or accdb files in a folder
  
  Dim strDir        As String
  Dim strOneFile    As String
  
  strDir = "c:\test2\*.accDB"
  
  strOneFile = Dir(strDir)
  
  Do While strOneFile <> ""
  
     ' link to this database
     
     ' run the append query to send data to sql server
     Debug.Print strOneFile
     
     DoCmd.TransferDatabase acLink, "MS access", strOneFile, acTable, "tblCustomers", "tblSource", False
     
     ' above links table to tblSource
     
     ' now run append query:
     
     CurrentDb.Execute "qryAppend", dbFailOnError
     
     
      strOneFile = dir()
  Loop
  
  
End Sub