c# - 尝试连接到 Ms Access Db，但我没有管理员权限或 ACE.OLEDB.16.0
问题描述
我正在尝试编写一个 C# 程序，它将大量 Ms Access Dbs 迁移到 Ms Sql 服务器。我目前连接到 Ms Sql 服务器没有问题，但 Access 一直是一堵墙。
我已经尝试使用 Odbc 使用 .Net Core，但我似乎无法解决错误“找不到数据源名称且没有默认驱动程序”。不管我投入了什么，所以我已经切换到一个使用 .Net 框架的新项目，认为旧的可能意味着我有更好的机会。
似乎我的系统具有“Microsoft.Jet.OLEDB.4.0”，但这无法识别格式为“*.accdb”的我的 Access Db。使用互联网时，我发现我应该尝试切换到“Microsoft.ACE.OLEDB.16.0”，但遗憾的是“此提供程序未在本地计算机上注册。”。
我没有管理员权限，所以我尝试按照建议安装 AccessDatabaseEngine 是徒劳的。
你会建议我尝试让 C# 打开与 Ms Access 数据库的连接。
更新：我编写了一个以 86x 和 64x 运行的脚本，它应该可以帮助我连接。有人可以帮助改进此脚本，以便我可以快速确定如何从任何机器连接到访问数据库 (*.accdb)。
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);
}
输出：
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
-----------------------------------------
评论：
解决方案
要使用ACE数据引擎？好吧，从 office/access 2013 开始，安装 Access 不会公开 ACE 的工作副本。（在 2013 年之前，Access 的简单安装将安装 + 公开 ACE 的工作副本，以便与 3rd 方应用程序一起使用。（VB6、FoxPro、c++、.net）。
现在，安装 Access 不会为第 3 方公开 ACE 数据引擎的工作副本。
因此，只需从此处安装 ACE 数据引擎：
https://www.microsoft.com/en-us/download/details.aspx?id=54920
并且，确保安装正确的位大小版本（X86 用于 x32 位）。完成后，您可以连接到访问 (accDB) 文件。注意“mdb”文件，您不必安装任何东西，因为“JET”引擎已作为 Windows 的一部分默认安装了 20 多年。
一旦安装了 ACE（较新版本的 JET），那么在网络中使用 ODBC 提供程序或 oleDB 提供程序应该可以正常工作。但是，您应该强制将 .net 项目作为 x86 来代替“任何 cpu”。
如果您希望将数据从 Access 数据库迁移到 sql server？几乎没有理由用 .net “自己动手”，我强烈建议您使用 SSMA（Access 的 sql server migration assistant）。这可以在这里找到：
以上不仅会迁移数据，还会设置现有索引，保持现有PK，甚至上传（保持）表关系完整。如果您只有几个简单的表，那么您甚至可以使用 SQL 管理器从 Access 导入。但是，如果您想节省一大堆工作，而不必重新创建 PK，不必重新创建索引，也不必重新创建现有的表关系？然后使用 Sql 迁移助手到 sql server 可以节省大量工作。SSMAA 还会自动为您添加一个“时间戳”（rowversion）列到每个表中。所以，我强烈推荐使用这个工具。（我还建议您将 TS 列添加到每个表中 - .net 将使用它来解决并发问题，如果您使用 Access 作为 sql server 的前端，Access 也将使用它）。
您可以在这里找到： https ://www.microsoft.com/en-us/download/details.aspx?id=54255
该工具具有“一点”学习曲线，但它确实是这项工作的最佳工具。
同样，请确保您使用 x86 或 x64 位版本——有两个下载。再一次，除非您安装了 ACE 的公开副本，否则该实用程序将无法工作。
编辑 - 可能的建议是使用访问代码。
考虑使用 Access。“原因”是访问附加（sql）查询具有“特殊”能力，可以同时在两个不同的连接之间工作！大多数 .net 提供商无法做到这一点。事实上，大多数系统都无法做到这一点。
如果那些“50”个数据库中的表名相同，则尤其如此。如果 50 个数据库中的架构是相同的。
那么，从一个空白的新访问数据库呢？
我们将 ONE 链接表设置为 sql server 表。（使用外部数据选项卡 - 链接到一个 sql server 表）。
您可以将该链接表重命名为您想要的任何内容。让我们假设
tbl输出
现在，为第一个 Access 数据库（50 个中的 1 个）再设置一个链接表。
我们这样做，因为现在我们可以启动访问查询构建器。放入源表，然后从功能区中选择追加查询。选择链接的 sql server 表。
我们这样做的原因是您可以使用 GUI 构建器来映射列。因此，您有一个 from 列和一个 to 列。这是通过从查询构建器的组合框中选择字段来完成的。
至今？我们没有写一行代码。您现在可以运行该附加查询，我们刚刚将数据发送到 sql server。这样表1就完成了。
现在，如果其他49个数据库都一样？
然后我们简单地编写一个小循环来链接到每个数据库，然后执行我们的追加查询。
唯一的问题是 50 个中的每个表名是同名还是不同？
假设所有 50 个表名相同，结构相同？
那么我们的vba代码会是这样的：
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
推荐阅读
- android - 如何处理多个身份验证提供程序（firebase）
- python-3.x - 连接数据帧，重命名新索引，删除旧索引
- node.js - 拥有数千个虚拟用户的 Node.JS 负载测试工具
- openssl - nelem.h 在哪里？（对于 OpenSSL）
- javascript - 如何使用 Javascript 相对于时间更改 HTML 中文本的颜色？
- c# - HttpClient 不发送响应
- java - [Java][Spring-boot] @NotNull 注解不是 DDL
- regex - 我需要一个只接受这种格式 YYYYWWWW 的正则表达式（例如 2021WW12）
- html - Bootstrap 模态文本点击
- python - Selenium 在打开 Python 后自动关闭窗口