如何通过.NET链接外部Access表? [英] How can I link an external Access table via .NET?

问题描述

我的解决方案中的App_Data文件夹中有两个文件.这两个文件都是.accdb文件，一个是表文件(核心).我还有另一个文件，其中包含存储的查询和不再需要的一些VBA元素，但是我将需要存储的查询.此解决方案位于网络驱动器上，保存查询的文件和保存表的文件链接在一起.

I have two files located in the App_Data folder in my solution. Both files are .accdb files and one is a file of tables, the core. I have another file that contains the stored queries and some VBA elements that I won't be needing anymore, but I will need the stored queries. This solution is located on a network drive and the file that holds the queries and the file that holds the tables are linked.

当我创建安装项目并安装应用程序时，我只需要包含查询的文件.问题在于此文件链接回到表文件的原始位置.我需要它来请求包含该表的文件的位置，因为它将安装在另一台计算机上，其中包含该表的.accdb文件可能在任何地方.有没有办法让OpenFileDialog提示他们指向其位置?

When I create an installation project and install the application, I only need the file that contains the queries. The problem is that this file links back to the table file's original location. I need it to request the location of the file that contains the table as this will be installed on another machine where the .accdb file that contains the table could be anywhere. Is there a way to have an OpenFileDialog come up to ask them to point to its location?

我目前有一个包含DAL的N层应用程序，该应用程序获取存储在My.Settings中的连接字符串.字符串是"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\App_Data\FPC_Reporting.accdb"，这是包含存储的查询的安装随附的文件.但是，该文件仍然认为包含表的文件仍指向我的网络位置，但是如上所述，它可以在任何地方，所以我想让它询问用户.安装后，确定包含表的本地文件的位置.

I currently have an N-Tier application containing a DAL that gets the connection string stored in My.Settings. The string is "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\App_Data\FPC_Reporting.accdb" which is the file that is included with the install that contains the stored queries. That file though, remains to think that the file containing the tables is still pointing to my network location but as stated, it could be anywhere so I would like to have it ask the user; after installation as to where their local file, that contains the tables, is located.

在安装应用程序后以及断开网络驱动器之后收到的错误为"N:\ PROJECTS \ FPC Reporting Tool \ FPCReportBuilder \ FPCReportBuilder \ App_Data \ FPC_Reporting_DATA.accdb'不是有效路径.表示路径名拼写正确，并且您已连接到文件所在的服务器." 错误中显示的文件名是包含表的文件，该表应该链接到包含该表的文件连接字符串中显示的已存储查询.

The error received after installation of the application and also after disconnecting the network drive is "N:\PROJECTS\FPC Reporting Tool\FPCReportBuilder\FPCReportBuilder\App_Data\FPC_Reporting_DATA.accdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides." The filename shown in the error is the file that contains the tables from which are supposed to be linked to the file containing the stored queries shown in the connection string.

推荐答案

以下C#代码已经过测试，并确认在Visual Studio 2010中可以正常工作:

The following C# code has been tested and confirmed as working in Visual Studio 2010:

private void button1_Click(object sender, EventArgs e)
{
    if (openFileDialog1.ShowDialog() == DialogResult.OK)
    {
        string RemoteDatabasePath = openFileDialog1.FileName;

        // the following code requires that the project have a COM reference to:
        // "Microsoft Office 14.0 Access Database Engine Object Library"

        // create the DBEngine object
        var dbe = new Microsoft.Office.Interop.Access.Dao.DBEngine();

        // open the local database file containing the linked table
        Microsoft.Office.Interop.Access.Dao.Database db = dbe.OpenDatabase(@"C:\__tmp\testData.accdb");

        // create a TableDef object for the linked table we want to update
        Microsoft.Office.Interop.Access.Dao.TableDef tbd = db.TableDefs["Products"];

        // update the .Connect property with the full path to the remote database
        tbd.Connect = ";DATABASE=" + RemoteDatabasePath;

        // refresh the table link
        tbd.RefreshLink();

        // test the new connection
        Microsoft.Office.Interop.Access.Dao.Recordset rs = db.OpenRecordset("SELECT * FROM Products", Microsoft.Office.Interop.Access.Dao.RecordsetTypeEnum.dbOpenSnapshot);
        MessageBox.Show(rs.Fields["ProductName"].Value);
        rs.Close();
    }
    this.Close();
}

编辑以下内容:评论

检查在给定计算机上安装的Access数据库引擎("ACE")的版本:

Edit re: comment

Checking the version(s) of the Access Database Engine ("ACE") that are installed on a given machine:

搜索文件ACEOLEDB.DLL.

1. 如果在C:\Program Files\Common Files\Microsoft Shared\OFFICE14中找到它，则说明已安装ACE，并且其版本与操作系统的位"相匹配:32位Windows上为32位ACE，64位Windows上为64位ACE Windows.

1. If it is found in C:\Program Files\Common Files\Microsoft Shared\OFFICE14 then ACE is installed and its version matches the "bit-ness" of the OS: 32-bit ACE on 32-bit Windows, and 64-bit ACE on 64-bit Windows.

如果在C:\Program Files (x86)\Common Files\microsoft shared\OFFICE14中找到它，则在64位Windows上安装了32位ACE.

If it is found in C:\Program Files (x86)\Common Files\microsoft shared\OFFICE14 then 32-bit ACE is installed on 64-bit Windows.

任何使用ACE的应用程序都需要安装正确的版本:32位应用程序将需要32位版本的ACE(即使在64位Windows上也是如此)，而64位应用程序将需要64位版本. ACE. .NET以任何平台"为目标的应用程序将需要与主机OS的位"相匹配的ACE版本.

Any application using ACE will need to have the correct version installed: 32-bit applications will require the 32-bit version of ACE (even on 64-bit Windows), and 64-bit applications will require the 64-bit version of ACE. .NET applications targeted to "any platform" will need the version of ACE that matches the "bit-ness" of the host OS.

这篇关于如何通过.NET链接外部Access表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！